CodeIgniter Forums
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,')
                                    ->where('msc.is_active',1)
                                    ->findAll(); 
Everything works as expected. However, later on:

PHP Code:
$data = array(
      'is_active' => 0,
); 

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.
https://codeigniter4.github.io/CodeIgniter4/models/model.html#models


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.
$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.
https://codeigniter4.github.io/CodeIgniter4/models/model.html#models

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();
$builder = $db->table('users u'); 


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).
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html

PHP Code:
$db      = \Config\Database::connect();
$builder = $db->table('users u'); 

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();
$records = $this->newsModel 
    ->table('news n')
    ->select('n.id, n.title, n.created_at, n.is_active, u.firstname')
    ->join('users u','news.createdby_id = u.id','left')
    ->orderBy('is_active DESC, title ASC')
    ->findAll(); 


Produces an error:

PHP Code:
Unknown column 'n.id' in 'field list' 


While this query:


PHP Code:
$this->newsModel = new \App\Models\NewsModel();
$records = $this->newsModel 
    ->from('news n')
    ->select('n.id, n.title, n.created_at, n.is_active, u.firstname')
    ->join('users u','news.createdby_id = u.id','left')
    ->orderBy('is_active DESC, title ASC')
    ->findAll(); 


Produces this (which is obviously wrong):


PHP Code:
SELECT `n`.`id`, `n`.`title`, `n`.`created_at`, `n`.`is_active`, `u`.`firstname`
FROM (`news`, `news` `n`)
LEFT JOIN `users` `u` ON `news`.`createdby_id` = `u`.`id`
ORDER BY `is_active` DESC, `title` ASC 


In case it matters, here is the Model:


PHP Code:
namespace App\Models;

class NewsModel extends \CodeIgniter\Model
{
protected $table = 'news';

protected $allowedFields = [
 'title','article','url','is_active','createdby_id','updatedby_id','img_name'
 ];

} 


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();
$builder = $db->table('news n');
$builder->select('n.id, n.title, n.created_at, n.is_active, u.firstname')
    ->join('users u','news.createdby_id = u.id','left')
    ->orderBy('is_active DESC, title ASC');
$query = $builder->get(); 

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.
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();
$builder = $db->table('news n');
$builder->select('n.id, n.title, n.created_at, n.is_active, u.firstname')
    ->join('users u','news.createdby_id = u.id','left')
    ->orderBy('is_active DESC, title ASC');
$query = $builder->get(); 

See https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#get

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)! Smile

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();
$builder = $db->table('news n');
$builder->select('n.id, n.title, n.created_at, n.is_active, u.firstname')
    ->join('users u','n.createdby_id = u.id','left')
    ->orderBy('n.is_active DESC, n.title ASC');
$query = $builder->get(); 

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';
$select .= ', table2.field2, table2.field3, table2.field4';
$select .= ', table3.field8, table3.field9';

$db = \Config\Database::connect();
$builder = $db->table('table1');
$builder->select($select)
 ->join('table2', '...', 'left')
 ->join('table3', '...', 'left'); 

2. you can use manually written query
PHP Code:
$sql = 'SELECT field1 FROM table1 WHERE id = 10';
$results = $this->db->query($sql)->getResultArray(); 


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.
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();
$builder = $db->table('news n');
$builder->select('n.id, n.title, n.created_at, n.is_active, u.firstname')
    ->join('users u','n.createdby_id = u.id','left')
    ->orderBy('n.is_active DESC, n.title ASC');
$query = $builder->get(); 

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';
$select .= ', table2.field2, table2.field3, table2.field4';
$select .= ', table3.field8, table3.field9';

$db = \Config\Database::connect();
$builder = $db->table('table1');
$builder->select($select)
 ->join('table2', '...', 'left')
 ->join('table3', '...', 'left'); 

2. you can use manually written query
PHP Code:
$sql = 'SELECT field1 FROM table1 WHERE id = 10';
$results = $this->db->query($sql)->getResultArray(); 


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 !

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?