Selecting data from DB with same date for multiple dates

There are a couple things that you're going to need to do to get this all working the way you want.

It's also not entirely clear what you mean when you say 'dynamic' - sometimes I would take that to mean that you want the page to react to user input, after it has been rendered. If that were the case then you'll have to sort out your UI and build some sort of API endpoint to serve your data for AJAX requests. But, I'm not sure that's what you mean. This approach is certainly possible, but you have to solve the other things to start with, so let's start with those.

First, and this is what your question seems to be about, is getting the data in the format you want. This means:

1. running the query
2. having the results 'grouped' by month
3. summing result totals by group

There are 3 places you could do this, so that would be the first thing to know. Do you prefer to do the data-structuring in:

1. the query (done by the database - depends your server)
2. in Laravel - using Collections (this would be my approach - "server-side")
3. in Javascript - (may be the most 'dynamic' - but depends on you comfort level programming in JS and what libraries you intend to use: {jQuery, Lodash, Vue,...})

So maybe some decisions first about that, before we start programming...

Thanks for the reply, so firstly what I meant by dynamic (wrong choice of word by me), is that as new data is added to the database e.g month 3, 4 etc ... , then that data will also be included, basically as time progresses and more data is added then more data would be outputted. Hopefully that clears up what I want but let me know if it doesn't.

As to your question I'd prefer to do it in Larval using Collections.

So far playing around I've managed to get the data grouped by date using this code in the controller.

  $results = $user->results;



    $groupResults = $results->groupBy(function($date) {
        return Carbon::parse($date->created_at)->format('m');
    });   

This returns 2 arrays with the relevant data.

If this is the correct to do this ,then my next task should be some how using these arrays in the Chart and also find a way to get the "label" to do ( foreach array, print M1 ++1). Of course if I'm going about this wrong then of course please correct me.

Thanks again for your help.

**Edit- Actually forgot, I need to get the sum of each array after sorting them by date, so that would be my next task I think.

OK - great. We're on the same page. So, you're right you will want the output sorted by ascending created_at (this will become the x-axis, independent variable). Since your using a Collection, we have nice methods to help out with this:

$results = Result::all()->sortBy('created_at'); // for you, $user->results is my Result::all()

So that will take care of ordering the set.

Next you want to group by months. The trick here is that you have timestamps, so we have to convert them into "month" categories, since that is how you want them, so a function to convert to months, and group the Collection:

$res = $results
    ->groupBy(function ($result, $key) {
        return $result->created_at->format('y-M'); // I have multi-year data, you can remove 'y-' if you like
    });

Here you define the groups that will be the equivalence classes for your set. This data now will look like:

Array
(
    [16-Jan] => Array
        (
            [0] => Array
                (
                    [id] => 23
                    [amount] => 8994
                    [created_at] => 2016-01-26 11:16:40
                    [updated_at] => 2018-01-17 04:48:55
                )

            [1] => Array
                (
                    [id] => 27
                    [amount] => 5958
                    [created_at] => 2016-01-28 17:04:31
                    [updated_at] => 2018-01-17 04:48:55
                )

            [2] => Array
                (
                    [id] => 33
                    [amount] => 3367
                    [created_at] => 2016-01-29 21:29:55
                    [updated_at] => 2018-01-17 04:48:55
                )

        )

    [16-Mar] => Array[...]

And you can see that the data is now organized in 'groups' of months (created_at).

Now, for your step #3, you want to run through this nested set, and the the sums for your y-axis dependent variables). We can accomplish this by map-ping over the set, and getting the sum of each internal set, by the amount:

$res = $results
    ->groupBy(function ($result, $key) {
        return $result->created_at->format('yM');
    })
    ->map(function ($result) {
        return ($result->sum('amount'));  // in your data - you will use `result` instead of amount
    });

and this will give us the output we want. Sums by monthly groups. Something like this:

Array
(
    [16-Jan] => 18319
    [16-Mar] => 10552
    [16-May] => 5839
    [16-Jun] => 6096
    [16-Sep] => 7743
    [16-Oct] => 6757
    [16-Nov] => 37419
    [16-Dec] => 7304
    [17-Feb] => 7533
    [17-Mar] => 4581
    [17-Apr] => 27095
    [17-Jun] => 3688
    [17-Jul] => 45448
    [17-Sep] => 18219
    [17-Oct] => 2582
    [18-Jan] => 9661
)

Now the data is taking the shape you're going to want for your graph. Let me know how you do with this...

Sorry for the late reply,

The is fantastic and works brilliantly, So I've followed along and the data is displayed how it should be. Im curious how I would retrieve the data for the X and Y axis, currently I've just made two variables:

   $resX = $results
    ->groupBy(function ($result, $key) {
    return $result->created_at->format('y-M');
});

and

   $resY = $results
    ->groupBy(function ($result, $key) {
     return $result->created_at->format('yM');
 })
    ->map(function ($result) {
    return ($result->sum('amount'));
 });

Is this the best way of doing this or can I do it a better way as I presume this is not really following the "DRY" principle.

Now I'm stuck with displaying the data with the graph, when using the following the graph fails is display at all.

   $chartOne = Charts::create('area', 'highcharts')
        ->title('Talents Progress')
        ->elementLabel('+points')
    ->labels($resX)
    ->values($resY)
    ->dimensions(1000,1200)
    ->responsive(true);

I think you only need $resY - it's keys are the labels. I don't think you need a second array - $resX is redundant.

I'm not sure of the exact format that highcharts wants, I think it's pretty flexible. It's probably not hard to find working examples out there. But, with just the data array, I'd imaging you can have something like:

    ->labels(array_keys($resY))
    ->values($resY)

Ok great ill read up on it but its already mostly working.

Thank you again, you've be massive help and I really appreciate it.

great - glad I was able to help - let us know if you get stuck

can the solution done here be applicable to codeigniter. I'm new to both frameworks