jeudi 29 novembre 2018

How to combine two collections by matching keys and their values

So, I've been bashing my head against this for a while and I cant get it to just do what I need it to.

I want to be able to store a set of valuse in our database as Json, but no matter what I cant get Laravel or format the object quite right first.

I've got three queries, One gets users usernames and their First name, as an object. Another two use that object to get a count of each users sales, and quotes, from our databsae with count().

Now all I need is the objects to be combined.

array:1 [
  "" => array:3 [
    0 => array:7 [
      0 => array:2 [
        "CallCentreID" => "sdickerson"
        "FirstName" => "Sarah"
      ]
      1 => array:2 [
        "CallCentreID" => "wjones"
        "FirstName" => "Wendy"
      ]
      2 => array:2 [
        "CallCentreID" => "aknox"
        "FirstName" => "Alex"
      ]
      3 => array:2 [
        "CallCentreID" => "mking"
        "FirstName" => "Melissa"
      ]
      4 => array:2 [
        "CallCentreID" => "nrowecc"
        "FirstName" => "Neil"
      ]
      5 => array:2 [
        "CallCentreID" => "ejones"
        "FirstName" => "Emma"
      ]
      6 => array:2 [
        "CallCentreID" => "spurnell2"
        "FirstName" => "Simon"
      ]
    ]
    1 => array:5 [
      0 => array:2 [
        "CallCentreID" => "aknox"
        "Sales" => 1169
      ]
      1 => array:2 [
        "CallCentreID" => "ejones"
        "Sales" => 401
      ]
      2 => array:2 [
        "CallCentreID" => "mking"
        "Sales" => 767
      ]
      3 => array:2 [
        "CallCentreID" => "sdickerson"
        "Sales" => 1067
      ]
      4 => array:2 [
        "CallCentreID" => "wjones"
        "Sales" => 716
      ]
    ]
    2 => array:6 [
      0 => array:2 [
        "CallCentreID" => "aknox"
        "Quotes" => 3587
      ]
      1 => array:2 [
        "CallCentreID" => "ejones"
        "Quotes" => 400
      ]
      2 => array:2 [
        "CallCentreID" => "mking"
        "Quotes" => 6975
      ]
      3 => array:2 [
        "CallCentreID" => "nrowecc"
        "Quotes" => 3
      ]
      4 => array:2 [
        "CallCentreID" => "sdickerson"
        "Quotes" => 2686
      ]
      5 => array:2 [
        "CallCentreID" => "wjones"
        "Quotes" => 2734
      ]
    ]
  ]
]

The above is as close as I can get with the code I have which is;

$Users = User::where('Accesslevel', 'laravelcallcentre')->select('EmailAddress as CallCentreID', 'FirstName')->get(); 

        $Sales = Order::groupBy('CallCentreID')
            // ->whereDate('OrderDate', '=', date('2018-06-21'))
            ->whereIn('CallCentreID', $Users->pluck('CallCentreID'))
            ->where('Status', 'BOOKING')
            ->selectRaw('CallCentreID, count( * ) AS Sales')
            ->get();

        $Quotes = Order::groupBy('CallCentreID')
            // ->whereDate('OrderDate', '=', date('2018-06-21'))
            ->whereIn('CallCentreID', $Users->pluck('CallCentreID'))
            ->where('Status', 'QUOTE')
            ->selectRaw('CallCentreID, count( * ) AS Quotes')
            ->get();

        $collection = collect([$Users, $Sales, $Quotes]);

        $CCData = $collection->groupBy('CallCentreID');

Anyone got any better Ideas? I feel like im doing this completley wrong. I had this written out as a foreach loop but that ended up being four queries for every user, whihc stacked up fast.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire