How to do WHERE ... IN ... with subquery

[jly36963]

Hey! I want to do something like

-- users table
-- purchases table (many-to-many relationship, lookup between users/products)
-- products table

SELECT
    *
FROM
    products
WHERE
    products.id IN(
        SELECT
            product_id FROM purchases
        WHERE
            purchases.user_id = $1);

I see cake/where has in and exists_in_query -- is there something that supports in_query (like in the example below)?

import cake/select as cs
import cake/where as cw
import gleam/io
import gleam/list
import gleam/pgo
import gleam/string
import pg_utils/helpers.{param_to_value, read_query_to_sql}

pub fn get_user_purchases(db: pgo.Connection, user_id: String) {
  let sub_query =
    cs.new()
    |> cs.from_table("purchases")
    |> cs.select(cs.col("product_id"))
    |> cs.where(cw.eq(cw.col("user_id"), cw.string(user_id)))
    |> cs.to_query

  let #(sql, raw_params) =
    cs.new()
    |> cs.from_table("products")
    |> cs.select(cs.col("*"))
    |> cs.where(cw.in_query(cw.col("id"), sub_query))
    |> cs.to_query
    |> read_query_to_sql

  let params = list.map(raw_params, param_to_value)
  io.println(sql)
  io.println(string.inspect(params))
}

[inoas]

It is indeed possible but not with public APIs, see PR.

[jly36963]

Works great in 0.13.0, thank you 🙏