Welcome Guest, Not a member yet? Register   Sign In
Table short name and queries
#1

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?
Reply
#2

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/CodeIgnit...tml#models
Reply
#3

(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/CodeIgnit...tml#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.
Reply
#4

Query Builder can use alias (short name).
https://codeigniter4.github.io/CodeIgnit...ilder.html

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

(11-07-2022, 06:00 PM)kenjis Wrote: Query Builder can use alias (short name).
https://codeigniter4.github.io/CodeIgnit...ilder.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` `uON `news`.`createdby_id` = `u`.`id`
ORDER BY `is_activeDESC, `titleASC 


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?
Reply
#6

(This post was last modified: 11-08-2022, 04:46 PM by kenjis.)

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/CodeIgnit...r.html#get
Reply
#7

(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/CodeIgnit...r.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
Reply
#8

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 !
Reply
#9

(This post was last modified: 11-09-2022, 12:39 AM by kenjis.)

I found tips to get QueryBuilder from the Model:
https://github.com/codeigniter4/CodeIgni...-869103637
Reply
#10

(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?
Reply




Theme © iAndrew 2016 - Forum software by © MyBB