Support the ongoing development of Laravel.io →
Database Eloquent

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.

Last updated 3 years ago.
0

Sign in to participate in this thread!

PHPverse

Your banner here too?

Netifriik netifriik Joined 14 Nov 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.