[Question] Pagination with left join?

[Froidoh]

Hello!

I am interested in using welds, but before I dive deeper, I find it difficult to find information about joins...

Can welds do queries like:

SELECT
    i.invoice_id,
    i.customer_id,
    i.invoice_date,
    i.total_amount,
    li.line_item_id,
    li.description,
    li.amount
FROM
    (SELECT
        id AS invoice_id,
        customer_id,
        invoice_date,
        total_amount
     FROM
        invoices
     ORDER BY
        invoice_date DESC
     LIMIT 10 OFFSET :offset) AS i
JOIN
    line_items li ON i.invoice_id = li.invoice_id
ORDER BY
    i.invoice_id, li.line_item_id;

?

Thanks in advance!

[Froidoh]

I love this library... it's "solved" in the sense that it maybe issues two queries instead of one, but even if that is the case, DataSet rules for this!

    let q = Invoice::all().limit(2).offset(1).include(|i| i.line_items);
    let q = q.to_sql(welds::Syntax::Sqlite);

    tracing::info!("Query: {q:?}");

leads to:

["SELECT t1."id", t1."customer_id", t1."invoice_no", t1."paid_on", t1."vat_value" FROM invoice t1 ORDER BY 1 LIMIT 2 OFFSET 1 ", "SELECT t2."id", t2."invoice_id", t2."description", t2."unit", t2."amount", t2."price_per_unit", t2."price" FROM invoice_line_item t2 WHERE ( t2.invoice_id IN (SELECT t1.id FROM invoice t1 ORDER BY 1 LIMIT 2 OFFSET 1 ) )"]

Thanks so much for everyone involved in this!

Now the next step is making this work with a pivot table for persons and addresses...