How to get your favorite items from another table?

Question

So, there is a User model and an Item model. This is a many-to-many relationship: an item can belong to many users, and a user can have many items. Therefore, there is a UserItemRel model.

Summarizing:

 item id name date_created date_updated user id email password date_created date_updated user_item_rel user_id item_id date_created 

My request, before moving on to Yii2, was as follows:

 SELECT COUNT(UIR.`user_id`) as `favourited`, IT.`id`, IT.`name`, CA.`name` as `category` FROM `user_item_rel` UIR LEFT JOIN `item` IT ON UIR.`item_id` = IT.`id` LEFT JOIN `category_item` CI ON UIR.`item_id` = CI.`item_id` LEFT JOIN `category` CA ON CI.`category_id` = CA.`id` WHERE UIR.`date_created` >= (SYSDATE() - INTERVAL 3 YEAR) GROUP BY UIR.`item_id` ORDER BY `favourited` DESC LIMIT 20 

I used the yii2-enhanced-gii extension to create models.

I want to show the 20 most liked items in the last 48 hours with their counts. I am migrating from Yii1.1, and so far it has been a real trip, and I cannot figure it out.

I found

 $this->hasMany(UserItemRel::className(), ['id' => 'user_id']) ->viaTable('user_item_rel', ['id' => 'item_id'], function ($query) { $query->andWhere(['date_created < INTERVAL 2 DAY']) ->orderBy(['COUNT(*)' => SORT_DESC]); }); } 

but how to use it correctly?

Answer 1

The request will be similar to below. I would try to run my own query, rather than trying to figure out how to do this with orm.

 SELECT item_id, item.name, count(*) favorite FROM user_item_rel LEFT JOIN user ON user.id = user_item_rel.user_id LEFT JOIN item ON item.id = user_item_rel.item_id WHERE user_item_rel.date_created >= (sysdate() - interval 2 DAY) GROUP BY item_id, name ORDER BY favorite DESC LIMIT 20 

Answer 2

You might be able to try something like this:

 $items = UserItemRel::find() ->asArray() ->select("COUNT(`user_id`) as favourited, `item_id`") ->groupBy("item_id") ->joinWith("item") ->orderBy("favourited DESC") ->indexBy("item_id") ->where("'date_created' >= '".date("Ymd", strtotime("-2 days"))."'") ->limit(3) ->all(); 

In my testing, it gives me something like this:

 Array ( [1] => Array ( [favourited] => 4 [item_id] => 1 [item] => Array ( [id] => 1 [name] => Donec [date_created] => 2015-08-26 [date_updated] => 2015-08-26 ) ) [8] => Array ( [favourited] => 3 [item_id] => 8 [item] => Array ( [id] => 8 [name] => Tellus [date_created] => 2015-08-26 [date_updated] => 2015-08-26 ) ) [7] => Array ( [favourited] => 2 [item_id] => 7 [item] => Array ( [id] => 7 [name] => Mollis [date_created] => 2015-08-26 [date_updated] => 2015-08-26 ) ) )