Рейтинг пользователей из двух таблиц публикаций одним запросом

[webplus]

Здравствуйте!
Есть таблица users.
Есть таблица articles , которая имеет поле user_id
Есть таблица rating_articles, которая имеет поля article_id и rating (от 1 до 5).
Такая же структура с таблицей news, она имеет поле user_id
и для нее есть таблица rating_news, которая имеет поля news_id и rating (от 1 до 5).

Нужно реализовать запрос, который выведет юзеров и отсортирует по возрастанию максимального количества поля rating из обеих таблиц.
Я думаю делать на каждую таблицу публикаций по sql запросу, а потом уже с помощью php сложить rating из двух таблиц, и потом массив отсортировать.

Но может это можно все сделать одним большим sql запросом или даже использовать ActiveRecord

[andku83]

... отсортирует по возрастанию максимального количества поля rating из обеих таблиц.

имеется ввиду количество записей из публикаций или среднее значение рейтинга этих публикаций?

Но может это можно все сделать одним большим sql запросом или даже использовать ActiveRecord

можно

[webplus]

имеется ввиду количество записей из публикаций или среднее значение рейтинга этих публикаций?

Вывести и отсортировать по возрастанию юзеров по полю rating
Например у одного юзера 10 статей и у всех оценка 5 балов и 4 новости у них оценка 3 у всех.
У другого юзера 5 статей с оценками 5 у каждой и 10 новостей с оценкой 5 у каждой.
То итог:
юзер 2 выводится первым с балом 75
юзер 1 выводится вторым с балом 62

[andku83]

а если у первого 5 по 5, а у второго 100 по 1 - какой порядок?

[webplus]

Вот так сделал:

Код: Выделить всё

        $rows = (new \yii\db\Query())
            ->select([
                '(
                    (
                    (select (select sum(rca.rating) from rating_crypto_analytics rca where rca.crypto_analytics_id=ca.id) from crypto_analytics ca where ca.user_id=p.user_id) 
                    +
                    (select (select sum(rca.rating) from rating_crypto_analytics rca where rca.crypto_analytics_id=ca.id) from crypto_analytics ca where ca.user_id=p.user_id)
                    )
                    /
                    (
                    (select (select count(*) from rating_crypto_analytics rca where rca.crypto_analytics_id=ca.id) from crypto_analytics ca where ca.user_id=p.user_id) 
                    +                   
                    (select (select count(*) from rating_crypto_analytics rca where rca.crypto_analytics_id=ca.id) from crypto_analytics ca where ca.user_id=p.user_id)  
                    )
                )
                as rating',
            ])
            ->from('profile p')
            ->all();

Может это тяжелый запрос и можно проще?

[andku83]

Приблизительно (а может и именно так) это должно выглядеть:

Код: Выделить всё

        $articleQuery = Article::find()
            ->leftJoin('rating_articles', Article::tableName().'.id = rating_articles.article_id')
            ->select(['user_id', 'rating']);

        $newsQuery = News::find()
            ->leftJoin('rating_news', News::tableName().'.id = rating_news.article_id')
            ->select(['user_id', 'rating']);

        $usersQuery = User::find()
            ->select([User::tableName().'.*', 'rating_sum'])
            ->leftJoin(
                ['rt' => (new \yii\db\Query())
                    ->from(['sub' => $articleQuery->union($newsQuery)])
                    ->select(['user_id', 'rating_sum' => new \yii\db\Expression('SUM(rating)')])
                    ->groupBy('user_id')], //возможно вместо 'user_id' нужно написать: new \yii\db\Expression('SUM(rating)')
                User::tableName().'.id = rt.user_id'
            )
            ->orderBy(['rating_news' => SORT_DESC]);

и не забыть в модель добавить поле rating_sum

[andku83]

юзер 2 выводится первым с балом 75
юзер 1 выводится вторым с балом 62

Здесь вы говорили о сумме всех баллов, а в своем запросе реализовали средний балл по публикациям.
Кстати вместо того чтобы делить сумму на количество нужно было использовать функцию AVG().
И в моем примере для реализации среднего значения достаточно SUM() изменить на AVG.

[webplus]

В общем сделал вот так:

Код: Выделить всё

class RatingUser
{
    public static function getRating($user_id = null)
    {
        $query = (new \yii\db\Query())
            ->select('*')
            ->from('profile');
        if(!empty($user_id)) {
            $query->where(['user_id' => $user_id]);
        }
        $userRating = $query->all();

        foreach ($userRating as $key => $user) {
            $rating = 0;
            $SUM = 0;$COUNT = 0;

            $sum = RatingCryptoAnalytics::find()->innerJoinWith('cryptoAnalytics')->where(['user_id' => $user['user_id']])->sum('rating');
            $count = RatingCryptoAnalytics::find()->innerJoinWith('cryptoAnalytics')->where(['user_id' => $user['user_id']])->count();
            $SUM += (!empty($sum)) ? $sum : 0;
            $COUNT += (!empty($count)) ? $count : 0;

            $sum = RatingArticles::find()->innerJoinWith('articles')->where(['user_id' => $user['user_id']])->sum('rating');
            $count = RatingArticles::find()->innerJoinWith('articles')->where(['user_id' => $user['user_id']])->count();
            $SUM += (!empty($sum)) ? $sum : 0;
            $COUNT += (!empty($count)) ? $count : 0;

            $sum = RatingNews::find()->innerJoinWith('news')->where(['user_id' => $user['user_id']])->sum('rating');
            $count = RatingNews::find()->innerJoinWith('news')->where(['user_id' => $user['user_id']])->count();
            $SUM += (!empty($sum)) ? $sum : 0;
            $COUNT += (!empty($count)) ? $count : 0;

            $rating = (!empty($SUM) && !empty($COUNT)) ? ($SUM/$COUNT) : 0;

            $userRating[$key]['rating'] = $rating;
        }
        ArrayHelper::multisort($userRating, ['rating'], [SORT_DESC]);

        return $userRating;
    }
}

