Joins Are Back

For those who haven’t noticed yet, INNER/LEFT joins for relations are back!

Moreover, now you can do some complex joins like


parent LEFT JOIN child ON parent.id = child.parent_id AND child.some_field = 'some_value'

and even self-joins/multiple joins to another table using table aliases.

This is quite important thing for those of us who deals with complex datasets and still wants to use all the power of AR/AQ.

It allows you to do filtering based on related model’s fields without using extra join() call, thus keeping code DRY.

As far as I know almost every modern php framework (including Phalcon) does not include this feature for some reason (the only FW that now has powerful ORM is CakePHP 3.0, which is in deep pre-alpha).

So now your code can look like this:




$query = MyModel::find()

    ->joinWith(['relation1', 'relation2']); // relation1 and relation2 are joined to MyModel and loaded eagerly


$countQuery = clone $query;

$pages = new Pagination(['totalCount' => $countQuery->count(), 'pageSize' => 15]);


$items = $query

    ->orderBy($sort->orders)

    ->offset($pages->offset)

    ->limit($pages->limit)

    ->with(['relation3', 'relation4']) // we also want to eagerly load relation3 and relation4, but we don't need to filter on them

    ->all();



Here is documentation on that.

Thanks guys from dev team, you made my day (week?..)!

That’s a great update B).

The best part of it is that you can get JOIN from SQL and noSQL in a single AR "query".

Thanks for posting btw. very well written.

I also noticed the update, very nice! A few weeks ago i posted a question about this here, so i’m very happy you guys implemented this! Thanks!

Edit:

Quick question though, is it possible to use joinWith() with an extra condition, so i can perform a join like this (i mean the ‘AND photoType = 1’ part):


LEFT JOIN photo ON user.id = photo.userId AND photoType = 1

@Davey: yes. Use onCondition(). Please see the doc at https://github.com/yiisoft/yii2/blob/master/docs/guide/active-record.md#joining-with-relations

Great, thanks!