Hello, guys. I have question for someone experienced.
Just imagine situation.
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.id,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(listings.id) as listings, user.*");
But i’m afraid that this $exp query can be the reason of memory exceeding
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!
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.