PostgreSQL: column must appear in the GROUP BY clause or be used in an aggregate function

Question

Есть четыре таблицы. В одной - данные об изображениях как о файлах:

CREATE TABLE public.images (
    id bigserial NOT NULL PRIMARY KEY,
    hash character(50) NOT NULL,
    type public.image_type NOT NULL,
    uploader_type public.entity_type NOT NULL,
    uploader_id bigint NOT NULL,
    uploader_ip inet NOT NULL
);

В другой - альбомы пользователей:

CREATE TABLE public.albums (
    id bigserial NOT NULL PRIMARY KEY,
    access public.access DEFAULT 'private'::public.access NOT NULL,
    name text NOT NULL,
    description text DEFAULT ''::text,
    owner_type public.entity_type NOT NULL,
    owner_id bigint NOT NULL,
    poster_image_id bigint,
    comments public.access DEFAULT 'public'::public.access NOT NULL,
    anonymous_comments_only boolean DEFAULT false NOT NULL
);

Третья связывает первую и вторую. То есть, если images - это информация о файлах изображений, то следующая таблица указывает, в каких альбомах какие изображения имеются:

CREATE TABLE public.album_images (
    id bigserial NOT NULL PRIMARY KEY,
    album_id bigint NOT NULL,
    image_id bigint NOT NULL,
    sha1 character(40) NOT NULL,
    owner_type public.entity_type NOT NULL,
    owner_id bigint NOT NULL,
    description text NOT NULL,
    last_comment_number bigint DEFAULT 0 NOT NULL,
    saved timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Здесь sha1 - не тот же хеш, что и hash в images. В теории, он нужен для отсева дубликатов по изображению и описанию.

И четвёртая таблица - лайки и дизлайки изображений, представленные как "рейтинги" (булево поле rating кажет, це лайк чи дизлайк):

CREATE TABLE public.media_ratings (
    id bigserial NOT NULL PRIMARY KEY,
    media_type public.media_type NOT NULL,
    media_id bigint NOT NULL,
    user_id bigint NOT NULL,
    rating boolean NOT NULL,
    datetime timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Я делаю запрос для определения трёх самых залайканных изображений из альбомов. То есть, выбираю владельца альбома, сам альбом и информацию об изображении, группирую по album_images.id, а затем сортирую по разнице лайков и дизлайков и ограничиваю результат до трёх строк. Вот таким образом:

SELECT a.owner_type, a.owner_id, a.name, i.hash, i.type
FROM albums a
JOIN album_images ai ON a.id = ai.album_id
JOIN images i ON i.id = ai.image_id
JOIN media_ratings mr ON mr.media_id = ai.id
GROUP BY ai.id
ORDER BY ( COUNT(mr.rating = true) - COUNT(mr.rating = false) ) DESC
LIMIT 3;

На что получаю ошибку: column "a.owner_type" must appear in the GROUP BY clause or be used in an aggregate function. Если добавить a.owner_type в GROUP BY, начинает по порядку ругаться на все остальные выбираемые поля, пока не сгруппирую по ним всем. Но мне нужна группировка только по изображениям в альбомах, так как боюсь, что если забью все поля в GROUP BY, требуемый результат сломается. На стаковерфлоу есть похожие вопросы, где советуют добавить DISTINCT. Добавил, не пашет. Что же делать?

Answer 1

Здесь не требуется присоединять таблицу media_ratings т.к. из нее нужна только сводная разница количеств лайков и дизлайков:

SELECT a.owner_type, a.owner_id, a.name, i.hash, i.type
FROM albums a
JOIN album_images ai ON a.id = ai.album_id
JOIN images i ON i.id = ai.image_id
ORDER BY (
  SELECT COUNT(mr.rating) filter (where mr.rating = true) 
       - COUNT(mr.rating) filter (where mr.rating = true)
  from media_ratings mr where mr.media_id = ai.id
) DESC
LIMIT 3;

Answer 2

Нашёл ещё решение от себя. Вместо выборки в ORDER BY присоединяем к основной таблице вместо media_requests полученный от внутреннего запроса список айдишников изображений, уже упорядоченный, как нам нужно, а затем ещё раз указываем сортировку снаружи. Вот таким образом:


SELECT a.owner_type, a.owner_id, a.name, i.hash, i.type
FROM albums a
JOIN album_images ai ON ai.album_id = a.id
JOIN images i ON i.id = ai.image_id
JOIN (
    SELECT ai.id, ( COUNT(mr.rating = true) - COUNT(mr.rating = false) ) AS diff
    FROM album_images ai
    JOIN media_ratings mr ON mr.media_id = ai.id
    WHERE mr.media_type = 'image'
    GROUP BY ai.id
    ORDER BY diff DESC
    LIMIT 3
) t ON t.id = ai.id
ORDER BY t.diff DESC;

Такой метод наверняка работает медленнее, но его плюс в том, что при пустой таблице media_requests полученный результат тоже будет пустым, пустые места рейтинга в нём не заполнятся случайными строками из album_images.

БЛОГ НА HUSL

PostgreSQL: column must appear in the GROUP BY clause or be used in an aggregate function

Ответы (2 шт):