\yii\db\Query *join methods parse $on arrays as a condition, destroying SQL statements

[herzmut]

For *join methods, there are two ways to use $on parameter: array and string. Using array lets the QueryBuilder parse that one as key value pair, and destroying the resulting SQL statement by tokenizing the second part of the array as string. While this is a wished behavior in (Query)->where(), the exception is in the ON statement where the link is made between two entity fields.

//...
$query->leftJoin('Grocery', ['Fruits.grocery_id' => 'Grocery.id'])

Resulting statement:

SELECT * FROM `Fruits` LEFT JOIN `Grocery` ON `Fruits`.`grocery_id` = 'Grocery.id';

Using direct strings would work, although the developer would need to care for backtick enclosure of database entities:

//...
$query->leftJoin('Grocery', 'Fruits.grocery_id = Grocery.id');

Resulting statement:

SELECT * FROM `Fruits` LEFT JOIN `Grocery` ON Fruits.grocery_id = Grocery.id;

[TriAnMan]

@dguhl You can use $query->leftJoin('Grocery', 'Fruits.grocery_id = Grocery.id') instead. It will provide something you expect. Or use $query->leftJoin('Grocery', ['Fruits.grocery_id' => new \yii\db\Expression('Grocery.id')]) it will do the same expected result.

@cebe I think that this issue is main about a documentation. Because to find a solution I had to browse a lot of the source code.

[cebe]

@TriAnMan the second one is not correct. it will try to bind the expression as a parameter. only first is correct. and yes, this is more about documentation. thanks for checking.

[TriAnMan]

@cebe sorry, but I've just double checked and an expression $query->leftJoin('Grocery', ['Fruits.grocery_id' => new \yii\db\Expression('Grocery.id')]) does the join. At least on Yii 2.0.2

[cebe]

that's interesting, did not know that this works and I am not sure if the code was created to work this way :)

[YasserHassan]

There are situations with multiple column-column and/or column-value where the string-based approach is not recommended. I want to share thoughts to enable using the array format in such cases:

• Add a new parameter to join() functions that tells it if the values in the arrays are column names or values. Or add new functions (e..g leftJoinColumns()) that takes only columns while keeping the old functions dealing with values. This is not convenient when dealing with both types together as they will need to be batched by type.
• Make the value in the array format accept a single value or array that specifies the value type as follows:

$query->leftJoin('Grocery', [
  'Fruits.store_id' => 5, // Treated as in where(), this is the default.

  'Fruits.grocery_id' => [ TYPE_COLUMN, 'Grocery.id'] ] // Explicitly set type to column
  // or value and quote accordingly.
])

[pvlg]

Now when you create a query condition we obtain the following sql query

$query = Order::find();
$query->from(['order' => Order::tableName()]);
$query->leftJoin([
    'user' => User::tableName(),
], ['order.UserId' => 'user.Id']);

SELECT "order".* FROM "tbl_order" "order" LEFT JOIN "tbl_user" "user" ON "order"."UserId" = 'user.Id'

I think it is a good idea to add a condition to the 4th parameter

$query = Order::find();
$query->from(['order' => Order::tableName()]);
$query->leftJoin([
    'user' => User::tableName(),
], ['=', 'order.UserId', 'user.Id', true]);

SELECT "order".* FROM "tbl_order" "order" LEFT JOIN "tbl_user" "user" ON "order"."UserId" = "user"."Id"

It will not break backward compatibility for existing code