I’ve been in full project mode all week and got a chance to really flex my SQL muscles again when I was creating a dropdown button that would display a user’s collections on the bottom of a queried drink recipe. It seemed easy in theory and proved a lot harder in execution. Here’s what I was going for:

I've searched for an Old Fashioned recipe and found one! But when I click the + button at the bottom of the recipe, I have nowhere to save it because I haven't created a collection yet.

And look at that! Now that I have a collection set up and this recipe isn't in it, the name's rendering in the dropdown!

I gave the collection name a click and viola! The recipe has been saved and I can take a look at it whenever I'd like.

Simple, right? The query doesn’t look so simple and was a bit of a monster to write.

Copycopy code to clipboard
1static getByUserIdAndDrinkId(user_id, drink_id) {
2    return db
3    .query(`
4    SELECT id, name, bool_or(drink_exists) as "drink_exists" FROM
5      (SELECT c.id, c.name,
6        CASE WHEN s.drink_id = $1
7          THEN TRUE
8          ELSE FALSE
9        END drink_exists
10      FROM collections c LEFT JOIN saves s on s.collection_id = c.id
11      WHERE c.user_id = $2) as saves
12    GROUP BY saves.id, saves.name;
13    `, [drink_id, user_id])
14    .then((collections) => collections.map((collection) => new this(collection)));
15  }

And that's that! Here’s a link to the app on Heroku. To check out my code on Github, click here.