2

How to convert this raw query to Laravel eloquent way:

select c.name as country from country c, address ad, city ci where
ad.id = 1 and city.id = ad.city_id and c.code = ci.country_code
Cœur
  • 37,241
  • 25
  • 195
  • 267
Wahid Nahiyan
  • 223
  • 1
  • 4
  • 11

2 Answers2

3

First link

Second link

Query Builder

DB::table("country")
->join('city', 'city.country_code', '=', 'country.user_id')
->join('address', 'address.city_id', '=', 'city.id')
->select('country.name as country')
->where('address.id', 1)
->get();

Eloquent

Country::with(['city','address' => function($query){
    return $query->where('id', 1)
}])
->select('country.name as country')
->get();
Community
  • 1
  • 1
Andrii Lutskevych
  • 1,349
  • 13
  • 23
  • Thanks for the help !! ur Query Builder method is running, but Eloquent way shows Call to undefined method Illuminate\Database\Query\Builder::city() – Wahid Nahiyan May 18 '16 at 08:57
  • @WahidNahiyan `city` and `address` are relations in the Country model and both must be declared. – Andrii Lutskevych May 18 '16 at 09:13
  • yes u are right .. in large project sometimes it is hard to manage Models along with other models as many dev are working and some are less experience and some has more .. I think Query Builder approach is pretty straight forward and easy to understand where Eloquent has its own methods and declarations on Model.. What is your Opinion ?? Thanks once again for help !! – Wahid Nahiyan May 18 '16 at 09:31
  • @WahidNahiyan, I like Eloquent approach, because it more flexible for me. I use Query Builder if need get aggregate information for more than one table (example, search). If I work with one table, use Eloquent. – Andrii Lutskevych May 18 '16 at 09:43
  • 1
    yea Me too !! in case of One table Eloquent is nice.. but in case of joining or complex query i found it complex (may be my lack) , i used to write DB::raw expression but now feel that Query Builder can do this in nicer way.. – Wahid Nahiyan May 18 '16 at 13:01
3

I will modify the answer from Andrey Lutscevich eloquent part

Country::select('country.name as country')->has('city')
  ->whereHas('address', function ($query)
  {
    $query->where('id', 1);
  })
  ->get();

Querying Relationship Existence When accessing the records for a model, you may wish to limit your results based on the existence of a relationship use has in that case

WhereHas methods put "where" conditions on your has queries

ARIF MAHMUD RANA
  • 5,026
  • 3
  • 31
  • 58