I needed to turn this SQL Query in to Eloquent and I'm wondering if what I did is safe and correct way of doing it.
SQL Query:
SELECT t2.`group`
, t2.`item`
, GROUP_CONCAT(IF(t1.locale = 'en', t2.`text`, NULL)) AS en
, GROUP_CONCAT(IF(t1.locale = 'et', t2.`text`, NULL)) AS et
FROM `language_entries` t2
LEFT JOIN `languages` t1 ON t1.`id` = t2.`language_id`
GROUP BY t2.`group`,
t2.`item`
Eloquent:
$langA = DB::connection()->getPdo()->quote("en");
$langB = DB::connection()->getPdo()->quote("et");
$sql = DB::table(DB::raw('language_entries t2'))
->select(
't2.group',
't2.item',
DB::raw('GROUP_CONCAT(IF(t1.locale = "'. $langA .'", t2.`text`, NULL)) AS '. $langA),
DB::raw('GROUP_CONCAT(IF(t1.locale = "'. $langB .'", t2.`text`, NULL)) AS '. $langB)
)
->leftJoin(DB::raw('languages t1'), function($join)
{
$join->on('t1.id', '=', 't2.language_id');
})
->groupBy('t2.group', 't2.item')
->get();
var_dump($sql);
I will most likely replace DB::table() with using a protected $table inside my Model.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community