How To Select Items From A Table Based On One Value If Another Value Does Not Exists? (eloquent/sql)

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 languag

Solution 1:

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.

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 isnull

sqlfiddle

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').

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

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

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

$blocks = App\Block::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()
;

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.

Solution 2:

You can use GROUP_CONCAT to do that.

1. Group by title, to get all relevant strings in one row.
2. Use GROUP_CONCAT's ORDER BY to put the desired language first.
3. Use SUBSTRING_INDEX to extract only the first string.

Example query:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(content ORDERBY IF(language='nl',1,IF(language='en',2,3))),',',1)
FROM block
GROUPBY title;