Laravel Eloquent ORM "whereHas" through table

Question

Hey I have a problem with Laravel on my site Phones number lookup. I try to choose places that have city that I choose, through contact table.

My model classes: Places class:

class Places extends Eloquent {
    public function contacts()
    {
        return $this->hasOne('Contacts');
    }
     public function clubs()
     {
        return $this->hasOne('Clubs');
     }    
}

Contacts class:

 class Contacts extends Eloquent {
     public function cities()
     {
         return $this->hasOne('Cities');
     }
   }

Cities class:

 class Cities extends Eloquent {

 }

My query:

$view->clubs = Places::whereHas('contacts',function ($q) use($city_id){
           $q->where('contacts', function ($q) use($city_id){
               $q->where('id', $city_id);
            });
        })->get();

Error msg:

MySQL server version for the right syntax to use near 'where id = ?)) >= 1' at line 1 (SQL: select * from places where (select count(*) from contacts where contacts.places_id = places.id and contacts = (select * where id = 2223)) >= 1)

I know it is missing "from" citites but I do not know how to achieve it.

Answer 1

You have 3 options using relations:

1 most straightforward solution:

Places::whereHas('contacts',function ($q) use ($city_id){
       $q->whereHas('cities', function ($q) use ($city_id){
           $q->where('id', $city_id);
        });
    })->get();

2 the same as above but using this PR: https://github.com/laravel/framework/pull/4954

Places::whereHas('contacts.cities', function ($q) use ($city_id){
        $q->where('id', $city_id);   
    })->get();

3 Using hasManyThrough relation:

// Place model
public function cities()
{
  return $this->hasManyThrough('City', 'Contact');
}

// then
Places::whereHas('cities',function ($q) use ($city_id){
   $q->where('cities.id', $city_id);
})->get();

---

edit

Having your schema it's obvious that none of the suggest or your original setup can work.

This is a many-to-many relation which in Eloquent is belongsToMany:

// Places model
public function cities()
{
  return $this->belongsToMany('Cities', 'contacts', 'places_id', 'cities_id')
    ->withPivot( .. contacts table fields that you need go here.. );
}

// Cities model
public function places()
{
  return $this->belongsToMany('Places', 'contacts', 'cities_id', 'places_id')
    ->withPivot( .. contacts table fields that you need go here.. );
}

Then you can call relations like this:

$city = Cities::first();
$city->places; // collection of Places models

// contacts data for a single city-place pair
$city->places->first()->pivot->open_hours; // or whatever you include in withPivot above

Now, there's another way to setup this, in case you need also Contacts model itself:

// Places model
public function contact()
{
  return $this->hasOne('Contacts', 'places_id');
}

// Contacts model
public function city()
{
  return $this->belongsTo('Cities', 'cities_id');
}

public function place()
{
  return $this->belongsTo('Places', 'places_id');
}

// Cities model
public function contact()
{
  return $this->hasOne('Contacts', 'cities_id');
}

then:

$city = Cities::first();
$city->contact; // Contacts model
$city->contact->place; // Places model

hasManyThrough won't work here at all