Guest relationship support queries for api-pls

many-to-many.sql

-- Select one
WITH toppings_of_pizzas AS (
  SELECT
    pizza_id,
    array_agg(topping_id) AS topping_ids
  FROM pizza_topping
  WHERE pizza_id=1
  GROUP BY pizza_id
)

SELECT
  pizza.*,
  (SELECT topping_ids FROM toppings_of_pizzas) AS topping_ids
FROM pizza
WHERE id=1;
  


-- Select all
\timing ON

WITH toppings_of_pizzas AS (
  SELECT
    pizza_id,
    array_agg(topping_id) AS topping_ids
  FROM pizza_topping
  GROUP BY pizza_id
)

SELECT
  pizza.*,
  (SELECT topping_ids FROM toppings_of_pizzas WHERE pizza.id = toppings_of_pizzas.pizza_id) AS topping_ids
FROM pizza;

works-with-one-and-many.sql

-- Select one
\timing ON

WITH cats_by_owner AS (
  SELECT
    owner_id,
    array_agg(id) AS cat_ids
  FROM cat
  WHERE owner_id=1
  GROUP BY owner_id
)

SELECT
  person.*,
  (SELECT cat_ids FROM cats_by_owner) AS cat_ids
FROM person
WHERE id=1;
  



-- Select all
\timing ON

WITH cats_by_owner AS (
  SELECT
    owner_id, array_agg(id) AS cat_ids
  FROM cat
  GROUP BY owner_id
)

SELECT
  person.*,
  (SELECT cat_ids FROM cats_by_owner WHERE person.id = cats_by_owner.owner_id) AS cat_ids
FROM person;

works-with-one.sql

-- This is the original query. It works great for fetching one person
-- at a time, but isn't so good with fetching many.

WITH
  cat_ids AS (
    SELECT array(
      SELECT id
        FROM cat
        WHERE owner_id=1
    ) AS pet_ids
  )

SELECT *
  FROM person, cat_ids
  WHERE person.id = 1;