Eloquent `with()` with filtering based on relation

Question

I have this tables.

And this model relations, this relations works fine.

class Item extends Model
{

    public function translations()
    {
        return $this->hasMany(ItemTranslations::class);
    }
}

class ItemTranslation extends Model
{

    public function language()
    {
        return $this->belongsTo(Language::class);
    }
}

I need to return a list of items with the translations, but only the translations related to a specific language.

I can't have this query working, im getting all translations of each item, not only the one filtered with this query. The language related to the translation is not needed on the result.

$query = Item::query();

$query->with('translations')->when('language',function($query) use ($ISOlanguage) {
    return $query->where('languages.ISO_code', '=', $ISOlanguage);
});

return $query->paginate();

Any idea who i can have this working? Thanks!

julianstark999 · Accepted Answer · 2020-06-10 11:58:55Z

4

So what you want to do is constraining eager loading

Item::with(["translations" => function ($query) use ($ISOlanguage) {
    $query->where('language.ISO_code', $ISOlanguage);
}])->get();

https://laravel.com/docs/5.8/eloquent-relationships#constraining-eager-loads

edited Jun 10, 2020 at 11:58

answered Jun 10, 2020 at 11:55

julianstark999

3,6241 gold badge30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ricardo Albear Over a year ago

When listing all (or some) items, want to have also the translations loaded, but only the translations related to a specific language

julianstark999 Over a year ago

With my example you load all translations where the languages ISO_code equals your $ISOlanguage: Or what did you mean?

Ricardo Albear Over a year ago

Thanks for your response, for some reason that is not working, i update my question with the whole query. Im getting this error: ``` Undefined table: 7 ERROR: missing FROM-clause entry for table "language" LINE 1: ...item_translations"."language_id" in (1, 2) and "language... ^ (SQL: select * from "item_translations" where "item_translations"."language_id" in (1, 2) and "language"."ISO_code" = EN) ```

Ricardo Albear Over a year ago

Even if i use languages which is the real table name, i get the same issue

julianstark999 Over a year ago

Does it work with my example in the question without your query and paginate? If so you can use my example and replace get() with paginate()

|

Ricardo Albear · Accepted Answer · 2020-06-10 13:46:14Z

2

I finally have it working

Item::with(['translations' => function($query) use ($ISOlanguage) {
   $query->whereHas('language', function($query) use ($ISOlanguage) {
       $query->where('ISO_code', '=', $ISOlanguage);
   });
}])->get();

Thanks @julian-s for your help!

answered Jun 10, 2020 at 13:46

Ricardo Albear

5261 gold badge10 silver badges30 bronze badges

Collectives™ on Stack Overflow

Eloquent `with()` with filtering based on relation

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related