Relation with table with unexisting data

Hello, guys. I have question for someone experienced.

Just imagine situation.

I have


table and



Relation between tables 1-many (1 user, many items)

Also i have CRUD in backend and i need to sort by count of items.

There is situation when some users have no items, but i need to sort them as 0.

Currently i’m doing that in way

$exp = new \yii\db\Expression("(SELECT,item.user_id FROM item WHERE item.archived = ".(string)Item::ARCHIVE_FALSE.") as listings");

$query->join('left join', $exp->expression, ['listings.user_id' => new \yii\db\Expression('`user`.`id`')]);

$query->select("count( as listings, user.*");

But i’m afraid that this $exp query can be the reason of memory exceeding

Question is:

How to make this relation in way to show ALL users (even if they have no items in relation) and to not kill memory

p.s. maybe my solution already OK? i’d like to hear some suggestions

p.p.s. also if there is something is not clear for you - ask me!

according to this pic i need use full outer join, but how to do it in yii2?

join($type, $table, $on = ‘’, $params = [])


ty for reply!

forgot to clarify fact that i’m using mysql

someone suggesting that in mysql its better to use union of left join and right join to make emulated full outer, but i dont see any possible variants to do this in activequery

You can use it like that:

$query1 = […Query() here…]

$query2 = […Query() here…]->union($query1)


I would do it the other way. If you really want to show counter and sort/filter it add counter column in the user model so there is no need for relation. You can update its value with updateCounters method every time items’ number changes.

ok its only TOP of problem (items count). I also was thinking about new column in user table, but i have another situations:

In backend CRUD i also need to show and sort by social networks, used for registration.

Not all users registered via socials. What should i do? also create field in user table to write there all social networks, separated by coma?

p.s. please, give me link to this updateCounters method and docs to this … looks like its something new for me


It’s ALWAYS faster to sort by column in the main table instead of relation so yes, that’s one (at least) way to do it.

i’m afraid about data relevance, but this is totally different story.

Thanks a lot, Bizley!