Laravel Eloquent Model related query not working as intended

Question

I have two tables. codes and translations.

codes table

id | name
---|------
1  | A99

translations table

id | code_id | language_code | copy
---|---------|---------------|------
1  |1        |en             | Heart Disease
2  |1        |fr             | Sning Nat

Code model

class Code
{
  function translation() // for default lanuage
  {
   return $this->hasOne(Translation::class, 'code_id')->where('language_code', 'en);
  }
}

Query

$codes = Codes::with(['translation' => function($query){
 $query->where('copy', 'like', '%hltad la loq%');
}]);

This should return nothing, but it returns all Codes without translation.

In other words I just want this query behind the scene.

select codes.*, translations.copy from codes
inner join translations ON codes.id = translations.code_id
where translations.language_code='en'
and translations.copy like '%hltad la loq%'

Does this answer your question? [Laravel where on relationship object](https://stackoverflow.com/questions/29989908/laravel-where-on-relationship-object) — Daniel Protopopov, Mar 23 '20 at 11:58
I believe it does. You are not using Laravel Eloquent code right (the part with "with" and doing sub-query inside of it), and it is shown there how it should be done. — Daniel Protopopov, Mar 23 '20 at 12:40
The only solution for your question and desired SQL query is using DB::raw/DB::query since Eloquent operates with eloquent model(s) as a result, not an assorted collection of columns from different tables. — Daniel Protopopov, Mar 23 '20 at 14:44

score -1 · Answer 1 · answered Mar 23 '20 at 11:52

-1

You are using with so it is returning first table it is like left join. If you want to get inner join you should do like this:

$codes = Codes::whereHas('translation', function ($join) {
    return $join->where('copy', 'like', '%hltad la loq%');
})->get();

answered Mar 23 '20 at 11:52

train_fox

1,517
1
12
31

Replace get() with toSql() and check sql – train_fox Mar 23 '20 at 13:04

Laravel Eloquent Model related query not working as intended

1 Answers1