[andku83]

СуПеР уЖЖЖаССС!!!

Какой смысл задавать вопрос и даже не смотреть(попробовать) ответы?!

[webplus]

Какой смысл задавать вопрос и даже не смотреть(попробовать) ответы?!

Переделал запрос, сделал как вы показали в примере:

Код: Выделить всё

        $articleQuery = Articles::find()
            ->leftJoin('rating_articles', Articles::tableName().'.id = rating_articles.articles_id')
            ->select(['user_id', 'rating']);

        $newsQuery = News::find()
            ->leftJoin('rating_news', News::tableName().'.id = rating_news.news_id')
            ->select(['user_id', 'rating']);

        $usersQuery = Profile2::find()
            ->select([Profile2::tableName().'.*', 'rating_sum'])
            ->leftJoin(
                ['rt' => (new \yii\db\Query())
                    ->from(['sub' => $articleQuery->union($newsQuery)])
                    ->select(['user_id', 'rating_sum' => new \yii\db\Expression('SUM(rating)/COUNT(*)')])
                    ->groupBy('user_id')],
                Profile2::tableName().'.user_id = rt.user_id'
            )
            ->orderBy(['rating_sum' => SORT_DESC]);

        $resUsers = $usersQuery->all();

добавил только в select деление на общее количество: new \yii\db\Expression('SUM(rating)/COUNT(*)')])

[andku83]

добавил только в select деление на общее количество: new \yii\db\Expression('SUM(rating)/COUNT(*)')])

Здесь вы говорили о сумме всех баллов, а в своем запросе реализовали средний балл по публикациям.
Кстати вместо того чтобы делить сумму на количество нужно было использовать функцию AVG().
И в моем примере для реализации среднего значения достаточно SUM() изменить на AVG.

[webplus]

Здесь вы говорили о сумме всех баллов, а в своем запросе реализовали средний балл по публикациям.
Кстати вместо того чтобы делить сумму на количество нужно было использовать функцию AVG().
И в моем примере для реализации среднего значения достаточно SUM() изменить на AVG.

Но мне нужен бал от 1 до 5, так как эти балы служат для оценки статей. У пользователей при выводе должно быть от 1 до 5 звезд. и по этому я на count(*) поделил.
А просто SUM дает общую суму из всех балов

[andku83]

http://oracleplsql.ru/mysql-function-avg.html

[webplus]

http://oracleplsql.ru/mysql-function-avg.html

Спасибо, сделал AVG(rating) и все правильно выводит.
Просто с такими запросами мне не приходилось работать. Спасибо что помогли разобраться!

[webplus]

Добавил в запрос еще получение количества всех публикаций COUNT(DISTINCT sub.id) но показывает не правильно.
вот запрос, если можете то подскажите что не так.

Код: Выделить всё

        $articleQuery = Articles::find()
            ->leftJoin('rating_articles', Articles::tableName().'.id = rating_articles.articles_id')
            ->select([Articles::tableName().'.id', 'user_id', 'rating']);

        $newsQuery = News::find()
            ->leftJoin('rating_news', News::tableName().'.id = rating_news.news_id')
            ->select([News::tableName().'.id', 'user_id', 'rating']);

        $cryptoAnalyticsQuery = CryptoAnalytics::find()
            ->leftJoin('rating_crypto_analytics', CryptoAnalytics::tableName().'.id = rating_crypto_analytics.crypto_analytics_id')
            ->select([CryptoAnalytics::tableName().'.id', 'user_id', 'rating']);

        $analyticsQuery = Analytics::find()
            ->leftJoin('rating_analytics', Analytics::tableName().'.id = rating_analytics.analytics_id')
            ->select([Analytics::tableName().'.id', 'user_id', 'rating']);

        $usersQuery = self::find()
            ->select([self::tableName().'.*', 'rating_sum', 'count_sum'])
            ->leftJoin(
                ['rt' => (new \yii\db\Query())
                    ->from(['sub' => $articleQuery->union($newsQuery)->union($analyticsQuery)->union($cryptoAnalyticsQuery)])
                    ->select([
                        'user_id',
                        'rating_sum' => new \yii\db\Expression('AVG(rating)'),
                        'count_sum' => new \yii\db\Expression('COUNT(DISTINCT sub.id)')
                    ])
                    ->groupBy('user_id')],
                self::tableName().'.user_id = rt.user_id'
            )
            ->orderBy(['rating_sum' => SORT_DESC]);
        if(!empty($user_id)) {
            $usersQuery->where([self::tableName().'.user_id' => $user_id]);
            return $usersQuery->one();
        } else {
            return $usersQuery->all();
        }

[andku83]

Как именно неправильно? показывает меньше чем ожидаете?
Попробуйте убрать DISTINCT

[webplus]

Как именно неправильно? показывает меньше чем ожидаете?
Попробуйте убрать DISTINCT

Убрал, но не помогло.
У меня у пользователя пять публикаций с разных таблиц, я заметил если ID в двух разных таблицах одинаковые, (например в таблице новостей есть ID с 4 и в таблице статей есть ID с 4) то оно считается как одна запись. Как то это странно.

[andku83]

как раз DISTINCT к этому и должен приводить, попробуйте:

Код: Выделить всё

new \yii\db\Expression('COUNT(*)')