MySQL "Тенденции"

Question

В настоящее время я выбираю "идеи" из своей базы данных, но еще одно требование состоит в том, чтобы иметь возможность получить "самые популярные идеи", то есть 10 самых популярных идей за последние 7 дней.

Мой запрос на выбор "идей" таков:

  SELECT
  t.id AS 'id',
  CONCAT(t.first_name, ' ', SUBSTRING(t.last_name,1,1)) AS 'name',
  t.votes_up,
  t.votes_down,
  t.votes_aggregate,
  GROUP_CONCAT(DISTINCT tags.name) AS 'tags',
  t.createdon AS 'timestamp'
  FROM (
    SELECT
      ideas.id, first_name, last_name, createdon,
      COALESCE(SUM(case when value > 0 then value end),0) votes_up,
      COALESCE(SUM(case when value < 0 then value end),0) votes_down,
      COALESCE(SUM(value),0) votes_aggregate
      FROM ideas
      LEFT JOIN votes ON ideas.id = votes.idea_id
      GROUP BY ideas.id
  ) as t
  LEFT JOIN tags_rel ON t.id = tags_rel.idea_id
  LEFT JOIN tags ON tags_rel.tag_id = tags.id

Как бы мне получить и отобразить все голоса, но получить только те "идеи", за которые проголосовали (votes_up) в течение последних 7 дней и заказывается на сумму votes_up?

Это моя попытка:

  SELECT
      t.id AS 'id',
      CONCAT(t.first_name, ' ', SUBSTRING(t.last_name,1,1)) AS 'name',
      t.votes_up,
      t.votes_down,
      t.votes_aggregate,
      GROUP_CONCAT(DISTINCT tags.name) AS 'tags',
      t.createdon AS 'timestamp'
      FROM (
        SELECT
          ideas.id, first_name, last_name, createdon,
          COALESCE(SUM(case when value > 0 then value end),0) votes_up,
          COALESCE(SUM(case when value < 0 then value end),0) votes_down,
          COALESCE(SUM(value),0) votes_aggregate
          FROM ideas
          LEFT JOIN votes ON ideas.id = votes.idea_id
          GROUP BY ideas.id
      ) as t
      LEFT JOIN tags_rel ON t.id = tags_rel.idea_id
      LEFT JOIN tags ON tags_rel.tag_id = tags.id
  WHERE t.published = 1
  AND (
    SELECT ideas.id,
    COALESCE(SUM(case when value > 0 then value end),0) votes_up
    FROM ideas
    LEFT JOIN votes ON ideas.id = votes.idea_id
    WHERE votes.`timestamp` > (NOW() - INTERVAL 7 DAY)
    GROUP BY ideas.id
  ) as v
  GROUP BY t.id
  ORDER BY v.votes_up DESC

Но я получаю ошибку for the right syntax to use near 'as v GROUP BY t.id ORDER BY v.votes_up DESC LIMIT 10'

Answer 1

Вы используете 'AS v' в предложении WHERE, что невозможно:

WHERE t.published = 1
  AND (
    SELECT ideas.id,
    COALESCE(SUM(case when value > 0 then value end),0) votes_up
    FROM ideas
    LEFT JOIN votes ON ideas.id = votes.idea_id
    WHERE votes.`timestamp` > (NOW() - INTERVAL 7 DAY)
    GROUP BY ideas.id
  ) as v

Answer 2

Попробуйте это, в своем операторе SELECT вы выполняете подзапрос, в своем WHERE вы проверяете, не равен ли он NULL, и можете ли вы заказать его (если я прав):

SELECT
      t.id AS 'id',
      CONCAT(t.first_name, ' ', SUBSTRING(t.last_name,1,1)) AS 'name',
      t.votes_up,
      t.votes_down,
      t.votes_aggregate,
      GROUP_CONCAT(DISTINCT tags.name) AS 'tags',
      t.createdon AS 'timestamp',
      (
         SELECT ideas.id,
         COALESCE(SUM(case when value > 0 then value end),0) votes_up
         FROM ideas
         LEFT JOIN votes ON ideas.id = votes.idea_id
         WHERE votes.`timestamp` > (NOW() - INTERVAL 7 DAY)
         GROUP BY ideas.id
      ) AS vup 
      FROM (
        SELECT
          ideas.id, first_name, last_name, createdon,
          COALESCE(SUM(case when value > 0 then value end),0) votes_up,
          COALESCE(SUM(case when value < 0 then value end),0) votes_down,
          COALESCE(SUM(value),0) votes_aggregate
          FROM ideas
          LEFT JOIN votes ON ideas.id = votes.idea_id
          GROUP BY ideas.id
      ) as t
      LEFT JOIN tags_rel ON t.id = tags_rel.idea_id
      LEFT JOIN tags ON tags_rel.tag_id = tags.id
  WHERE t.published = 1
  AND vup IS NOT NULL
  GROUP BY t.id
  ORDER BY vup DESC