如何根据该查询的结果（使用Eloquent或原始SQL）执行/返回部分查询？

Question

I am trying to get the content of a page based on the slug of the page (the identifier), and the set locale/language. However, If the page is not available in the database with the selected locale, I want to return the page with a fall back locale, which should always be available (if it is not, return an error).

For my app I am using Laravel's Eloquent ORM.

Data in the 'pages' table (pseudocode):

- entry 1:
    title: About
    slug: about
    locale: en
    content: the content of the about page

- entry 2:
    title: Informatie
    slug: about
    locale: nl
    content: De tekst van de informatie pagina

- entry 3:
    title: Home
    slug: home
    locale: en
    content: The content of the home page

Desired output (with fallback locale = en):

Required return to set $page to:

if the selected locale = nl, slug = about:
     'entry 2'
if the selected locale = en, slug = about:
     'entry 1'
if the selected locale = nl, slug = home:
     (since the nl-page is not available for this slug)
     set $page to 'entry 3'

This is the code I have written:

<?php
... other code ...

//based on selection
$slug = 'something';
$locale = 'the selected locale';

//setting for fallback Locale
$fallbackLocale = 'en';

//first try to get the page with the selected locale        
$page = Page::where([
                    'slug' => $slug,
                    'locale' => $locale
                ])
                ->first();

// if the first query is empty, get the page with the fallback Locale
if (empty($page))
{
    $page = Page::where([
                        'slug' => $slug,
                        'locale' => $fallbackLocale
                    ])
                    ->firstOrFail();
}

As you can see, this code does work altough I am performing two querys. I would like to perform one query, where it checks if the first half of the query return something (the page with the selected locale), if this is empty, then look for the page with the fall back locale (the slug is still the same).

Is there a way to do this with Eloquent? (I don't think so, the methods with 'if' statements in eloquent are meant to be used to check if a parameter in a form is set, not if a query returns something)

And if it is not possible with Eloquent, is it possible with just regular SQL?

Answer 1

If you select both and order by locale = 'en' 'nl' will be selected first because 'nl' = 'en' is 0 and 'en' = 'en' is 1.

$page = Page::where('slug', $slug)
    ->whereIn('locale', [$locale, $fallbackLocale])
    ->orderByRaw("locale = '$fallbackLocale'")
    ->first();

The problem here: If $fallbackLocale comes from user input, it is not injection save. And i didn't find a way to use a placeholder in the orderByRaw() caluse.

But you can use a raw query with placeholders:

$page = Page::hydrateRaw("
        select *
        from pages
        where slug = ?
          and locale in (?, ?)
        order by locale = ?
        limit 1
    ",
    [$slug, $locale, $fallbackLocale, $fallbackLocale]
)->first();

Update:

I've found a way to use a placeholder in orderByRaw():

$page = Page::where('slug', $slug)
    ->whereIn('locale', [$locale, $fallbackLocale])
    ->orderByRaw("locale = ?")
    ->addBinding($fallbackLocale, 'order')
    ->first();