PHP Laravel Multiple returns

Question

hello i have this function

//Get Total Number of Personal Leads By User ID & Requested week Through Each Day
//Get Total Number of Personal Leads By User ID & Requested week Through Each Day
$personalleads = \DB::table('leads') 
->where('owned_by_id', $id) // User ID
->where('lead_source_id', 7) // 7 = Personal Lead
->whereBetween('created_at', [$weeks[$week]]) // get week through GET request & Query the data
->select(\DB::raw('DATE(created_at) as date'), \DB::raw('count(*) as pleads'))
->groupBy('date')
->get(); // Get All Data

//Get Total Number of leads Created by Managers By User ID & Requested week Through Each Day
$managerleads = \DB::table('leads') 
->where('owned_by_id', $id) // User ID
->where('lead_source_id', 3) // 3 = Manager Lead
->whereBetween('created_at', [$weeks[$week]]) // get week through GET request & Query the data
->select(\DB::raw('DATE(created_at) as date'), \DB::raw('count(*) as mleads'))
->groupBy('date')
->get(); // Get All Data

//Get Total Number of leads Created by Admins By User ID & Requested week Through Each Day
$adminleads = \DB::table('leads') 
->where('owned_by_id', $id) // User ID
->where('lead_source_id', 4) // 4 = Admin Lead
->whereBetween('created_at', [$weeks[$week]]) // get week through GET request & Query the data
->select(\DB::raw('DATE(created_at) as date'), \DB::raw('count(*) as aleads'))
->groupBy('date')
->get(); // Get All Data

i want to return the data of all of them like

return $adminleads+personalleads+managerleads;

i know this is invalid but i want to display all data at once this is the output i get when i return $personalleads only

[{"date":"2019-02-10","pleads":1},{"date":"2019-02-12","pleads":1},{"date":"2019-02-14","pleads":1}]

how can i make it something like

[{"date":"2019-02-10","pleads":1,"aleads":1,"mleads":1},{"date":"2019-02-12","pleads":1,"aleads":1,"mleads":1},{"date":"2019-02-14","pleads":1,"aleads":1,"mleads":1}]

Thank you very much

Answer 1

Two options you can try to join these values:

1. PHP will loop the results and join the data.
2. The queries handle the joining of data (which I cannot wrap my head around at this moment).

Not sure which one would be better. PHP looks much easier however, depenending on the system and size, my guess is that mysql can handle this much more efficient.

PHP

$pLeads = ...
$mLeads = ...
$aLeads = ...

$allLeads = $pLeads->merge($mLeads)->merge($aLeads); // Maybe there is a shorter variant.
$leads = [];

$types = ['pleads', 'mleads', 'aleads'];

$allLeads->each(function ($lead) {
    // Make sure there is an array present with the date property.
    data_fill($leads, $lead->date, [
        'date' => $lead->date,
    ]);

    // At this point our array looks like:
    // ['2019-11-06' => ['date' => '2019-11-06']]

    // Get the the type of lead.
    $type = $types[array_search(array_keys($lead))];

    // Set the lead amount for the type.
    // Not sure if "$lead->date.$type" works. Make sure you end up with a key like 2019-11-06.pleads
    data_set($leads, "$lead->date.$type", $lead->$type);

    // At this point our array looks like:
    // ['2019-11-06' => ['date' => '2019-11-06', 'pleads' => 1]]
});

// Remove the keys from the array.
$leads = array_flatten($leads);

// At this point our array looks like:
// [['date' => '2019-11-06', 'pleads' => 1, 'mleads' => 2, 'aleads' => 3]]

Query

SELECT
    IFNULL(p.date, m.date)
    pleads,
    mleads
FROM (
    (
        SELECT
            DATE(created_at) as date,
            COUNT(*) pleads
        FROM
            leads
        WHERE
            ...
        GROUP BY
            date
    ) p
    RIGHT JOIN
    (
        SELECT
            DATE(created_at) as date,
            COUNT(*) mleads
        FROM
            leads
        WHERE
            ...
        GROUP BY
            date
    ) m ON p.date = m.date
);

Both solutions have not been tested fully.