1

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?

  • 3
    MySQL query which group only my Movie.id will not run in any other dbms because it doesn't make sense. This is the correct way. One improvement you can make is to only join the necessary tables in the GROUP BY. – Eric Feb 11 at 18:11
  • 1
    Unrelated to your question, but: you should really avoid those dreaded quoted identifiers. wiki.postgresql.org/wiki/… – a_horse_with_no_name Feb 11 at 18:17
  • This quotes are generated by swifQL plain query export. – Paweł Madej Feb 11 at 18:23
  • 2
    In Postgres CASE WHEN "field" = 'value' THEN TRUE ELSE FALSE END may be replaced with simple "field" = 'value'... see fiddle. – Akina Feb 11 at 18:53
  • 1
    "Is this natural for postgresql" - yes, and for every other self respecting DBMS that cares for correct resulst – a_horse_with_no_name Feb 11 at 19:03
0

Imagine there is a row in "Movie" where id is 42.

Then imagine that there are two rows in "UserMovieWatchProgress" where "movieID" is 42.

Then the result of

"Movie" LEFT JOIN "UserMovieWatchProgress"
   ON "UserMovieWatchProgress"."movieID" = "Movie"."id" 

would be something like

 "Movie".id | "UserMovieWatchProgress"."movieID"
------------+------------------------------------
         42 |                                143
         42 |                                 93

Now consider the following query:

SELECT "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id"
GROUP BY "Movie".id;

By definition of GROUP BY, there can be only one result row per "Movie".id. Which number should we take for "UserMovieWatchProgress"."movieID"? That is unclear and not well defined, so this is forbidden by the SQL standard.

Now if PostgreSQL can deduce that there can be only one possible "UserMovieWatchProgress"."movieID", it will allow you to omit the column from the GROUP BY clause.


If you just want any of the "UserMovieWatchProgress"."movieID" in the query result, and you don't care which one, you can do the following in PostgreSQL:

SELECT DISTINCT ON ("Movie".id)
       "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id";

Perhaps that is what you are looking for.

|improve this answer|||||
  • There are two cases for this table. One I need to know if user watched movie. And second one how many times users overall watched movie so which user it does not matter that is why I needed to make this aliased same table twice to get this data – Paweł Madej Feb 11 at 18:44
  • You do care about individual users, else you wouldn't have "userWatched"."userID" in the SELECT list outside an aggregate function. – Laurenz Albe Feb 11 at 18:53
  • userWatched is first case when it matters. overallWatched is the case when it doesn't matter. – Paweł Madej Feb 11 at 18:58
  • Well, just add all columns that appear outside of aggregates to the GROUP BY clause and be done with it. I don't see the big problem. – Laurenz Albe Feb 11 at 19:06
  • 1
    I was using MySQL for many years for some easy hobby projects and now I decided to switch and I looked weird to me. But as I read above its normal I will be ok with such postgresql behavior and learn how it looks here. Thank you for explanations – Paweł Madej Feb 11 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.