How to get the most favourited items, from another table?

Question

So, there's the User model, and the Item model. It's a many-to-many relation: an item can belong to many users, and a user can have many items. Therefore, there's the UserItemRel model.

To summarize:

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 query, before making the switch to Yii2, was this:

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've used the yii2-enhanced-gii extension to generate the models.

I want to show the 20 most favourited items in the past 48 hours, with their counts. I'm migrating from Yii1.1, and it's been quite the ride so far, and I can't figure this out.

I've 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 properly use this?

Answer 1

The query would something like bellow. I would try to run a native query instead of trying to find out how this could be done withing the 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("Y-m-d", 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
            )

    )

)