Add condition base on parent's parent data in laravel

I have a problem converting query to Laravel Eloquent. This is the query:

SELECT cat.id, reg.title as region_title, reg.region_code, catlang.title, catlang.description, cat.status from categories as cat join regions as reg ON cat.region_id = reg.id join category_language_region as catlang on cat.id = catlang.category_id and reg.language_id = catlang.language_id;

That is working in Postgres and as you can see, I added additional condition for joining category_language_region "reg.language_id = catlang.language_id". I'm stuck on converting it to Laravel Eloquent Builder.

This is the schema for it

• Table Language: id, code
• Table Region: id, code, language_id
• Table Category: id, status, region_id
• Table Category Language Region: id, title, category_id, language_id

This is what I tried

$results = Category::with([ 'region' => function($query){ $query->select('title', 'id', 'region_code', 'language_id'); }, 'categoryLanguages']) ->select(['categories.*']) ->whereIn('region_id', $region_ids);

But it returns all category language region. I need add to condition to select only category_language_region.language_id equal to selected region.language_id

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2xMG37C
via IFTTT