如果另一个值不存在，如何根据一个值从表中选择项目?(雄辩/sql)

How to select items from a table based on one value if another value does not exists? (eloquent/sql)(如果另一个值不存在，如何根据一个值从表中选择项目?(雄辩/sql))

问题描述

我有一个表格，用于为我的网站存储不同的文本块.我目前正在为每个页面选择正确的块，其中包含页面的 slug 和首选语言.当首选语言不可用时，我想选择后备语言中的相同文本块(具有相同标题).

I have a table where I store different blocks of texts for my website. I am currently selecting the correct blocks for each page with the slug of the page, and the prefered language. I would like to select the same block of text (with the same title) in the fallback language when the prefered language is not available.

块表

columns:
| id | slug | title | language | content |

entries:
| 1 | home | first | en | the first block |
| 2 | home | first | nl | het eerste blok |
| 3 | home | second | en | the second block |
    --> block "second" not available for the 'nl' language

所需的输出

fallback language = en

selected prefered language = en, output:
| 1 | home | first | en | the first block |
| 3 | home | second | en | the second block |

selected prefered language = nl, output:
| 2 | home | first | nl | het eerste blok |
| 3 | home | second | en | the second block |
     --> select this one, because the 'nl' version is not available

目前我只选择具有所选首选语言的块，因为如果首选语言"块不可用，我不知道如何通过选择后备语言"块来解决此问题.我可以尝试对两种语言运行两个查询，然后以某种方式合并它们，并且仅在此块的标题计数低于 1 时才插入后备语言"块，但这似乎很复杂而且不是很优雅?

Currently I am only selecting the blocks with the selected prefered language, because I don't know how to approach this with selecting the 'fallback language' block if the 'prefered language' block is not available. I could try to run two queries for both languages, and then merge them somehow and only inserting the 'fallback language' blocks if the title count of this block is lower than 1, but this seems quite elaborate and not very elegant?

对于我的应用，我使用的是 eloquent:

For my app I am using eloquent:

$blocks = Block::->where('slug', '=', 'home')
                ->whereIn('language', $selectedLanguage)
                ->get();

我怎么能雄辩地做到这一点?(或者在原始 SQL 中，就此而言?)

推荐答案

如果我理解正确，如果已经有一行具有相同 slug 和 title.

If i understand you correctly, you want to "remove" the rows with the fallback language if there is already a row with the prefered language for the same slug and title.

您可以使用 LEFT JOIN 作为后备语言来检查是否存在具有首选语言的条目.例如，如果您的首选语言是nl"而后备语言是en"，则您的查询可能如下所示:

You can use a LEFT JOIN for the fallback language to check if an entry with the prefered language exists. For example if your preferd language is 'nl' and the fallback language is 'en' your query could look like:

select blocks.*
from blocks
left join blocks b1
    on  b1.slug  = blocks.slug
    and b1.title = blocks.title
    and b1.language = 'nl'
    and blocks.language <> 'nl'
where blocks.slug = 'home'
  and blocks.language in ('nl', 'en')
  and b1.id is null

sqlfiddle

单词的连接可能类似​​于:为相同的 slug 和 title 寻找更好的翻译.如果语言是首选语言，则不会匹配，因为 blocks.language blocks.language <>'nl'.否则，联接将搜索"首选翻译('b1.language = 'nl').

$prefered = 'nl';
$fallback = 'en';

$blocks = AppBlock::where('blocks.slug', '=', 'home')
    ->whereIn('blocks.language', [$prefered, $fallback])
    ->leftJoin('blocks as b1', function($join) {
        $join->on('b1.slug', '=', 'blocks.slug')
             ->on('b1.title', '=', 'blocks.title')
             ->on('b1.language', '=', DB::raw('?'))
             ->on('blocks.language', '<>', DB::raw('?'))
        ;
    })
    ->whereNull('b1.id')
    ->addBinding([$prefered, $prefered], 'join')
    ->select(DB::raw('blocks.*'))
    ->get()
;