How to select items from a table based on one valu

Question

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.

The blocks table

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

Required output

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

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?

For my app I am using eloquent:

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

How could I do this in eloquent? (or in raw SQL, for that matter?)

Answer 1

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 ORDER BY IF(language='nl',1,IF(language='en',2,3))),',',1)
FROM block
GROUP BY title;

Answer 2

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 is null

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.