Laravel Eloquent ORM "whereHas" across the table

Question

Hey, I have a problem with Laravel. I try to choose places in which there is a city that I choose, through the contact table.

My model classes: Seat class:

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

Contact Class:

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

City class:

  class Cities extends Eloquent { } 

My request:

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

Msg error:

MySQL server version for the correct syntax to use next to 'where id =?))> = 1' on 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 that he is absent "from" citites , but I do not know how to achieve it.

Answer 1

You have 3 options using relationships:

1 most simple solution:

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

2 is 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 the 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(); 

---

change

Having your own scheme, it is obvious that none of the proposed or initial settings can work.

This is a many-to-many relationship, which in Eloquent 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 the following relationships:

 $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 is another way to configure this if you need the 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 will not work at all

Answer 2

As you know the city identifier, and from this you want to find the corresponding place that you can start in the city and return to the place. To do this, you will need to determine the feedback.

 // Add this function to your Cities Model public function contact() { return $this->belongsTo('Contact'); } // Add this function to your Contacts Model public function place() { return $this->belongsTo('Places'); } 

Now you can request a city and find a place.

 $place = Cities::find($city_id)->contact->place; 

EDIT: Added missing function return

Answer 3

 SELECT * from pemeriksaan_ginekologi_iva where id in ( SELECT m1.id FROM pemeriksaan_ginekologi_iva m1 LEFT JOIN pemeriksaan_ginekologi_iva m2 ON (m1.id_klien = m2.id_klien AND m1.id < m2.id) WHERE m2.id IS NULL ) and id_klien in (select id from klien where id_kelurahan =1) AND (hasil_periksa='IVA Negatif / Normal') traslate to laravel