I have such query:
SELECT "Movie"."title",
"Movie"."id",
"Movie"."publicationDate",
"Movie"."authorID",
"Author"."photoURL" as "authorPhoto",
concat("Author"."name", ' ', "Author"."surname") as "authorName",
"Movie"."seriesID",
"Series"."name" as "seriesName",
"Series"."logoURL" as "seriesLogo",
"Movie"."languageID" as "language",
"Movie"."ratingUp",
"Movie"."ratingDown",
"userWatched"."watchedDate",
CASE WHEN "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
THEN TRUE
ELSE FALSE
END as "favourited",
CASE WHEN "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
THEN TRUE
ELSE FALSE
END as "onWatchlist",
CASE WHEN "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' AND "userWatched"."watchedDate" IS NOT NULL
THEN TRUE
ELSE FALSE
END as "watched",
count(DISTINCT "overallWatched"."watchedDate") as "viewCount"
FROM "Movie"
LEFT JOIN "Series" ON "Series"."id" = "Movie"."seriesID"
LEFT JOIN "Author" ON "Author"."id" = "Movie"."authorID"
LEFT JOIN "UserMovieFavourited" ON "UserMovieFavourited"."movieID" = "Movie"."id"
AND "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchlist" ON "UserMovieWatchlist"."movieID" = "Movie"."id"
AND "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchProgress" as "userWatched" ON "userWatched"."movieID" = "Movie"."id"
AND "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchProgress" as "overallWatched" ON "overallWatched"."movieID" = "Movie"."id"
GROUP BY "Movie"."id",
"Author"."photoURL",
"Author"."name",
"Author"."surname",
"Series"."name",
"Series"."logoURL",
"userWatched"."watchedDate",
"UserMovieFavourited"."userID",
"UserMovieWatchlist"."userID",
"userWatched"."userID"
In MySQL i needed only one column for grouping that's Movie.id. For postgresql I needed to add 10 columns to make this query work as shown above.
Is this natural for postgresql or this query should be refactored to remove need of so many grouping columns?
Asked by Paweł Madej
(123 rep)
Feb 11, 2020, 06:08 PM
Last activity: May 23, 2025, 11:04 AM
Last activity: May 23, 2025, 11:04 AM