The equivalence of the AND statement in a ON statement (JOIN) in EloquentORM

Question

Take a look at the query below:

SELECT v*, s.* 
FROM videos v
INNER JOIN subtitles s ON (s.subtitle_id = v.video_id AND s.language = 'en')
WHERE v.video_id = 1000

I want to find the equivalent data retrieval action for a Laravel / Eloquent ORM environment. So, my options are:

using the DB facade
using the query builder
defining the relationship in the Video model

Let's say I wish to use the latter (if possible).

namespace App\Models\v1;

use App\Models\v1\Subtitles;
use Illuminate\Database\Eloquent\Model;

class Video extends Model
{
    protected $table = 'videos';

    public function subtitle()
    {
        return $this->hasOne(Subtitles::class, 'subtitle_id', 'video_id'); // How can I define the AND s.language = 'en' ?
    }
}

The problem here is that I don't know how to define the AND s.language = 'en' in EloquentORM. Any help is appreciated.

Remul · Accepted Answer · 2019-11-04T09:19:42.087

1

You can add a where clause to the relationship:

public function subtitle()
{
    return $this->hasOne(Subtitles::class, 'subtitle_id', 'video_id')->whereLanguage('en');
}

Retrieving the model:

Provided you changed your primaryKey property on the video model:

protected $primaryKey = 'video_id';

Docs

You can do the following:

$video = Video::findOrFail(1000);
$subtitle = $video->subtitle;

edited Nov 04 '19 at 09:19

answered Nov 04 '19 at 08:47

Remul

7,874
1
13
30

I didn't knew that Eloquent was capable of handling the `where` like that. Can you supply an official documentation page that explains the usage of the `where` in the way you are using it? I couldn't find it online. – Julian Nov 04 '19 at 10:23
1

I don't think it is explained in the docs, but it is occasionally used, for example [here](https://laravel.com/docs/5.0/eloquent#query-scopes). You can find the code that makes it possible [here](https://github.com/laravel/framework/blob/5.6/src/Illuminate/Database/Query/Builder.php#L1502). – Remul Nov 04 '19 at 10:54

rkj · Answer 2 · 2019-11-04T09:15:09.750

You can define the relationship and then use whereHas

public function subtitle()
{
    return $this->hasOne(Subtitles::class, 'subtitle_id', 'video_id');
}

And then filter it like this

$video = Video::where('video_id', 1000)->whereHas('subtitle', function($query){
  $query->where('language', 'en');
})->first();

For details check the doc

If you just want to use join then you can use it like this

$lang = 'en';

$video = Video::where('video_id', 1000)
        ->join('subtitles', function ($join) use ($lang){
             $join->on(function ($query) use ($lang) {
                $query->on('subtitles.subtitle_id', '=', 'videos.video_id');
                $query->on('subtitles.language', '=', DB::raw($lang));
             });
        })->first();

Check laravel join

The equivalence of the AND statement in a ON statement (JOIN) in EloquentORM

2 Answers2