Relation with table with unexisting data

Yii 2.0 General Discussions
1

Hello, guys. I have question for someone experienced.

Just imagine situation.

I have


users

table and


item

table

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

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!

2

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

3

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

$type = ‘FULL OUTER JOIN’

4

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

5

You can use it like that:

$query1 = […Query() here…]

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

But…

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.

6

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

7

updateCounters

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.

8

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

Thanks a lot, Bizley!