CODEWITHSUNDEEP
mysqllaravel
halloei
halloei

Reputation: 2036

How to use a subquery in a join clause in Laravel's query builder?

I'd like to build following query in Eloquent:

SELECT products.*
FROM products
LEFT JOIN product_translations ON product_translations.id = (
    SELECT id
    FROM product_translations
    WHERE product_id = products.id
      AND title IS NOT NULL
      AND language IN (?, ?)
    ORDER BY FIELD(language, ?, ?)
    LIMIT 1
)
WHERE is_published = ?
ORDER BY product_translations.title ASC;

This is my attempt:

$languages = ['de', 'en'];
$placeholders = collect($languages)->map(fn() => '?')->join(', ');

$subquery = ProductTranslation::query()
    ->select('id')
    ->whereRaw('product_id = products.id')
    ->whereNotNull('title')
    ->whereIn('language', $languages)
    ->orderByRaw("FIELD(language, $placeholders)", $languages)
    ->limit(1);

$query = Product::query()
    ->addSelect('products.*')
    ->leftJoin('product_translations', 'product_translations.id', '=', DB::raw("({$subquery->toSql()})"))
    ->mergeBindings($subquery->toBase())
    ->where('is_published', false)
    ->orderBy('product_translations.title');

$query->get();

But in that way the bindings order will be incorrect. I expected this: 'de', 'en', 'de', 'en', 1. But the actual produced query is as follows:

SELECT products.*
FROM products
LEFT JOIN product_translations ON product_translations.id = (
    SELECT id
    FROM product_translations
    WHERE product_id = products.id
      AND title IS NOT NULL
      AND language IN ('de', 'en')
    ORDER BY FIELD(language, 1, 'de')
    LIMIT 1
)
WHERE is_published = 'en'
ORDER BY product_translations.title ASC;

How do I accomplish that? I've seen no way to use a subquery in a join clause.

Update:
Here is the migration with test records:

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->boolean('is_published');
});
Schema::create('product_translations', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('product_id');
    $table->string('language', 2);
    $table->string('title');
});

DB::table('products')->insert(['is_published' => 1]);
DB::table('product_translations')->insert([
    ['product_id' => 1, 'language' => 'en', 'title' => 'Test EN'],
    ['product_id' => 1, 'language' => 'de', 'title' => 'Test DE'],
    ['product_id' => 1, 'language' => 'es', 'title' => 'Test ES'],
]);

Upvotes: 0

Views: 3501

Answers (2)

Rav
Rav

Reputation: 1470

Just for the record, this code worked for me:

->join(
    DB::raw($subqueryString), 
    function ($join) { 
        $join->on('table1.id', '=', 'table2.id'); 
    }
)

Example:

$results = Translation::selectRaw('t1.*')
            ->from('translations AS t1')
            ->join(DB::raw("
                (SELECT MAX(id) AS id, release_id
                FROM translations
                WHERE release_id = 3
                "), function ($join) {
                $join->on('t1.id', '=', 't2.id');
            })
            ->orderBy('t1.release_id', 'DESC')
            ->get();

Upvotes: 1

user3532758
user3532758

Reputation: 2271

Instead of mergeBindings you would need addBinding:

$query = Product::query()
    ->addSelect('products.*')
    ->leftJoin('product_translations', 'product_translations.id', '=', DB::raw("({$subquery->toSql()})"))
    ->addBinding($subquery->getBindings(), 'join') //use add bindings.
    ->where('is_published', false)
    ->orderBy('product_translations.title');

mergeBingings constructs the bindings on the entire query by type. As in, order, where, having... and so on. addBinding is needed to merge bindings on individual join levels.

I've tested this with the data you provided and here is the binding array when addBinding is used.

"bindings" => array:5 [▼
      0 => "de"
      1 => "en"
      2 => "de"
      3 => "en"
      4 => false
    ]

Upvotes: 1

Related Questions