Eloquent - MySql - JOIN_TABLE on JOIN

[squiaios]

Hello everyone,

I want to use JOIN_TABLE function from mysql on a join relation, but I didn't found how to do it with eloquent builder.

Here is a DB-FIDDLE example : db-fiddle

Using DB::raw() works like a charm, but I'm stuck with eloquent builder.

It's not required to have a ON XXXX = YYYY after the JOIN.
join method from Eloquent Builder requires "join_specification" (ON search_condition | USING (join_column_list))

So I tried by using on search condition "ON 1 = 1", but eloquent transforms the input as a column name, and I have this error :

Unknown column '1' in 'on clause'

\App\Models\News::query()->join(
    \Illuminate\Support\Facades\DB::raw('
        JSON_TABLE(
	        summary_news.cliked_ats,
	        "$[*]" COLUMNS(
		        value datetime PATH "$"
	        )
        ) `cliked_ats`
    '),
    1,
    '=',
    1
);

Does anyone have a solution please ?

Thank you very much

[kaozaza2]

try

Models/News.php

class News extends Illuminate\Database\Eloquent\Model
{
    protected $table = 'news';

    public function summary_news()
    {
        return $this->hasOne(SummaryNews::class);
    }
}

Models/SummaryNews::class

class SummaryNews extends Illuminate\Database\Eloquent\Model
{
    protected $table = 'summary_news';

    protected $casts = [
        'cliked_ats' => 'array',
    ];

    public function news()
    {
        return $this->belongsTo(News::class);
    }
}

use Illuminate\Support\Facades\DB;

News::whereHas('summary_news', function ($query) {
    $query->joinWhere(DB::raw('JSON_TABLE(cliked_ats, "$[*]" COLUMNS(value datetime PATH "$")) as `cliked_ats`'), 'cliked_ats.value', '<', '2016-01-01');
});

[squiaios]

using joinWhere is what I need 👍

Thank you very much :)