Table name in Query

Yii 2.0 General Discussions
1

Hi,

when using yii\db\Query it is required in some cirumstances to include the table name (for instance to disambiguate column names).

… but the table name may not be constant if for example it includes a prefix.

Question : What is the best way to handle such case ?

Should I write something like that ? :


$customers = Customer::find()

	->select('customer.*')

	->leftJoin('order', '`'.$this->tableName().'`.`customer_id` = `customer`.`id`')

	->where([$this->tableName().'.status' => Order::STATUS_ACTIVE])

	->with('orders')

	->all();

assuming $this refers to an ActiveRecord instance

Or is there a better way ?

ciao

B)

2

$this refers to context where you are. It could be in model or in Controller, according where you are write this code.

3

To include the prefix, use {{%table_name}}.

4

Thanks to both of you… but I do know how to setup table prefix in the Model, and how to get it ;)

My question was : is there a better way than calling tableName() to refer to the table name in a query where I must include it ?

I suspect there is not but maybe I’m wrong.

ciao

B)

5

Hi Raoul,

you are right that tableName() is the only "right" way to get the table name of the AR class.

However, since ActiveRecord::tableName() is a static method, I would recommend to call it

in static context with explicit AR class:




$customers = Customer::find()

    	->select(Customer::tableName() . '.*')

    	->leftJoin(Order::tableName(), '`'.Customer::tableName().'`.`customer_id` = `customer`.`id`')

    	->where([Customer::tableName().'.status' => Order::STATUS_ACTIVE])

    	->with('orders')

    	->all();

        

// more DRY way

$customerTable = Customer::tableName();

$customers = Customer::find()

    	->select("$customerTable.*")

    	->leftJoin(Order::tableName(), '`'.$customerTable.'`.`customer_id` = `customer`.`id`')

    	->where([$customerTable.'.status' => Order::STATUS_ACTIVE])

    	->with('orders')

    	->all();

By explicitly specifying the AR class names, you can easily re-use parts of your queries, in various contexts, where $this might be something different.

6

Thanks for this valuable advice that I will follow.