CODEWITHSUNDEEP
mysqllaraveleloquentleft-join
fuatkaraca
fuatkaraca

Reputation: 47

Laravel multiple search in related tables

I have two tables related for towns and cities. (Town belongsTo City, City hasMany Town)

towns table: id, name, city_id

cities table: id, name

For an autocomplete ajax query, i can get results by sql query like this.

Select t.id AS townid, t.name AS townname, c.name AS cityname FROM towns t 
left join cities c ON t.city_id=c.id
where t.name like '%ana%' OR c.name like '%ana%';

So i get the result witch i want. But in laravel i couldnt achieve that. i can search in towns but without searching also city name.

    $towns = App\Town::with('city')->where('name', 'like', '%ana%')->get();

Upvotes: 2

Views: 1506

Answers (2)

Bostjan
Bostjan

Reputation: 814

Laravel Elequent with() will not do join, so you are unable to search in towns.

To make same query as abouse you have to use proper laravel join.

So something like:

$towns = App\Town::leftJoin('cities', 'towns.city_id', '=', 'cities.id')
->where('towns.name', 'like', '%ana%')->orWhere('cities.name', 'like', '%ana%')->get();

But in this example please check what is selected. You can also specifiy fields to select ( select('towns.id', 'towns.name', ...) ).

Upvotes: 0

KuKeC
KuKeC

Reputation: 4610

Try code below

$towns= DB::table('towns')
     ->join('cities', 'cities.id', '=', 'towns.city_id')
     ->select('towns.id as townid', 'towns.name as towname', 'cities.name as cityname')
     ->where('towname', 'like', '%ana%')
     ->orWhere('cityname', 'like', '%ana%') 
     ->get();

Upvotes: 0

Related Questions