CODEWITHSUNDEEP
laravellaravel-5eloquent
Hamidreza
Hamidreza

Reputation: 724

write orwhere for multi tables in eloquent

I am using eloquent as ORM and I want to use where in multi-table like this:

$raw_query = EntityCity::with(['province']);
        $raw_query = $raw_query->where(function ( $q ) use ( $search_data ) {
            $q->where('city.title' , 'like' , "%$search_data%")
            ->orwhere('province.title' , 'like' , "%$search_data%");
        });
    }
    $this->data[ 'result_list' ] = $raw_query->limit($this->per_page)
                                             ->orderByDesc("time_insert")
                                             ->offset(( $page_num - 1 ) * $this->per_page)
                                             ->get();

However, I encounter the following error:

Message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'province.title' in 'where clause' (SQL: select count(*) as aggregate from city where (city.title like %fars% or province.title like %fars%))

If I comment the orwhere it works.

So how can you write this with orwhere?

Upvotes: 4

Views: 762

Answers (2)

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111899

Instead of:

   $raw_query = $raw_query->where(function ( $q ) use ( $search_data ) {
        $q->where('city.title' , 'like' , "%$search_data%")
        ->orwhere('province.title' , 'like' , "%$search_data%");
    });
}

you should use:

$raw_query = $raw_query->where(function($q) {
     $q->where('city.title', 'like', "%$search_data%")
       ->orWhereHas('province', function ( $q ) use ( $search_data ) {
           $q->where('province.title' , 'like' , "%$search_data%");
      });
});

Notice that the where .. orWhereHas was wrapped here in additional where and this gives you confidence you can add any other conditions as for example selecting only active cities: 

$raw_query = $raw_query->where(function($q) {
     $q->where('city.title', 'like', "%$search_data%")
       ->orWhereHas('province', function ( $q ) use ( $search_data ) {
           $q->where('province.title' , 'like' , "%$search_data%");
      });
})->where('active', 1);

Upvotes: 3

Andriy Lozynskiy
Andriy Lozynskiy

Reputation: 2604

Try to use orWhereHas:

$raw_query = $raw_query->where('city.title', 'like', "%$search_data%")
        ->orWhereHas('province', function ( $q ) use ( $search_data ) {
            $q->where('province.title' , 'like' , "%$search_data%");
        });

Upvotes: 1

Related Questions