У меня есть SQL-запрос для выдачи бейджей, который присваивает бейдж в зависимости от количества новых тем, созданных в определённой категории, при условии, что в первом посте темы содержится конкретная фраза (конкретный URL).
Запрос работает, но не совсем так, как задумано:
SELECT badge_posts.user_id, min(badge_posts.created_at) as granted_at, MIN(badge_posts.id) AS post_id
FROM badge_posts
JOIN topics ON topics.id = badge_posts.topic_id
WHERE category_id = 17
AND badge_posts.post_number = 1
AND badge_posts.raw LIKE '%dronescene.co.uk%'
GROUP BY badge_posts.user_id
HAVING COUNT(*) > 49
ORDER BY post_id DESC
У меня доступно четыре бейджа: бронзовый, серебряный, золотой и платиновый. Для бронзового я использую условие HAVING count(*) > 1, для серебряного — HAVING count(*) > 10 и так далее. Бейджи выдаются автоматически, проблем с этим нет.
Проблема, с которой я сталкиваюсь, заключается в том, что все бейджи присваиваются по ID первой темы в этой категории, а не по ID последней.
Например, у одного участника есть три бейджа, и все они были выданы за одну и ту же первую тему, которую он создал и которая соответствует моему условию WHERE, вместо самой последней темы, соответствующей этому условию.
Подскажите, пожалуйста, что я делаю не так с моим ORDER BY?
Если я заменю MIN на MAX, то получу ID самой последней темы (спасибо @JammyDodger), но, посмотрев на некоторые предварительные изменения, я думаю, что моя логика в любом случае в корне неверна
Использование MAX приводит к тому, что все остальные бейджи в этой группе выдаются для ID самой последней темы.
Это та же проблема, только наоборот.
Д’о
Моя логика была простой:
Проблема в том, что если кто-то получил бронзу в январе, серебро в марте, а золото в июне, то все три бейджа будут выданы для ID его первой темы ещё в январе
Я думаю, это потому, что вы запускаете это для пользователей, которые уже соответствуют всем четырем критериям. Если бы это было внедрено «с нуля», я полагаю, оно автоматически присваивало бы бейдж по дате последнего топика сразу после того, как пользователь преодолеет порог.
Для «legacy»-запуска, возможно, вам потребуется добавить LIMIT?
Редактирование: Возвращение первых (например) 25 подходящих записей на одного пользователя в одном запросе выходит за рамки моих текущих навыков.
Однако, в зависимости от того, сколько пользователей соответствуют нескольким бейджам, вы можете вручить legacy-бейджи вручную, используя информацию из запроса в Data Explorer? А затем запуск автоматического присвоения бейджей, описанный выше, должен присвоить правильный бейдж для правильного поста после того, как эти бейджи будут вручены.
Что-то вроде:
-- [params]
-- int :user_id = 1
-- int :limit = 50
SELECT bp.user_id, bp.created_at, bp.id as post_id
FROM badge_posts bp
JOIN topics t ON t.id = bp.topic_id
WHERE bp.user_id = :user_id
AND t.category_id = 17
AND bp.post_number = 1
AND bp.raw LIKE '%dronescene.co.uk%'
ORDER BY bp.created_at ASC
LIMIT :limit
Приношу извинения за возможные опечатки.
(Бронзовый бейдж должен быть реализуем с помощью версии MIN, а платиновый — с помощью MAX, так что остаются только варианты на 10 и 25).
