How To Read a Full Join?

Squery is using case class composition to read JOINed tables.
Let's say we have tables whose rows are represented by these 2 case classes:

case class Customer(id: Int, name: String) derives SqlReadRow
case class Phone(id: Int, number: String) derives SqlReadRow

Doing a FULL JOIN on them would look like this:

SELECT  c.id, c.name,
        p.id, p.number
FROM customers c
JOIN phones p ON p.customer_id = c.id     

The result can be expressed as a composition of the 2 case classes above:

case class CustomerWithPhone(c: Customer, p: Phone) derives SqlReadRow

Variables like c: Customer are expected to have corresponding column names in the query: c.id and c.name.
The final query is a composition of Customer and Phone, so it maps nicely in your head, it is easier to read and manipulate.

You could have additional columns like a COUNT/SUM or whatever you need in CustomerWithPhone query result.

Full example:

import ba.sake.squery.{*, given}

case class Customer(id: Int, name: String) derives SqlReadRow
case class Phone(id: Int, number: String) derives SqlReadRow

case class CustomerWithPhone(c: Customer, p: Phone) derives SqlReadRow

def customerwithPhones: List[CustomerWithPhone] = ctx.run {
  sql"""
    SELECT c.id, c.name,
          p.id, p.number
    FROM customers c
    JOIN phones p ON p.customer_id = c.id
  """.readRows[CustomerWithPhone]()
}