Рейтинг пользователей из двух таблиц публикаций одним запросом
Рейтинг пользователей из двух таблиц публикаций одним запросом
Здравствуйте!
Есть таблица 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
Есть таблица 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
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
имеется ввиду количество записей из публикаций или среднее значение рейтинга этих публикаций?
можно
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Вывести и отсортировать по возрастанию юзеров по полю rating
Например у одного юзера 10 статей и у всех оценка 5 балов и 4 новости у них оценка 3 у всех.
У другого юзера 5 статей с оценками 5 у каждой и 10 новостей с оценкой 5 у каждой.
То итог:
юзер 2 выводится первым с балом 75
юзер 1 выводится вторым с балом 62
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
а если у первого 5 по 5, а у второго 100 по 1 - какой порядок?
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Вот так сделал:
Может это тяжелый запрос и можно проще?
Код: Выделить всё
$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();
Последний раз редактировалось webplus 2018.06.22, 20:11, всего редактировалось 1 раз.
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Приблизительно (а может и именно так) это должно выглядеть:
и не забыть в модель добавить поле rating_sum
Код: Выделить всё
$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]);
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Здесь вы говорили о сумме всех баллов, а в своем запросе реализовали средний балл по публикациям.
Кстати вместо того чтобы делить сумму на количество нужно было использовать функцию AVG().
И в моем примере для реализации среднего значения достаточно SUM() изменить на AVG.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
В общем сделал вот так:
Код: Выделить всё
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;
}
}
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
СуПеР уЖЖЖаССС!!!
Какой смысл задавать вопрос и даже не смотреть(попробовать) ответы?!
Какой смысл задавать вопрос и даже не смотреть(попробовать) ответы?!
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Переделал запрос, сделал как вы показали в примере:
Код: Выделить всё
$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();
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
andku83 писал(а): ↑2018.06.22, 20:12 Здесь вы говорили о сумме всех баллов, а в своем запросе реализовали средний балл по публикациям.
Кстати вместо того чтобы делить сумму на количество нужно было использовать функцию AVG().
И в моем примере для реализации среднего значения достаточно SUM() изменить на AVG.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Но мне нужен бал от 1 до 5, так как эти балы служат для оценки статей. У пользователей при выводе должно быть от 1 до 5 звезд. и по этому я на count(*) поделил.andku83 писал(а): ↑2018.06.22, 20:12 Здесь вы говорили о сумме всех баллов, а в своем запросе реализовали средний балл по публикациям.
Кстати вместо того чтобы делить сумму на количество нужно было использовать функцию AVG().
И в моем примере для реализации среднего значения достаточно SUM() изменить на AVG.
А просто SUM дает общую суму из всех балов
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Спасибо, сделал AVG(rating) и все правильно выводит.
Просто с такими запросами мне не приходилось работать. Спасибо что помогли разобраться!
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Добавил в запрос еще получение количества всех публикаций 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();
}
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Как именно неправильно? показывает меньше чем ожидаете?
Попробуйте убрать DISTINCT
Попробуйте убрать DISTINCT
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
Убрал, но не помогло.
У меня у пользователя пять публикаций с разных таблиц, я заметил если ID в двух разных таблицах одинаковые, (например в таблице новостей есть ID с 4 и в таблице статей есть ID с 4) то оно считается как одна запись. Как то это странно.
Сайт по работе в Украине: https://jobis.com.ua/. Сайт по поиску строителей: https://stroyzakaz.com.ua/
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Создание сайтов в Киеве: https://webplus.com.ua/ по доступной цене.
Re: Рейтинг пользователей из двух таблиц публикаций одним запросом
как раз DISTINCT к этому и должен приводить, попробуйте:
Код: Выделить всё
new \yii\db\Expression('COUNT(*)')