2 Relationship between 3 Table

these are my tables

publishers
| id | name | url |

languages
| id | name |

cantons
| id | name | code |


canton_publisher
| id | canton_id | publisher_id |


canton_language
| id | canton_id | language_id |

i am trying to find all the publisher based on the language.

for example: http://publisher.dev/api/languages/dea result of a list which publisher with that language de=german

i have this query, but not working correctly, i have lot of duplicates and empty arrays

return Language::with(['cantons', 'cantons.publishers'])
                        ->where('name', $lang)
                        ->get()
                        ->pluck('cantons')
                        ->first()
                        ->pluck('publishers');

My relations is:

class Publisher extends Model
{
    public function cantons()
    {
        return $this->belongsToMany(Canton::class);
    }
}

class Language extends Model
{
    public function cantons()
    {
        return $this->belongsToMany(Canton::class);
    }
}

class Canton extends Model
{
    public function publishers() {
        return $this->belongsToMany(Publisher::class);
    }

    public function languages()
    {
        return $this->belongsToMany(Language::class);
    }
}

how to fix it, what i am missing?

Shahrukh4

8 years ago

Level 4

As I see there is no direct relation between your Language and Publisher table so you can write the elaquent in backward direction as follows,

  return Publisher::with([ 'cantons.publisher' ])->get()->toArray();

1 like

Snapey

8 years ago

Level 122

you can use wherehas to restrict a model by a relation


$publishers = Publisher::whereHas('language', function ($query) use($lang) {
    $query->where('name', $lang);
})->with('cantons')->get();

satuk

8 years ago

Best Answer

Level 1

@Shahrukh4 in your solution, i recieve all publisher without a limitation. @Snapey your solution proposal has helped me to find the solution.

the solution

return Publisher::whereHas('cantons', function($query) use ($lang) {
            $query->whereHas('languages', function($query) use ($lang) {
                $query->where('name', $lang);
            });
        })->get();

Please or to participate in this conversation.