Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

tlacaelelrl's avatar

Eloquent query relation based on another relation's column value

Hello,

Considering the following four tables

language [id,name]
article [id]
article_translation [id,article_id,language_id,title]
image [id,article_id,language_id,title]

And given the following values

language [1,english],[2,spanish]
article [1]
article_translation [1,1,1,"article title"]
image [1,1,1,"pic 1 description"],[2,1,2,"pic 1 description"],[3,1,1,"pic 2 description"],[4,1,2,"pic 2 description"]

I would like to do something like this

Article::with(['translation','images'])->first();

which should return the following

{
	"id": 1,
	"relations": {
		"translation": {
			"id": 1,
			"article_id": 1,
			"language_id": 1,
			"title": "article title"
		},
		"images": [
			{
				"id": 1,
				"article_id": 1,
				"language_id": 1,
				"title": "pic 1 description"
			},
			{
				"id": 3,
				"article_id": 1,
				"language_id": 1,
				"title": "pic 2 description"
			}
		],
		"language": {
			"id": 1,
			"name": "english"
		}
	}
}

meaning I am getting the translation of english for article with id 1, and all the images fort article with id 1 where the language id is set to english.

In my model I have something like this

    public function translation() {
        return $this->hasOne(ArticleTranslation::class, 'article_id', 'id');
    }

    public function images() {
        return $this
            ->hasMany(Images::class, 'article_id', 'id');
    }

And what I would like to achive is

    public function images() {
        return $this
            ->hasMany(Images::class, 'article_id', 'id')
            ->where('translation.language_id', 'images.language_id');
         /*
          * Where 'translation.language_id' i  a column from the translation relation
          * And 'images.language_id' is a column from the images relation
          */
    }

Even though I know I could just do

Article::with(['translation'=>function($q){
            $q->where('language_id', '1');
        }, 'images'=>function($q){
            $q->where('language_id', '1');
        }])->first();

Is there a way to do it this way

Article::with(['translation'=>function($q){
            $q->where('language_id', '1');
        }, 'images'])->first();

And the language_id will be applied automatically to the images relation?

Even though in this example I know the language_id, what if I wanted to have multiple records pulled, say something like

Article::with(['translation', 'images'])->get();

However that last one would get me all images regardless of the language_id.

And if I do what I wrote before

->where('translation.language_id', 'images.language_id');

I will get an error column does not exists.

Is this posible or not, I am realy not looking for ways around this particular escenario but a solution that would work for other similar escenarios, where maybe the key in images is not language_id but something else.

Is here the only solution a raw query or is there a built in way to do this?

0 likes
2 replies
CorvS's avatar

@tlacaelelrl Unfortunately you would've to go with your suggestion from above

// PHP >= 7.4
Article::with([
    'translation' => fn($q) => $q->where('language_id', $langId),
    'images' => fn($q) => $q->where('language_id', $langId),
])->first();

but that results in three queries to the database, which is not necessarily bad, but you could use joins to break it down to one.

One other thing tho, ain't your "translation" relationship supposed to be hasMany as well?

tlacaelelrl's avatar

there are two, translation and translations, as when someone is pulling an article they only need the one language they are reading. However an article may have many images per translation.

Please or to participate in this conversation.