How to efficiently map between an existing array to query Collection result?

@sr57 In this example there is no day, as there does not have to be, it can be either by days or months.

So, for example (with months only), the user wants to retrieve data from last 3 months. The user wants to fetch count of posts of each type, so the query groups by date and by the post_type, which can be: private, public

Let's say the 3 months are: 2022-05, 2022-04, 2022-03

My frontend expects objects in this format:

(object)['date' => 'some_date',  'private' => 'some_value', 'public' => 'some_value'];

The results are:

Illuminate\Support\Collection {
  #items: array:4 [
    0 => {
      +"date": 2022-05
      +"private": 20
    }
    1 => {
      +"date": 2022-05
      +"public": "10"
    }
    2 => {
      +"date": 2022-04
      +"private": 5
    }
    3 =>  {
      +"date": 2022-03
      +"public": 11
    }
    4 =>  {
      +"date": 2022-03
      +"private": 33
    }
  ]

So I fetched the information grouped by date and by post_type, and now I need to create a JSON with the same format that the frontend expects, so you can see month 4 has no public type posts, but I still need to fill the value 0 in the object, so the end result should look like:

[
(object)['date' => '05-2022',  'private' => '20', 'public' => '10'];,
(object)['date' => '04-2022',  'private' => '5', 'public' => '0'];,
(object)['date' => '03-2022',  'private' => '33', 'public' => '11'];
]

So what I did so far was to first create an array filled with 0 values and the wanted dates:

$arr = [];

$arr = [
(object)['date' => '05-2022',  'private' => '0', 'public' => '0'];,
(object)['date' => '04-2022',  'private' => '0', 'public' => '0'];,
(object)['date' => '03-2022',  'private' => '0', 'public' => '0'];
]

But I need to map the results from the query to this array.

I created the array beforehand because I always know the length of it, since I know the wanted dates, unless there's a better way to do it without first doing this step.

@sr57 I do get all the data but for example when there is 0 rows it will not provide json as above with `"value"=>'0' ``and it's important,

So the query does get all the data, just not in the correct format

@sr57 the query is

->select('post_type', 'date', 'status')
->whereIn('post_type', ['private', 'public'])
->groupBy('date', 'post_type')
->get();

@sr57 sorry, there is aggregate, I forgot to add:

DB::raw('count(*) as posts_count')

@cooperino

better with this :-)

@sr57 Haha I wanted to only show a simple version of the query and forgot to add it

Btw I might have confused you, I will try to make it simple this time in the same post, and now I have made a change to maybe make it easier - I added the dates as keys:

I have an array of objects with the dates as keys:

$arr = [
'05-2022' => (object)['date' => '05-2022',  'private' => '0', 'public' => '0'],
'04-2022' => (object)['date' => '04-2022',  'private' => '0', 'public' => '0'],
'03-2022' => (object)['date' => '03-2022',  'private' => '0', 'public' => '0']
]

And I get a collection:

Illuminate\Support\Collection {
  #items: array:4 [
    0 => {
      +"date": 2022-05
      +"private": 20
    }
    1 => {
      +"date": 2022-05
      +"public": "10"
    }
    2 => {
      +"date": 2022-04
      +"private": 5
    }
    3 =>  {
      +"date": 2022-03
      +"public": 11
    }
    4 =>  {
      +"date": 2022-03
      +"private": 33
    }
  ]

I need to fill the collection data in the corresponding date key

@cooperino can you supply a sample of what you would like the array to look like?

if i get what you mean, you want:

array(3) {
  ["2022-05"]=>
  array(3) {
    ["date"]=>
    string(7) "2022-05"
    ["public"]=>
    int(10)
    ["private"]=>
    int(20)
  }
  ["2022-04"]=>
  array(3) {
    ["date"]=>
    string(7) "2022-04"
    ["public"]=>
    int(0)
    ["private"]=>
    int(5)
  }
  ["2022-03"]=>
  array(3) {
    ["date"]=>
    string(7) "2022-03"
    ["public"]=>
    int(11)
    ["private"]=>
    int(33)
  }
}

which you can get doing the following:

$data = [
    0 => [
        "date" => '2022-05',
        "private" => 20,
    ],
    1 => [
        "date" => '2022-05',
        "public" => "10",
    ],
    2 => [
        "date" => '2022-04',
        "private" => 5,
    ],
    3 => [
        "date" => '2022-03',
        "public" => 11,
    ],
    4 => [
        "date" => '2022-03',
        "private" => 33,
    ],
];

$results = [];
foreach ($data as $row){
    $results[$row['date']] = [
        'date' => $row['date'],
        'public' => (int) ($row['public'] ?? $results[$row['date']]['public'] ?? 0),
        'private' => (int) ($row['private'] ?? $results[$row['date']]['private'] ?? 0),
     ];
}

var_dump($results);

@automica Here is an example corresponding to the above Collection fetched:

$arr = [
'05-2022' => ['date' => '05-2022',  'private' => '20', 'public' => '10'];,
'04-2022' => ['date' => '04-2022',  'private' => '5', 'public' => '0'];,
'03-2022' => ['date' => '03-2022',  'private' => '33', 'public' => '11'];
]

Notice the change: I now have the array as key-value with the dates as the keys so that might help

@MichalOravec I made a change because I thought it would be easier to work with (added a key-value), you're right. Can you please remove this post and I will make a new one with simple and constant structure now?

@cooperino see my answer above.

@automica thank you, I ended up using foreach, slightly differently but you gave me this idea