VPF::[Postgres]2 JOIN одной таблицы с разными условиями

Alx

Дата 6.3.2014, 11:32 (ссылка)

(нет голосов)

Загрузка ...

Ajaxy

Профиль
Группа: Комодератор
Сообщений: 2903
Регистрация: 26.11.2003
Где: Cutopia

Репутация: нет
Всего: 78

Привет! Помогите, пожалуйста составить запрос. Задача такая (кажется, очень простая):

Есть таблица users и таблица posts. В таблице posts есть поле author_id и поле accepted. Необходимо выбрать пользователей и добавить к ним по два счетчика: кол-во опубликованных постов и общее количество постов.

Пробовал все варианты INNER/LEFT/RIGHT JOIN, все время не то, что нужно возвращает, вот один из моих вариантов:

Код


SELECT users.*,
       count(posts.author_id) AS posts_count,
       count(accepted_posts.author_id) AS accepted_posts_count
   FROM users
   LEFT JOIN posts ON posts.author_id = users.id
   LEFT JOIN posts accepted_posts ON accepted_posts.author_id = users.id AND accepted_posts.accepted = 1
   GROUP BY users.id
   ORDER BY accepted_posts_count DESC

Однако, этот код возращает не то, что нужно (вообще непредсказуемый результат).

Заранее спасибо!

Это сообщение отредактировал(а) Alx - 7.3.2014, 02:47

--------------------

Akina

Дата 6.3.2014, 16:49 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

При решении конкретных задача СУБД указывать надо! и постить в соотв. раздел, а не в общие вопросы.

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

Akina

Дата 6.3.2014, 17:49 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

Так, в качестве примера...

Код


SELECT users.*, 
       COUNT(posts.author_id) AS posts_count, 
       SUM(posts.accepted = 1) AS accepted_posts_count 
FROM users 
LEFT JOIN posts 
  ON posts.author_id = users.id

Для MySQL - нормально, для MS SQL - бредятина.

--------------------

Alx

Дата 7.3.2014, 00:20 (ссылка)

(нет голосов)

Загрузка ...

Ajaxy

Профиль
Группа: Комодератор
Сообщений: 2903
Регистрация: 26.11.2003
Где: Cutopia

Репутация: нет
Всего: 78

Akina, не думал, что это будет отличаться в зависимости от СУБД. Вообще говоря в данный момент у меня Postgres. Попробую твой код, спасибо!

--------------------

Alx

Дата 7.3.2014, 00:40 (ссылка)

(нет голосов)

Загрузка ...

Ajaxy

Профиль
Группа: Комодератор
Сообщений: 2903
Регистрация: 26.11.2003
Где: Cutopia

Репутация: нет
Всего: 78

Ругается на SUM(posts.accepted = 1):

Цитата

No function matches the given name and argument types.

А что это должно делать? Может "= 1" лишнее? Но вообще говоря я соврал для упрощения, на самом деле мне нужно делать фильтр не по accepted = 1, а по status = 15, так как у меня есть следующие статусы:

Цитата

      waiting:  0,
      declined: 5,
      returned: 10,
      accepted: 15

--------------------

Alx

Дата 7.3.2014, 01:09 (ссылка)

(нет голосов)

Загрузка ...

Ajaxy

Профиль
Группа: Комодератор
Сообщений: 2903
Регистрация: 26.11.2003
Где: Cutopia

Репутация: нет
Всего: 78

Мне удалось добиться результата с помощью одного или двух вложенных запросов.
Я правда не знаю, которое из этих решений лучше и в целом, насколько они оптимальны, не будут ли такие запросы слишком "тяжелыми".

Код


SELECT DISTINCT
    COUNT(posts.author_id) AS posts_count,
    accepted_posts.count AS accepted_posts_count
FROM users
LEFT OUTER JOIN posts
    ON posts.author_id = users.id
LEFT OUTER JOIN (SELECT author_id, COUNT(*) AS count FROM posts WHERE status = 15 GROUP BY author_id)
    AS accepted_posts
    ON accepted_posts.author_id = users.id
