Chartjs-Laravel query builder for Charts.js

by

0👍

✅

It should actually be straight forward, but you need to be aware that using DB::raw() is necessary. To begin with, here your query a little bit nicer formatted:

SELECT
  [LeadSource],
  CAST([leadcreated] as date) as createddate,
  COUNT([LeadID]) as numleads
FROM [dbo].[viewAllLeads]
WHERE [companyid] = '001'
GROUP BY [LeadSource], CAST([leadcreated] as date)
ORDER BY [createddate]

And converted to an Eloquent query:

DB::table('viewAllLeads') // the query builder doesn't care if it's a table or view
    ->select([
        'LeadSource',
        DB::raw('CAST(leadcreated as date) as createddate'),
        DB::raw('COUNT(LeadID) as numleads')
    ])
    ->where('companyid', '001')
    ->groupBy('LeadSource', DB::raw('CAST(leadcreated as date)'))
    ->orderBy('createddate')
    ->get();

This will return a Illuminate\Support\Collection containing stdClass objects of the following form:

object(stdClass)#1 (3) {
  ["LeadSource"] => string(3) "ABC"
  ["createddate"] => string(10) "2019-08-05"
  ["numleads"] => int(15)
}

0👍

$leads = Lead::where('companyid', $companyid)->get()->groupBy(function ($item) {
    return $item->created_at->format('Y-m-d');
})->sortBy(function ($item) {
    return $item->created_at->format('x');
});

if you use dd($leads) you can see the data structure.

Leave a comment