PostgreSQL — Tricks or Treats! (Part 2)

Last time, I discussed three PostgreSQL tips that were used in Happ. Today, I’ll talk about a few more, some of which I utilized during the development of 1our, our final CS3216 project.

Inline Query Population

1our, being an adhoc job listing for NUS, has to store jobs, each of which contains a created_by field as a reference to the user who created that job. To query all the jobs with their respective creator, we’d do this in MongoDB:

Job
   .find(...) 
   .populate('created_by') 
   .exec(...);

In SQL, normally we’d have to query for the jobs first, then scan through the resulting lists and perform another user query for each job. However, PostgreSQL has exceptional support for JSON manipulation, which allows us to populate fields in a single query:

SELECT j.*, row_to_json(u.*) AS "creator"
FROM jobs j
INNER JOIN users s ON j.created_by = u.id;

The query above joins tables users and jobs together, at the same time combine the user’s columns into a JSON object creator. By doing everything in one single query, we’re able to avoid overheads if we populated in separate queries. Neat.

Easy foreach with LATERAL JOIN

To motivate people to start using 1our, we introduce offers, which provide a limited number of coupons that can be claimed (once) by users. Each coupon has a field claimed_by to indicate whether it has been claimed by some user.

Say we want to list all offers, at the same time count the number of available (not yet claimed) coupons, it’s easy with FILTER (as mentioned in the previous blog post):

SELECT o.*,
       COUNT(c.id) AS total_coupons_count,
       COUNT(c.id) FILTER (WHERE c.claimed_by IS NULL) 
            AS available_coupon_count
FROM offers o
INNER JOIN coupons c ON o.id = c.offer_id
GROUP BY o.id;

Say, if the current user has already claimed a coupon for an offer listed above, we also want to include that coupon object in the result. This means we have to perform a foreach loop through the queried offers, and assign the claimed coupon, if any.

PostgreSQL 9.3 and above supports a new JOIN query: LATERAL, which is perfect for our need. ALATERAL join is like a SQL foreach loop, in which PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter.

Let’s update our query to see what that means:

SELECT o.*,
       COUNT(c.id) AS total_coupons_count,
       COUNT(c.id) FILTER (WHERE c.claimed_by IS NULL) 
            AS available_coupon_count,
       -- The only change in the select query
       row_to_json(claimed_coupon) AS claimed_coupon
FROM offers o
INNER JOIN coupons c ON o.id = c.offer_id
-- Here comes our lateral join:
LEFT JOIN LATERAL (
    SELECT c2.* FROM coupons c2
    INNER JOIN users u ON c2.claimed_by = $currentUserId
    WHERE c2.offer_id = o.id -- o is accessible here
    LIMIT 1
) AS claimed_coupon ON TRUE
-- group by claimed_coupon so row_to_json would work
GROUP BY o.id, claimed_coupon.*;

The first part lists all the offers as before. Then, the JOIN LATERAL block iterates over the resulting offers, and finds the coupon of each offer that has been claimed by the current user. Note that the variable offers o in the first SELECT block is accessible in the LATERAL block.

LATERAL JOIN is a very powerful tool. You can chain multiple LATERAL JOIN blocks together to create a pipeline, each of which feeds data to the one after it. Without lateral joins, we would need to resort to custom functions for the same functionality.