如果另一个值不存在，如何根据一个值从表中选择项目?(雄辩/sql) [英] How to select items from a table based on one value if another value does not exists? (eloquent/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').

The join in words could be somthing like: Look for a better translation for the same slug and title. If the language is the prefered one there won't be a match because of blocks.language <> 'nl'. Otherwise the join will "search" for the prefered translation ('b1.language = 'nl').

在 WHERE 子句中，如果没有找到更好的翻译(b1.id is null)，我们只告诉返回行.

In the WHERE clause we tell only to return rows if no better translation has been found (b1.id is null).

将查询转换为 eloquent 我能做的最好的事情是:

Best i could do to convert the query to eloquent is:

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

注意:我假设 title 对于所有语言的块都是相同的.否则，您将需要另一列(如 block_id)来标识块.

Note: I'm assuming that title is the same for a block in all languages. Otherwise you would need another column (like block_id) to identify a block.

这篇关于如果另一个值不存在，如何根据一个值从表中选择项目?(雄辩/sql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！