Laravel Query Builder join doesn't affect query

Question

I'm trying to build in search functionality in my application (Laravel 5.1), but my join doesn't seem to do anything to the resulting query. What am I doing wrong?

Code:

$query = InvoiceHeader::where('customer_code_id', '=', Auth::user()->customer_code_id);

$query->join('invoice_types', 'invoice_headers.invoice_type_id', '=', 'invoice_types.id')
      ->where('invoice_types.name', '<>', array_search('Faktura', InvoiceHeader::INVOICE_TYPES));

$invoices = $query->paginate(15);

Resulting query:

select count(*) as aggregate from invoice_headers where customer_code_id = 1 and (invoice_types.name <> 380)

Resulting response:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoice_types.name' in 'where clause'

This is the query I was hoping to see:

select count(*) as aggregate 
from invoice_headers 
inner join invoice_types 
   on invoice_headers.invoice_type_id = invoice_types.id
where customer_code_id = 1 
   and (invoice_types.name <> 380)

dakine · Accepted Answer · 2015-08-06 13:02:56Z

1

$query = InvoiceHeader::where('customer_code_id', '=', Auth::user()->customer_code_id);

you need to store the query in a variable.

$query = $query->join('invoice_types', 'invoice_headers.invoice_type_id', '=', 'invoice_types.id')
      ->where('invoice_types.name', '<>', array_search('Faktura', InvoiceHeader::INVOICE_TYPES));

$invoices = $query->paginate(15);

answered Aug 6, 2015 at 13:02

dakine

7095 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jedrzej.kurylo · Accepted Answer · 2015-08-06 10:07:47Z

0

You need to add JOIN with invoice_types table if you want to filter on invoice_types.name.

answered Aug 6, 2015 at 10:07

jedrzej.kurylo

41.1k10 gold badges108 silver badges112 bronze badges

15 Comments

OskarD90 Over a year ago

Sorry, I pasted the wrong code. Please take a look again!

jedrzej.kurylo Over a year ago

Looks like it's losing the join. Try to first do the join and then apply 2 where conditions.

OskarD90 Over a year ago

I tried adding this: ->where('invoice_types.name', '<>', array_search('Faktura', InvoiceHeader::INVOICE_TYPES)); But it just added another "and invoice_types.name <> 380" to the query

jedrzej.kurylo Over a year ago

Well, that's what you asked eloquent to do so it's no suprise. Have you tried first doing the join and then applying those 2 where constraints?

OskarD90 Over a year ago

I tried separating the join and where into two statements before, if that's what you meant. Or did you mean something else?

|

Collectives™ on Stack Overflow

Laravel Query Builder join doesn't affect query

2 Answers 2

Comments

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related