How to implement a combined cross and inner joins in Laravel?

Question

I have defined 3 tables in Laravel as follow:

Schema::create('locales', function (Blueprint $table) {
    $table->string('id', 2);
    $table->string('name', 5000);
    $table->timestamps();
    $table->primary('id');
});

Schema::create('i18n_keys', function (Blueprint $table) {
    $table->string('id', 255);
    $table->timestamps();
    $table->primary('id');
});

Schema::create('i18ns', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('key', 255);
    $table->string('locale', 2);
    $table->string('translation', 5000)->nullable();
    $table->timestamps();            
    $table->foreign('key')->references('id')->on('i18n_keys')->onDelete('cascade')->onUpdate('cascade');
    $table->foreign('locale')->references('id')->on('locales')->onDelete('cascade')->onUpdate('cascade');
    $table->unique(array('key', 'locale'));
});

Now the question is how can I implement the following SELECT statement in Laravel programmatically. I mean without running the SQL statement directly.

SELECT `il`.`key`, `il`.`locale`, `in`.`translation` FROM 
(SELECT `ik`.`id` AS `key`, `lo`.`id` AS `locale` FROM `i18n_keys` as `ik` CROSS JOIN `locales` as `lo`) AS `il` 
left join `i18ns` as `in` 
ON `in`.`key` = `il`.`key` 
and `in`.`locale` = `il`.`locale`;

The aim is to extract the keys that they don't have a translation yet. But I like to do this with query builder or eloquent or something similar rather than pass the query directly. Is there any way?

Have tried using Laravel's query builder's [cross joins and subquery joins](https://laravel.com/docs/6.x/queries#joins)? — D Malan, Jan 23 '20 at 11:32
Of course. But in that tutorials only linear joins explained. This is a nested join and a true answer provided by @tsaikoga — MJBZA, Jan 23 '20 at 12:03

score 2 · Accepted Answer · edited Jan 23 '20 at 11:59

2

You can try this code:

use App\Models\I18nKey;

$ik = I18nKey::crossJoin('locales as lo')
    ->select('i18n_keys.id AS key', 'lo.id AS locale');

$res = \DB::table(\DB::raw("({$ik->toSql()}) AS il"))
    ->mergeBindings($ik->getQuery())
    ->leftjoin('i18ns as in',function($join){
    $join->on('in.key', '=', 'il.key')
        ->whereColumn('in.locale', 'il.locale');
    })->select('il.key','il.locale','in.translation')->get();

edited Jan 23 '20 at 11:59

MJBZA

4,796
8
48
97

answered Jan 23 '20 at 11:34

TsaiKoga

12,914
2
19
28

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'il.locale' in 'field list' (SQL: select `il`.`key`, `il`.`locale`, `in`.`translation` from (select `i18n_keys`.`id` as `key`, `lo`.`id` from `i18n_keys` cross join `locales` as `lo`) AS il left join `i18ns` as `in` on `in`.`key` = `il`.`key` and `in`.`locale` = `il`.`local`) – MJBZA Jan 23 '20 at 11:44
Makes this exception, – MJBZA Jan 23 '20 at 11:44
@MahdiJ.Ansari sorry, I forgot to alias `lo.id`, updated. – TsaiKoga Jan 23 '20 at 11:48
Thanks. There was some more typos that I edited. But it works. – MJBZA Jan 23 '20 at 12:00

How to implement a combined cross and inner joins in Laravel?

1 Answers1