SQL JOIN : bad aliasing on join with prefixed db tables

[TatwiraT]

---

name: bad aliasing when using prefix on tables in join
about: when using prefix on db tables, the aliased table must match to the join table, both uses the prefix or both not to use it.

---

Describe the bug
miss match of tables names when using prefix and join, the db tables "dotted" to fields list built by the core class dont match to the supplied ones

CodeIgniter 4 version
all versions and commits

Affected module(s)
Database

Expected behavior, and steps to reproduce if appropriate
the expected behaviour is that the aliasing must match between the aliased tables by the core class and given one by the application

temporary fix

as shown above, the aliasing is not matching, the on condition is on the expected alias but the table aliasing with AS is prefixed which it must not be ^^

i could not use event to debug sql error, hence i used ajax request to see the query ^^
would u please add an example of using dbquery debugger on the "event" in docs

Context

• OS: win7 / centos 7 / debian 12
• Web server : XAMPP 7.2.9
• PHP version: 7.2.9

[lonnieezell]

Please provide an example query that can recreate this issue as what you're showing here looks to be deep in a bunch of custom code with no way for us to actually know if the issue is with the core framework or how it's being used.

[TatwiraT]

this is the Query in StoresController

$this->data['stores'] = (new Stores)->rows(array('store.user_id' => app_config('user','id') , 'store.status !=' => scope('suspend')) )
						 ->order_by('store.online DESC, store.created_at DESC')
						 //->rawData( $limit = NULL );
						 ->join_category()->join_subcategory()
						 ->total_listings()->total_reviews()->total_followers()
						 //->getCompilledSelect();
						 ->formatted( $limit = NULL );

and this is the model

	/*
	* ENCAPSULATE NEW COLUMNS
	*/
	public function add_columns($columns)
	{
		if( is_array($columns) ){
			$this->columns = array_merge($this->columns , $columns);
		}
		else{
			$this->columns[] = $columns;
		}
		$this->columns = array_unique($this->columns);

		return $this;
	}

	/*
	* JOINT OWNER
	*/
	public function join_owner( $on = 'user.id=store.user_id' , $type = 'LEFT')
	{
		$this->add_columns(array( 'name AS owner_name' ));
		//$this->select(array_merge(['store.*'] , $this->columns));
		$this->join(db_table('users').' AS sq_user ' , $on , $type);

		return $this;
	}
	/*
	* 
	*/
	public function join_category( $on = 'cat.id=store.cat_id' , $type = 'LEFT')
	{
		$this->add_columns(array( 'cat.label AS cat_label' ));
		//$this->select(array_merge(['store.*'] , $this->columns));
		$this->join(db_table('categories').' AS sq_cat ' , $on , $type);

		return $this;
	}

	/*
	* 
	*/
	public function join_subcategory($on = 'subcat.id=store.subcat_id' , $type = 'LEFT')
	{
		$this->add_columns(array( 'subcat.label AS subcat_label' ));
		//$this->select(array_merge(['store.*'] , $this->columns));
		$this->join(db_table('subcategories').' AS sq_subcat ' , $on , $type);

		return $this;
	}

	/*
	* COUNT TOTAL SToRE LISTINGS 
	*/
	public function total_listings( $on = 'list.store_id=store.id' , $type = 'LEFT')
	{
		$this->add_columns(array( '(SELECT COUNT(id) FROM '.db_table('listings').' WHERE store_id=store.id) l_count' ));
		//$this->select(array_merge(['store.*'] , $this->columns));

		return $this;
	}

	/*
	* COUNT TOTAL SToRE REVIEWS 
	* 		*REPUTATION*
	*/
	public function total_reviews( $on = 'reviews.store_id=store.id' , $type = 'LEFT')
	{
		//reputation
		$this->add_columns(array( '(SELECT COUNT(id) FROM '.db_table('reviews').' WHERE store_id=store.id) e_count' ));
		//$this->select(array_merge(['store.*'] , $this->columns));

		return $this;
	}

	/*
	* COUNT TOTAL SToRE FOLLOWERS
	*/
	public function total_followers( $on = 'wishlist.store_id=store.id' , $type = 'LEFT')
	{
		//follower
		$this->add_columns(array( '(SELECT COUNT(id) FROM '.db_table('wishlist').' WHERE store_id=store.id) AS f_count' ));
		//$this->select(array_merge(['store.*'] , $this->columns));

		return $this;
	}

	/*
	* 
	*/
	public function order_by($order_by)
	{
		if ( is_array($order_by) )
		{
			foreach ($order_by as $o => $oBy) 
			{
				$this->orderBy($oBy);
			}
			return $this;
		}

		if( match_str($order_by , ',') )
		{
			return $this->order_by(explode(',' , $order_by));
		}

		$this->orderBy($order_by);

		return $this;
	}

	/*
	* GET LIMITED RECORDS 
	* 
	*/
	public function rows($where = array())
	{
		$this->rows = $this->setTable($this->table.' AS store')->where($where);
		return $this;
	}

	/*
	* GET LIMITED RECORDS 
	* 
	*/
	public function limit( $limit )
	{
		$this->add_columns(array( 'store.*' ));
		$this->select( $this->columns );

		//
		if( eq($limit , 1) )
			$this->rows = $this->first();
		else
			$this->rows = $this->paginate($limit);

		return $this;
	}

	/*
	* GET RAW RECORDS
	*/
	public function rawData()
	{
		$this->add_columns(array( 'store.*' ));
		$this->select( $this->columns );
		$this->rows = $this->get()->getResult();
		
		return $this->rows;
	}

	/**
	* FORMATTED RECORDS
	*/
	public function formatted()
	{
		$rows = $this->rawData();
		foreach ($rows as $r => $row)
		{
			$this->rows[$r] = $this->format( $row );
		}

		//
		unset($rows);
		return $this->rows;
	}

[TatwiraT]

the above code is fixed by adding the prefix sq_ which i must not do cause the aliasing doesn't based on the feature of autodetecting prefix in db core class
function db_table auto adds prefix to table in application, it does not affect the code

[lonnieezell]

So the core issue is that the prefix is not added to the aliased table? In the original example you gave it looks like the issue is that it's not escaping the aliased field correctly. All of the table names that you have boxed in your example show the same, and presumably correct, table name, sq_cat.

I really didn't need your entire model here, just a small example that allows me to reproduce an error. Can you provide a (much) smaller, encapsulated example of the core issue here soI can test it easier?

[TatwiraT]

So the core issue is that the prefix is not added to the aliased table?
That's correct, but it shouln't prefixed cause the supplied one does not contain the prefix sq_

All of the table names that you have boxed in your example show the same, and presumably correct, table name, sq_cat
No, I should not add the prefix sq_.
as shown on the temporary fix, is not a good practice, cause when i change the prefix I have to update the model and change again the sq_ with the new prefix

basicaly when I use the alias, I expect that the db core class take the alias as it is to build the fields list, but in this case, it takes the alias and prefix it with the prefix sq_ then builds the fields list which is not identical to the original alias given by the app.
the issue is :
.. LEFT JOIN sq_categories as cat ON sq_cat.id=store.cat_id
The expected is:
.. LEFT JOIN sq_categories as cat ON cat.id=store.cat_id

I faced this issue when I used a separated join as shown on the model, and therefore I have put the concerned part of my model above

[TatwiraT]

more details :
this issue occurs when using Paginate/first;
if I select all records , it works fine, but since I limit by paginate/first it breaks, getResult() works fine

---

I believe, the issue is related to this join syntax

public function join_subcategory($on = 'subcat.id=store.subcat_id' , $type = 'LEFT')
{
	$this->add_columns(array( 'subcat.label AS subcat_label' ));
	$this->join(db_table('subcategories').' AS subcat ' , $on , $type);
	return $this;
}

basicaly, the columns must be called on select clause then join table after from clause
but, here I add columns with the join on the same level, thinking that the db Core Class will arrange the query into the right syntax.
I do not know if is it the case or not, if not it would be great if the core class support this feature and arrange te selected columns whenever/wherver called in a model that customize the join

[lonnieezell]

Cannot replicate. If you provide simpler, CI-only code that can demonstrate the example, I'll take a look again. I've added a couple of tests to the AliasTest, though, and not seeing any issues.