Help me convert this raw query to Lucid

Hi all. I have 3 tables currently (customers, invoices, items).

  1. Customers has many Invoices
  2. Invoices has many Items

My legacy raw query is this

select customers.*, (select sum(price * quantity) - sum(discount_per_bag * quantity) from items inner join invoices on invoices.id = items.invoice_id where customers.id = invoices.customer_id) as invoice_items_total from customers;

This is my first attempt but I’m getting errors

Customer.query()
				.select(
					'*',
					DB.raw(
						'select sum(price * quantity) - sum(discount_per_bag * quantity) from items inner join invoices on invoices.id = items.invoice_id where customers.id = invoices.customer_id) as invoice_items_total'
					)
				)
				.fetch()

Anybody can help me with this? Thank you very much.

1 Like

My be you are looking something like this

Customer.query().with('invoices.items').get()

this query will return all invoices and it’s items under a customer.

1 Like