Chciałbym zbudować następujące zapytanie w wymokorze:

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;

To jest moja próba:

$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();

Ale w ten sposób zamówienie wiązania będą nieprawidłowe. Spodziewałem się tego: 'de', 'en', 'de', 'en', 1. Ale rzeczywiste wyprodukowane zapytanie jest następujące:

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;

Jak to osiągnąć? Nie widziałem sposobu na używanie podzewności w klauzuli dołączenia.

Aktualizacja :
Oto migracja z płytami testowymi:

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'],
]);
0
halloei 24 lipiec 2020, 17:10

2 odpowiedzi

Najlepsza odpowiedź

Zamiast mergeBindings potrzebowałbyś 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 Konstruuje powiązania na całym zapytaniu według typu. Jak w porządku, gdzie, mieć ... i tak dalej. addBinding jest potrzebna do łączenia wiązań na indywidualnych poziomach łączenia.


Przetestowałem to z podanymi danymi, a tutaj jest tablica wiązania, gdy jest używany addBinding.

"bindings" => array:5 [▼
      0 => "de"
      1 => "en"
      2 => "de"
      3 => "en"
      4 => false
    ]
1
user3532758 25 lipiec 2020, 11:35

Tylko dla rekordu, ten kod działał dla mnie:

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

Przykład:

$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();
0
Rav 9 luty 2021, 10:06