0

I have this query:

SELECT  
(SELECT COUNT(media_id) FROM likes WHERE like_state = true AND media_id = ?) AS likes, 
(SELECT COUNT(media_id) FROM likes WHERE like_state = false AND media_id = ?) AS dislikes, 
(SELECT media_views FROM media_thumbnail WHERE media_id = ?) AS views;

It works fine but I'm not sure if this is the best way. Any recommendations?

I don't think this requires any explanation, but... It counts how many likes, or dislikes are in function of the like_state (true = like, false = dislike) and returns the view count of the associated video.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jean Lambert
  • 131
  • 2
  • 10

2 Answers2

2

It's definitely faster to use a single SELECT with two counts on table likes instead of two separate SELECT:

SELECT COUNT(    like_state OR NULL) AS likes
     , COUNT(NOT like_state OR NULL) AS dislikes
     ,(SELECT media_views FROM media_thumbnail WHERE media_id = ?) AS views
FROM   likes
WHERE  media_id = ?;

There are corner-case differences between this query with an uncorrelated subquery, your original query with two implicit CROSS JOIN (a comma , in the FROM list is almost identical to a CROSS JOIN) as well as Gordon's answer with a CROSS JOIN to the last subquery.

The first two subqueries always return a row, so a CROSS JOIN cannot eliminate the result. But the third query might not find the given media_id in table media_thumbnail and return no row. A CROSS JOIN to that results in no row for the whole query.

My query with a subquery in the SELECT list converts no row to a null value and still returns a result row. The same could be achieved with LEFT JOIN ... ON true:

My subquery in the SELECT raises an exception if it returns more than one row - which cannot happen if media_thumbnail.media_id is defined UNIQUE or PRIMARY KEY, and probably should not happen in any case.

About conditional counts:

The pretty and clean syntax would be with a FILTER clause:

SELECT COUNT(*) FILTER (WHERE     like_state) AS likes
     , COUNT(*) FILTER (WHERE NOT like_state) AS dislikes
...

A bit more verbose, but may be easier to read. Performance is basically the same.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Your query is fine, although I would be more inclined to write it as: 

SELECT SUM( (l.like_state = true)::int ) as likes,
       SUM( (l.like_status = false)::int ) as dislikes,
       MAX(mt.media_views) as media_views
FROM likes l CROSS JOIN
     (SELECT media_views FROM media_thumbnail WHERE media_id = ?
     ) mt
WHERE l.media_id = ?;

This saves the overhead of scanning the likes table one time.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786