dow56114 2016-09-04 13:33
浏览 68
已采纳

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

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?

  • 写回答

1条回答 默认 最新

  • dongxinche1264 2016-09-04 19:46
    关注

    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();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 电脑鼠标键盘不好使了
  • ¥15 求糕手!用matlab求解目标规划模型🙏
  • ¥15 画出的分类图不对,求解答一下
  • ¥20 springboot和springcloud版本问题
  • ¥15 ps2手柄控制树莓派小车
  • ¥30 C#:vsto powerpoint的外接程序
  • ¥30 stata将do文件代码转化为ado文件
  • ¥15 两个同维数组相比,不同位置、出现重复比无意义,而不同位置、不出现重复比,则有意义。把有意义的两个数组放入新的集合MK中。
  • ¥15 可以远程电脑安装nvm
  • ¥15 写一个可直接调用的函数,将32位有符号数转成另一个无符号的数