Laravel Sum with where and alias with Eloquent

user_2324216 0
Viewed 1056

I have this query for an old Codeigniter project:

$this->db->select("SUM(source = 'FBS) AS facebook");
$this->db->select("SUM(source = 'FBS' AND promotion = 0) AS facebook_promotion");
$this->db->select("SUM(source = 'IG') AS instagram");
$this->db->select("SUM(source = 'LP') AS landing_page");

I'd like to have same result using Laravel, if is possible with Eloquent instead of Query Builder or Raw Query.

eloquent laravel codeigniter sum
2 Answers

If you want to use raw Query Builder, it would be like,

DB::table('table_name')->where('source','=','FBS')->sum('source');

For Eloquent Models,

 Model::where('source','=','FBS')->sum('source')

It also works with eloquent relationship.

Besides, for multiple sum results like yours, it would be like,

DB::table('table_name')->get( array(
  DB::raw("SUM(source = 'FBS) AS facebook"),
  DB::raw("SUM(source = 'FBS' AND promotion = 0) AS facebook_promotion"),
));
user_77372700

Solved in this way using DB::raw;

$data = CONTACT::select(
    DB::raw('DATE_FORMAT(created_at, "%m-%d-%Y")),
    DB::raw('SUM(source = "IG") as instagram'),
    DB::raw('SUM(source = "FBS") as facebook')
)
->orderBy('data', 'DESC')
->groupBy(DB::raw("DATE_FORMAT(created_at, '%d-%m-%Y')"))
->get();
return response()->json(['success' => $data], 200);
user_23242160
Related