php - How to return the list of objects and their count

Solution:

I would approach it from the other side. Select your objects and put them into the date buckets.

Laravel can help you out of the box grouping it per attribute. You'll only need to map it to the desired struct.

$products = DB::table("products")
    ->where('id_advisor',$advisor->id)->where('deleted_at', NULL)
    ->where('created_at',">=", $start)->where('created_at',"<=", $end)
    ->get()
    ->groupBy('created_at');
Copy code

**Original bits of code example ** Below is some (pseudo) code as an example over the reverse approach.

$products = DB::table("products")
    ->where('id_advisor',$advisor->id)->where('deleted_at', NULL)
    ->where('created_at',">=", $start)->where('created_at',"<=", $end)
    ->get();
    
$buckets = $products->map(
  function($product) { 
    return $product->created_at; 
  }
)->unique();

$result = $buckets->map(function ($bucket) use ($products) {
    return [
      'date' => $bucket, 
      'products' => $products->filter(
        function($product) use ($bucket) { 
          return $product->created_at === $bucket;
        }
      )
    ];
});

dd($results);
Copy code

Dump result:

Illuminate\Support\Collection {#1050
  #items: array:3 [
    0 => array:2 [
      "date" => "2020-07-03"
      "products" => Illuminate\Support\Collection {#1048
        #items: array:1 [
          0 => array:2 [
            "date" => "2020-07-03"
            "id" => 1
          ]
        ]
      }
    ]
    1 => array:2 [
      "date" => "2020-07-04"
      "products" => Illuminate\Support\Collection {#1047
        #items: array:2 [
          1 => array:2 [
            "date" => "2020-07-04"
            "id" => 2
          ]
          2 => array:2 [
            "date" => "2020-07-04"
            "id" => 3
          ]
        ]
      }
    ]
    3 => array:2 [
      "date" => "2020-07-05"
      "products" => Illuminate\Support\Collection {#1045
        #items: array:1 [
          3 => array:2 [
            "date" => "2020-07-05"
            "id" => 4
          ]
        ]
      }
    ]
  ]
}
Copy code

Solution:

try this

$data['myresponse'] = DB::table("products")
            ->where('id_advisor',$advisor->id)->where('deleted_at', NULL)
            ->where('created_at',">=", $start)->where('created_at',"<=", $end)
            ->select(DB::raw("DATE(created_at) as date, DB::raw('count(*) as objects'))
            ->groupBy(DB::raw('DATE(created_at)')) // or ->groupBy('date')
            ->get();
Copy code