Does Postgres have a way to filter by count of a entry's related table?

Question

Okay admittedly that title wasn't the best, but I don't know how else to ask this...

I'm using the Sequel gem with Postgres. I have a database with Playlists and Tracks, and I've filtered the Tracks by a specific attribute (so, I have a subset of the tracks).

I'm then using Playlists.where(tracks: filtered_tracks) to filter the playlists down to the ones that contain the subset of tracks.

However, is it possible to only select playlists that have two or more Tracks from the subset of tracks? Say, something like Playlists.where(tracks: filtered_tracks, at_least: 2).

Answer 1

Some pointers to a final answer...

I assume your tables are defined in a way similar to this one:

CREATE TABLE playlists
(
    playlist_id integer PRIMARY KEY,
    playlist_name text
) ;

CREATE TABLE playlists_x_tracks
(
    playlist_id integer,
    track text,
    PRIMARY KEY (playlist_id, track)
) ;

And that we populate them with some data:

INSERT INTO playlists (playlist_id, playlist_name)
VALUES 
    (1, 'list 1'), 
    (2, 'list 2');

INSERT INTO playlists_x_tracks (playlist_id, track) 
VALUES 
    (1, 'track a'), 
    (1, 'track b'), 
    (1, 'track c'),
    (1, 'track d'),
    (2, 'track a'),
    (2, 'track e') ;

Using SQL, and assuming your filtered_tracks would be 'track a', 'track b' and 'track x' you get the answer you want by executing the following query:

SELECT
    *
FROM
    playlists
WHERE
    (SELECT 
        count(*) 
    FROM
        playlists_x_tracks 
    WHERE
        playlists_x_tracks.playlist_id = playlists.playlist_id
        AND track in ('track a', 'track b', 'track x')
    ) >= 2 ;

How or whether this SQL statement can be "back-translated" to Sequel I am not sure. I really don't know how to add a SELECT within a WHERE using Sequel. However, you can always take advantage of the fact that, if necessary, Sequel will let you use custom SQL directly, which means you could actually write your SQL statement and execute it.