I have the following relationship in the database:
tbl_account-> PK id
tbl_invoice -> PK id
tbl_payment -> PK id
tbl_account_invoices_payments PK (account_id, invoice_id, payment_id)
I have models for Account, Invoice, Payment but not for tbl_account_invoice_payment.
Right now in the Invoice model I have this code to return all the invoice objects that match to a specific account_id and payment_id:
$sql = "SELECT invoice_id FROM tbl_account_invoice_payment WHERE account_id=':accountId 'AND payment_id=':paymentId'"; $params=array(':accountId'=>$account->id,':paymentId'=>$payment->id); return self::model()->findAllBySql($sql, $params);
I dont think this will work, and it feels so....well...dirty! is there a better way?
How can I get a set of filtered invoice objects back from the return value?
(I like the idea of setting up a HAS_MANY , through relationship in the model, but dont need the overhead of another model class for the relational table.)