Laravel Sum with where and alias with Eloquent-CodePudding

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.

CodePudding user response：

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"),
));

CodePudding user response：

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);