Perform join on multiple tables

Question

I have three tables right now in a postgres DB:

Artists             
ID  Name        

Shows
ID  Date    Venue   Price   Is_sold Ages    Time    Multi_day   Pit     

Artists_shows       
ID  Show_id Artist_id

A show can have one or many artists; eventually I'm going to produce JSON for all shows that looks like this:

[
    {
    "bands": [
        "Domino And The Derelicts",
        "Creeper",
        "Lesbros"
    ],
    "date": "2015-12-26T08:00:00.000Z",
    "venue": "Stork Club, Oakland",
    "time": null,
    "soldOut": false,
    "pit": false,
    "multiDay": false,
    "ages": "21+",
    "price": null
    }

...]

Right now I'm doing this pretty inefficiently.

First, I'm running a query: SELECT * FROM shows WHERE date >= ${date} ORDER BY date, where date is today's date.

Then, I'm taking the result and running a for loop through each individual show that results and running this query: SELECT artist.* FROM artist INNER JOIN artist_shows ON artist.id = artist_shows.artist_id AND artist_shows.show_id=${id}, where id is each id from the results of the first query on the Shows table.

I'm then stitching together the results of both queries to produce the resulting JSON.

Is there a way to perform a single JOIN operation on my tables so that I can avoid the loop that I'm running? I assume that there might be a way to merge my existing join with my first query so that I get a table with all of the artists and all of the show details in one table. The thing that is tripping me up is the fact that a show can have many artists associated with it.

Answer 1

You can only return single row results so grouping in the way you are envisioning is not all that natural. There is a way, however, to use aggregate functions to combine groups and get the exact JSON result you are looking for.

SELECT
  row_to_json(all_shows)
FROM (
  SELECT
    array_agg(bands) bands,
    shows.*
  FROM shows
  JOIN (
    SELECT show_id, array_agg(artists.name)
    FROM artist_shows
    JOIN artists ON (artist_shows.artist_id = artists.id)
    GROUP BY show_id
  ) bands ON bands.show_id = shows.id
  WHERE date >= now()
) all_shows

Here we are using a subquery (bands) to group all artists names based on the show id and then join the aggregate array into the show row. Lastly you can combine all rows and return a JSON representation of the data. If you don't care to have Postgres wrap everything up into JSON then you can just use what is in the initial FROM clause.

Answer 2

My coding mentor suggested that I break this up into two queries because it's going to be more performant, and then to do the assembly in js:

buildArtistHashFromArtistsByShow(artistsByShow) {
    const showArtistsHash = {};

    for (const artist of artistsByShow) {
        if (!(artist.show_id in showArtistsHash)) {
            showArtistsHash[artist.show_id] = [artist.name];
        } else {
            showArtistsHash[artist.show_id].push(artist.name);
        }
    }

    return showArtistsHash;
}

buildParsedShows(shows, artistsHash) {
    const parsedShows = [];
    for (const show of shows) {
        const artists = artistsHash[show.id];
        parsedShows.push({
            'bands': artists,
            'date': show.date,
            'venue': show.venue,
            'time': show.time,
            'soldOut': show.is_sold,
            'pit': show.pit,
            'multiDay': show.multi_day,
            'ages': show.ages,
            'price': show.price,
        });
    }
    return parsedShows;
}

async fetchParsedShowsWithGroupedBandsAfterToday() {
    try {
        const shows = await this.db.query(`
            SELECT shows.* FROM shows WHERE date >= now() ORDER BY date
        `);


        const artistsByShow = await this.db.query(`
            SELECT artist_shows.show_id, artist.*
                FROM artist_shows
                INNER JOIN artist ON artist_shows.artist_id = artist.id
                WHERE artist_shows.show_id IN (SELECT shows.id
                    FROM shows
                    WHERE date >= now())
        `);

        const artistsHash = this.buildArtistHashFromArtistsByShow(artistsByShow);

        const parsedShows = this.buildParsedShows(shows, artistsHash);

        return parsedShows;

    } catch (error) {
        this.logger.error(error);
    }
}