CODEWITHSUNDEEP
phplaraveleloquentlaravel-5.6
imqqmi
imqqmi

Reputation: 443

Unknown column in eloquent query builder laravel 5.6

The relationship between gwrcategory and gwrproces is one to many. Between gwrproces and gwrlist is many to one.

I use this code to search in the data. I use % for now to find everything until this works.

    $searchresult = GwrCategory::
    whereHas('gwrproces', function($query) use ($request){
        $query->where('gwr_code', 'LIKE', '%'.$request->input('gwrcode').'%')->
        whereHas('gwrlist', function($query) use ($request)
        {
            $query->where('versie', 'LIKE', '%');
        }
    )->with('gwrlist')->get();
    }
)->with('gwrproces')->get();

The resulting sql of the code above is:

select * from `gwr_proces` 
where `gwr_proces`.`gwr_category_id` = `gwr_categories`.`id` and `gwr_code` LIKE %20% 
and exists 
(select * from `gwr_lists` 
 where `gwr_proces`.`gwrlist_id` = `gwr_lists`.`id` and `versie` LIKE %)

This gives an error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'gwr_categories.id' in 'where clause'.

I've played around with the query in phpmyadmin and this seems to get it working. I only added gwr_categories to the first select * from. 

select * from `gwr_proces`,`gwr_categories` 
where `gwr_proces`.`gwr_category_id` = `gwr_categories`.`id` and `gwr_code` LIKE '%' 
and exists 
(select * from `gwr_lists` 
where `gwr_proces`.`gwrlist_id` = `gwr_lists`.`id` and `versie` LIKE '%')

My question is: how can I convince eloquent to add the correct column or rewrite the code to get it to search in gwrproces and gwrlist tables for a specific text in a column while keeping the relationship with categories in-tact?

Edit: I use eager loading, hence the with()->get()

Upvotes: 0

Views: 1092

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

The problem is that you execute the nested query.
You also can't use eager loading like that.

Remove ->with('gwrlist')->get().

Upvotes: 1

Related Questions