localize databases with working searching and ordering?

For an easy solution, your color tables should be:

color_id - color_name_en - color_name_de - color_name_it etc.

in your Controller you can catch your language easily, it is generally the first segment after your base_url. And also you can catch it after you send the form. You can send the language with a hidden input.

From your Controller to the view, send the current language with:

Code:

$data['lang'] = $this->request->uri->getSegment(1);

Later, it is time to display. No need for the translation file. Just catch your $lang variable and on top of your site, in a common file - maybe a top menu one - set your names :

Code:

<?php

$color_name_lang = 'color_name_'.$lang;

?>

And wherever you want to display them:

Code:

<ul>

<?php foreach($colors as $color) { ?>

    <li><?php echo $color->$color_name_lang; ?></li>

<?php } ?>

</ul>

This part is important. Not $color->color_name_lang but $color->$color_name_lang

Hope it helps

P.S. You are going to make your search via color_id, not names. Names are just sth visual that you display.

I tried something like this. All predefined standard colors would get translated, user defined ones in the language what they used during naming.
Then I could use translated_name as search and order column and everything would work like expected. Unfortunatelly, when I add this function, my closure function does not find the translated_name column.

PHP Code:

  /**
    * Get localized color names
    *
    * @return object result with additional localized name column
    */
    public function withLocalizedNames() {
        $this->builder()->select('
            CASE
                WHEN name = "Colors.black"  THEN "' . lang('Colors.black') . '"
                WHEN name = "Colors.blue"   THEN "' . lang('Colors.blue') . '"
                WHEN name = "Colors.green"  THEN "' . lang('Colors.green') . '"
                ELSE name
            END AS translated_name', false);
        return $this;
    } 


PHP Code:

$data['colors'] = $this->model
            ->select()
            ->withLocalizedNames()
            ->when($data['search'] !== '', function ($query) use ($data) {
                return $query
                    ->like('color', $data['search'], 'both')
                    ->orLike('color2', $data['search'], 'both')
                    ->orLike('translated_name', $data['search'], 'both');
            })
            ->orderBy($data['sortColumn'], $data['sortDirection']); 


Edit:

Well, the solution above works pretty good for correct order, since I can order by column translated_name. The problem I am facing now is the logical SQL execution order.
WHERE is executed before SELECT, thus the column translated_name does not exist when WHERE is compared.

Any SQL gurus around that can rewrite this query? Runtime add a column with translated values that I can compare in WHERE clause?

Code:

SELECT *, CASE
    -> WHEN name = "Colors.black" THEN "schwarz"
    -> WHEN name = "Colors.blue" THEN "blau"
    -> WHEN name = "Colors.green" THEN "grün"
    -> ELSE name END AS translated_name FROM `colors` WHERE `translated_name` LIKE '%schwarz%';

Ok, rewrote the query myself using CTE (which is unfortunatelly not supported by QueryBuilder)

Code:

'with translated as (
select id, CASE
WHEN colors.name = "Colors.black" THEN "' . lang('Colors.black') . '"
WHEN colors.name = "Colors.blue" THEN "' . lang('Colors.blue') . '"
WHEN colors.name = "Colors.green" THEN "' . lang('Colors.green') . '"
...
ELSE colors.name
END AS tname from colors)
SELECT *, translated.tname from routes
JOIN colors ON colors.id = routes.color_id
JOIN translated ON translated.id = colors.id
WHERE tname like "%' . $searchterm . '%"'

How does it work? It creates dynamically an additional column with translated color names for the ~10 predefined/hardcoded colors, user defined colors are kept untranslated (assuming, the user uses its native language anyway), tables are joined and the dynamic column is searched for the translated colorname.