Level 102
Can you show the raw query. Be aware that with() runs a seperate query. It is not a join
Mar 1, 2022
3
Level 1
Use a condition on a relationship query
I have the following eloquent query
$approved = InvoicesSprout::with(['payment' => function ($query) {
$query->select('id', 'pay_method')
->where('pay_method' , 0) #Important where condition - A1
->orWhere('pay_method' , 2);
}]
)
->with(['client' => function ($query) {
$query->select('id', 'cnme' , 'add1' , 'add2' , 'city' , 'stat' , 'emal'); }]
)
->with(['crm_type' => function ($query) {
$query->select('WLid', 'Cid' ); }]
)
->orderBy('dte_issued', 'desc')
->whereNotNull('dte_processed')
->whereNull('dte_collected')
->whereNull('voided')
->get();
I need that this query follows the condition A1 "See the comment on the code" on the relationship, i getting the response with 278 rows:
"draw": 0,
"recordsTotal": 278,
"recordsFiltered": 278,
"data": [
when if i run this query on Mysql i get only 207 rows
For the 3 relationships that are on the query The 1st one is a hasMany relation The 2nd one and the 3rd is belongsTo relationship
Level 1
@Sinnbeck Something like this but only with eloquent using the relationships
SELECT * FROM invoice_track_tbl a
left JOIN client_payment_tbl b ON a.cid = b.id
WHERE b.pay_method = 0 OR b.pay_method = 2
AND a.dte_processed is NOT NULL AND a.dte_collected IS NULL AND a.voided IS NULL
ORDER BY a.id DESC;
Level 102
@Victor Alfonso then you need to use a join instead of with. Or you can use whereHas() https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence
Please or to participate in this conversation.