I am running Laravel 5.8 and hammering my head all day against walls, to find out how to achieve this MySQL query in Laravel -> render function in my livewire component to list all available "ABKs" which include a specific text in the other table "Parts".

What I have:
2 Tables one is ABKs and the other one is PARTs < relation between them is on to many one ABK can have one part, one Part can have many ABKs. Noted the relationship in the Models, works as expected. But if I try right now to search over both tables(to order and paginate in my livewire component) I need to have this query for example in Laravel:

SELECT * FROM `abks` LEFT JOIN parts ON abks.part_id = parts.id WHERE parts.zeichnungsnummer LIKE 'F%'

if I try this in Tinker I get the result:

 $abks = ABK::where('id', 'like','%%')->with('part')->where('parts.zeichnungsnummer','like','F%')->get();

Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'parts.zeichnungsnummer' in 'where clause' (SQL: select * from abks where id like %% and parts.zeichnungsnummer like F%)',

what am I doing wrong here? Why doesn't Eloquent find this column?

just a short example what I want to achieve because my code is more complex to post here ... I think its easier to understand to post it in a little example.

a little further explanation why I need this: I have a data table which outputs all ABKs and have 2 search inputs one for the ABKs columns and one for the PARTs columns -> where the user can search for texts in ABKs and in the other one in the PARTs table. which is filtered in this way in livewire while the user types...

Thanks to all here for your great answers, and helping us a lot to learn and understand.

CodePudding user response：

you missed join clause from your eloquent query:

 $abks = ABK::where('id', 'like',$abksId)
->join('parts','parts.id','=','abks.part_id')
->where('parts.zeichnungsnummer','like','F%')->get();