I am currently working on a project where translations are a core feature. Implementing this with Eloquent has been a great experience so far, but the problem unfolds when a model has multiple relations to one model.
Say you have a model called Post:
<?php
namespace App;
// posts (
// id
// title
// content
// )
class Post extends \Illuminate\Database\Eloquent\Model {
public function title() {
// posts.title -> translations.reference
return $this->hasMany('App\Translation', 'reference', 'title');
}
public function content() {
// posts.content -> translations.reference
return $this->hasMany('App\Translation', 'reference', 'content');
}
}
And a Translation model:
<?php
namespace App;
// translations (
// id
// reference
// language
// value
// )
class Translation extends \Illuminate\Database\Eloquent\Model {
public function language() {
return $this->hasOne('App\Language', 'id', 'language');
}
}
And finally a Language model:
<?php
namespace App;
// languages (
// id
// code
// name
// )
class Language extends \Illuminate\Database\Eloquent\Model { }
Then the following will successfully generate the expected output:
Post::with([
'title.language',
'content.language'
])->get();
[
{
"id":1,
"title":[
{
"id":68,
"reference":"5efd07083a002",
"language":{
"id":1,
"code":"en",
"name":"English"
},
"value":"Title in english"
}
],
"content":[
{
"id":70,
"reference":"5efd0708f1c82",
"language":{
"id":1,
"code":"en",
"name":"English"
},
"value":"Lorem in English"
}
]
}
]
THE PROBLEM
Is that Eloquent sent duplicate (or very similar) queries to the database, making me wonder if there is a better solution:
[
{
"query":"select * from `translations` where `translations`.`reference` in (?)",
"bindings":[
"5efd07083a002"
],
"time":186.14
},
{
"query":"select * from `languages` where `languages`.`id` in (?, ?)",
"bindings":[
1,
2
],
"time":170.69
},
{
"query":"select * from `translations` where `translations`.`reference` in (?)",
"bindings":[
"5efd0708f1c82"
],
"time":171.93
},
{
"query":"select * from `languages` where `languages`.`id` in (?, ?)",
"bindings":[
1,
2
],
"time":142.38
}
]
After doing some research I could not find any answers to this problem except "it's not possible" with no explanation.
What is the best solution for this problem? Is there something that I am missing or is this really impossible?
Thankful for any answers!