GROUP BY users.id, accepted_posts.count;

Код


SELECT DISTINCT
    posts.count AS posts_count,
    accepted_posts.count AS accepted_posts_count
FROM users
LEFT OUTER JOIN (SELECT author_id, COUNT(*) AS count FROM posts GROUP BY author_id)
    AS posts
    ON posts.author_id = users.id
LEFT OUTER JOIN (SELECT author_id, COUNT(*) AS count FROM posts WHERE status = 15 GROUP BY author_id)
    AS accepted_posts
    ON accepted_posts.author_id = users.id
GROUP BY posts_count, accepted_posts.count;

Это сообщение отредактировал(а) Alx - 7.3.2014, 01:09

--------------------

Alx

Дата 7.3.2014, 02:26 (ссылка)

(нет голосов)

Загрузка ...

Ajaxy

Профиль
Группа: Комодератор
Сообщений: 2903
Регистрация: 26.11.2003
Где: Cutopia

Репутация: нет
Всего: 78

Но тут возникает новая проблема: на эти запросы теперь нельзя наложить никакие дополнительные условия. Например, у меня есть необходимость добавить фильтр по времени:

Код


WHERE (posts.created_at >= '2014-03-02' AND posts.created_at <= '2014-03-09')

Однако, этот код придется добавлять к каждому подзапросу, что неудобно и не получится сделать, например, при использовании SQL-билдера (или, например, оформить как scope в Rails).

--------------------

Akina

Дата 7.3.2014, 07:58 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

Цитата(Alx @ 7.3.2014, 01:20

)

не думал, что это будет отличаться в зависимости от СУБД.

Задача имеет, конечно, общее решение, которое будет работать на любой СУБД. Но оно гарантированно будет неэффективно. Эффективное решение всегда использует особенности конкретного диалекта.

Цитата(Alx @ 7.3.2014, 01:40

)

Ругается на SUM(posts.accepted = 1):

Об том и речь.

Цитата(Alx @ 7.3.2014, 01:40

)

А что это должно делать? Может "= 1" лишнее?

Здесь используется фича MySQL. Сравнение даёт булев результат. MySQL интерпретирует True как единицу, а False как ноль.
С постгрессе есть CASE? должен быть, по идее... тогда

Код


SELECT users.*,  
       COUNT(posts.author_id) AS posts_count,  
       SUM(CASE posts.accepted WHEN 15 THEN 1 ELSE 0 END) AS accepted_posts_count  
FROM users  
LEFT JOIN posts  
  ON posts.author_id = users.id

--------------------

Zloxa

Дата 9.3.2014, 22:26 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(Akina @ 7.3.2014, 08:58

)

по идее... тогда

Групбай нужен и users.* нельзя

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

tzirechnoy

Дата 10.3.2014, 10:18 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1173
Регистрация: 30.1.2009

Репутация: 2
Всего: 16

Код

SELECT  users.id, 
      COALESCE(count(posts), 0) AS posts, 
      COALESCE(SUM(CASE WHEN status=0 THEN 1 ELSE 0 END), 0) AS waiting,
      COALESCE(SUM(CASE WHEN status=5 THEN 1 ELSE 0 END), 0) AS declined,
      COALESCE(SUM(CASE WHEN status=10 THEN 1 ELSE 0 END), 0) AS returned,
      COALESCE(SUM(CASE WHEN status=15 THEN 1 ELSE 0 END), 0) AS accepted
FROM users 
        LEFT JOIN posts
             ON posts.author_id=users.id
   GROUP BY users.id;

Zloxa

Дата 10.3.2014, 10:22 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(tzirechnoy @ 10.3.2014, 11:18

)

COALESCE(SUM(CASE WHEN status=0 THEN 1 ELSE 0 END), 0)

эквивалентно

Код


count(CASE WHEN status=0 THEN 1 END)

То же самое, но запись покороче

Цитата(tzirechnoy @ 10.3.2014, 11:18

)

COALESCE(count(posts), 0)

здесь коалес лишний, count null не возвращает.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »