Filters for query with multiple joins

I have information in 3 tables - Store, Invoice, Invoice Payments.

Store ----1to*---- Invoice ----1to*----- InvoicePayments
id ------------------ id --------------------- id
name -------------------------------------- type
---------------------------------------------- amount

Is there a way to construct a questtion to find total payments from InvoicePayments by Store? I would like to use two filters (Store and Payment Type) in the question.

Closest I came to achieving this is by using a SQL.

select sum(amount)
from invoice_payment ip
LEFT JOIN invoice i ON ip.invoice_id =
LEFT JOIN store s ON i.store_id =
[[where = {{Store_Name}}]]
[[and ip.payment_type = {{pmt_type}}]]

It works, but not optimal. User has to enter the store name and payment type, as opposed to selecting from a drop down. Any way to make it work like filters?

1 Like