如何在 Laravel 中实现组合的交叉和内连接？

Question

我在 Laravel 中定义了 3 个表，如下所示：

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'));

});

现在的问题是如何SELECT以编程方式在 Laravel 中实现以下语句。我的意思是不直接运行 SQL 语句。

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`;

目的是提取他们还没有翻译的键。但我喜欢使用查询生成器或 eloquent 或类似的东西来执行此操作，而不是直接传递查询。有什么办法吗？

Answer 1

你可以试试这段代码：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();