![]() |
Table short name and queries - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28) +--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30) +--- Thread: Table short name and queries (/showthread.php?tid=84761) Pages:
1
2
|
Table short name and queries - SoccerGuy3 - 11-07-2022 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? RE: Table short name and queries - kenjis - 11-07-2022 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. RE: Table short name and queries - SoccerGuy3 - 11-07-2022 (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. RE: Table short name and queries - kenjis - 11-07-2022 Query Builder can use alias (short name). https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html PHP Code: $db = \Config\Database::connect(); RE: Table short name and queries - SoccerGuy3 - 11-08-2022 (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? RE: Table short name and queries - kenjis - 11-08-2022 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/CodeIgniter4/database/query_builder.html#get RE: Table short name and queries - SoccerGuy3 - 11-08-2022 (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)! ![]() RE: Table short name and queries - davis.lasis - 11-09-2022 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 ! RE: Table short name and queries - kenjis - 11-09-2022 I found tips to get QueryBuilder from the Model: https://github.com/codeigniter4/CodeIgniter4/issues/4527#issuecomment-869103637 RE: Table short name and queries - SoccerGuy3 - 11-09-2022 (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? |