Table short name and queries |
Hey all. Using CI 4.2.6 and having some issues with trying to use a table short name in my queries.
In my model if I put: PHP Code: protected $table = 'mailing_suggestedchanges msc'; And do: PHP Code: $sugChanges = $this->mlChangesModel ->select('msc.ml_id, msc.created_at,') PHP Code: $data = array( PHP Code: $this->mlChangesModel->update($id,$data); Throws this error: Code: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`msc`.`id` IN ('3')' at line 2 Removing the short name and updating the queries to use the full table name makes everything work again. Any suggestions? Am I using the shortname incorrectly?
You cannot use short name in $table.
$table is a table name. 'mailing_suggestedchanges msc' is not a table name. You are confusing the Model and QueryBuilder. Quote:The CodeIgniter’s Model provides convenience features and additional functionality that people commonly use to make working with a single table in your database more convenient.
(11-07-2022, 04:53 PM)kenjis Wrote: You cannot use short name in $table. Thanks. I don't remember where I stumbled on using that format in the last couple weeks (getting old). Is there a way to use short names in CodeIgniter? It's a real pain to have to use full table names in queries.
Query Builder can use alias (short name).
https://codeigniter4.github.io/CodeIgnit...ilder.html PHP Code: $db = \Config\Database::connect();
(11-07-2022, 06:00 PM)kenjis Wrote: Query Builder can use alias (short name). Thanks for the tip, but it doesn't appear to work for me. Is it because I am using a different style of query building? This is the only way I have ever done queries, so maybe what I originally learned was wrong? This query: PHP Code: $this->newsModel = new \App\Models\NewsModel(); Produces an error: PHP Code: Unknown column 'n.id' in 'field list' While this query: PHP Code: $this->newsModel = new \App\Models\NewsModel(); Produces this (which is obviously wrong): PHP Code: SELECT `n`.`id`, `n`.`title`, `n`.`created_at`, `n`.`is_active`, `u`.`firstname` In case it matters, here is the Model: PHP Code: namespace App\Models; This is a super simple query, figured that was best for testing/demonstration purposes. Where have I gone wrong? Am I using an old coding style or something?
It is not a super easy query.
I don't know how to properly write it in Model. If you really like to use an alias, it seems better to extend Model to add $alias property. If you use Query Builder, something like this: PHP Code: $db = \Config\Database::connect(); See https://codeigniter4.github.io/CodeIgnit...r.html#get
(11-08-2022, 04:46 PM)kenjis Wrote: It is not a super easy query. Ok, thanks for taking a look. My comment about it being super simple I guess was in reference to others that I have with 3 joins and 2 sub-selects with grouping and sorting (for instance)!
As far as i can see (without testing my-self), you have a syntax error in your example code.
You have `n` not `news` and order_by must have those tables in-front of table columns as well PHP Code: $db = \Config\Database::connect(); I often use many joins, the biggest i have i think was 9 joins 1. you can collect all your selects first PHP Code: $select = 'table1.field1'; 2. you can use manually written query PHP Code: $sql = 'SELECT field1 FROM table1 WHERE id = 10'; P.S. i never shorten database table names. Yes - it's longer writing, but: - if you have error - easier to spot which table / column issue you have in query - if i have like 5 joins, then tables like `n`, `u`, `m`, `i` - in the big picture does not make sense - if you need to change table name - long table name can be find / replaced easily in all instances, because in 99% it will be unique, with shortening try to find / replace `n` in code !
I found tips to get QueryBuilder from the Model:
https://github.com/codeigniter4/CodeIgni...-869103637
(11-09-2022, 12:30 AM)davis.lasis Wrote: As far as i can see (without testing my-self), you have a syntax error in your example code. Reading the comments above, I am beginning to think the way I am creating my queries is the wrong approach. Should I be using the $builder instead of the way I have since I started with CI? What are the drawbacks to my approach versus the $builder approach? |
Welcome Guest, Not a member yet? Register Sign In |