In Eloquent the only documented way I've found to query through a belongsTo relationship by using the relationship definition is by using whereHas, which is implemented as a subquery and thus cannot be properly optimized by the mysql engine in order to use the join buffer and causes temporary tables to be created which makes this very inefficient for large data sets.
The efficient way to query this kinds of relationships in mysql is through joins, but I have found no way to generate actual joins by using the relation definitions in eloquent.
Is there a way to generate a query like this without manually joining the tables? (which defeats the purpose of defining the relationship in eloquent....):
SELECT b.* FROM model_b_items b
INNER JOIN model_a_items a ON b.a_id = a.id
WHERE a.somefield = 'somevalue'
Just to clarify, an eloquent belongsTo relatinship for this example would be like:
class ModelBItem extends Model{
....
function a(){
return $this->belongsTo(ModelAItem::class, 'a_id');
}
...
}
The only documented way with whereHas looks like this:
ModelBItems::whereHas('a', function($q){
$q->where('somefield', 'somevalue');
});
But this generates a subquery instead of a join, like this:
SELECT b.* FROM model_b_items b WHERE (
SELECT COUNT(*) FROM model_a_items a
WHERE b.a_id = a.id AND a.somefield = 'somevalue'
) > 0
By defeating the purpose of defining the relationship, this can be solved as follows, but what is the point of defining the relationship if the joins cannot be inferred by eloquent?
ModelBItems::select('model_b_items.*')
->join('model_a_items', 'model_b_items.a_id', '=', 'model_b_items.id')
->where('model_a_items.somefield', 'somevalue');
What I'm looking for is to have eloquent internally infer the table name "model_b_items" and the condition 'model_b_items.a_id', '=', 'model_a_items.id'. Like this (which doesn't work):
ModelBItems::where('a.somefield','somevalue');
Otherwise, why have the relationship definition at all if I'm going to be repeating the join condition and the table names on every non trivial query?