Date Range Better Way?

Did you try this ?

$getDates['startDate'] = $getTasks->min('start_date');
$getDates['endDate'] = $getTasks->max('end_date');

Why not make two DB calls with min and max aggregates methods (http://laravel.com/docs/4.2/queries#aggregates) ?

I think sort on collection is not very efficient. Why not use a raw query ?

Not sure of syntax :

$getTasks = $this->task
            ->where('author_id', '=', $id)
            ->where('milestone_id', '=', $$projectId)
            ->orderBy('start_date', 'desc')
            ->select('start_date' ,'end_date')
            ->selectRaw('DATEDIFF(MIN(start_date), MAX(end_date)) as date_diff')
            ->get();

I do the same thing as @bestmomo, however being a new to laravel, I'd ask the following. Is there a query syntax interpreted middle layer? What I mean is if your code is full of raw SQL (like mine), you are now attached to a specific database type and version. You couldn't just swap it out anymore via class. So how expensive is it to make the two calls and sort in PHP as opposed to server side.

Also, I'm not advanced enough to think about this with laravel yet, however since this topic is open, my old code is full of stored procs on MS SQL Server, or UDF's and Proc's on MySQL. This makes a big enough difference on the apps that we take the time to code them, however, transitioning to laravel, is there even a way to accomplish this at the migration level? Or do we do we pepper our model or repository with RAW SQL code?

Happy new years

-Roni

@roni @bestmomo @sdebacker thanks for the replies. @bestmomo, really like your approach. Single DB request. Let me see if I can get that work. Will keep you guys posted.

@roni Laravel will accept SQL, but the relationship model is just a thing of beauty. It is one of the major reasons I moved to Laravel from RoR. Try do this in any other language, I cannot speak to ASP

$posts = Post::with('user')
->with(array('comments'=>function($query){
$query->where('user_id', '=', 3);
$query->with('user');
}))
->with('tags')
->where('locale_id','=',$locale->id)
->take($this->numOfPostsInAreaPageLoad)
->orderBy('created_at', 'DESC')
->get();

@bestmomo 759 days! which is spot on based on seed and Carbon data. What is weird are the dates in the return

Your query:

 array(3) {
        ["start_date"]=>
        string(19) "2015-12-13 03:24:17"
        ["end_date"]=>
        string(19) "2016-01-19 01:57:03"
        ["date_diff"]=>
        string(4) "-759"
      }
      ["original":protected]=>
      array(3) {
        ["start_date"]=>
        string(19) "2015-12-13 03:24:17"
        ["end_date"]=>
        string(19) "2016-01-19 01:57:03"
        ["date_diff"]=>
        string(4) "-759"
      }

This is the right answer based on my lengthy code. So we have a match on 759 days, but start and end dates are not matching, Tried a couple of changes, but I cannot get it tow ork.

array(4) {
  ["startDate"]=>
  object(Carbon\Carbon)#467 (3) {
    ["date"]=>
    string(26) "2015-01-02 07:00:07.000000"
    ["timezone_type"]=>
    int(3)
    ["timezone"]=>
    string(3) "UTC"
  }
  ["endDate"]=>
  object(Carbon\Carbon)#269 (3) {
    ["date"]=>
    string(26) "2017-01-30 08:29:00.000000"
    ["timezone_type"]=>
    int(3)
    ["timezone"]=>
    string(3) "UTC"
  }
  ["daysDiff"]=>
  int(759)
  ["daysDiffHuman"]=>
  string(13) "2 years after"
}

How many tuples are returned by the original query? What DB is it? group by features can yield different results based on the DB, specfically MySQL, requires different grouping parameters that MS or PG SQL. Again, I'm a CRUD guy with my own MVC trying to move to Laravel. I've seen the issue before in sproc implementation, but it's DB specific.

Put a listener for your SQL to dump it to a log, then and the tuples for the query. Then check them against your results. MySQL really threw me for some loops when migrating aggregate functions and procedures over for the first bit. I'm not sure what DB you are using, but it doesn't look like an MS.

@roni it is mySQL, I have to be honest I'm as far from a DB expert as you can get i.e. a $1 and my DB expertise will get you a can of coke ... lol ... I suspect most will use mySQL as it open source, but I've seen people here posting everything from MS to Mongo. In my case I'm using mySQL. My code works, just always wonder if there is a better way. Right now I've optimized to the following. Net is one DB query with a results to array and then a Carbon:: call, works great:

    public function getAllByMilestone($milestoneId )
    {
        return $this->task
            ->where('milestone_id', '=', $milestoneId)
            ->orderBy('start_date', 'desc')
            ->get();
    }

    public function getDateRanges($tasks)
    {
        if(count($tasks) > 0)
        {
           return $this->dateTimeProvider ->daysDiff( head($tasks)['start_date'], last($tasks)['end_date']);
        };

        return false;

    }

@nolros, a can of coke is better than nothing. Right now my laravel knowledge == your DB background but I can give you some help there. If your data is returning correctly, it's probably illuminate\Database giving you what you think you want. I'm trying to purposely stay away from RAW queries. It's a crutch for me, I can easily keep my projects running on CRUD or very tiny MVC's that I've made over the years, but they are nowhere near as robust as laravel. Plus it makes hiring new staff harder and learning curves longer.

If you are using RAW DB calls my suggestion is take a ton of notes, and learn the in's and out's of that DB. Companies change platforms for weird reasons. I've seen SQL Server to pgSQL and mySQL and back for no reason other than some CTO reads an article in a magazine and decides a switch is required. Anyhow, mysql aggregates and groupings follow their own rules.

If you are using RAW DB stuff just read these sections in the mysql docs and do some tests for yourself. -select aggregate functions -group by's -havings and sorting.

Some times depending on the the parts you have or are missing you might get a single tupple with what appears all wrong data but your having function will turn out correct. Sometimes it's the other way around. But MySQL, was definitely it's own beast, and it throw me for days when I migrated to it.

You best bet is to look at the RAW sql and play with it in sequelPro. In Laravel this is remarkably easy, add an event listner for SQL or just that specific command, and then take it and run it and change your select aggregates group bys and havings as you run through the docs. It will probably only take you 30 minutes or so to convince yourself of the rules.

Hope this help, and happy new year. -Roni

PS. I don't know what illuminate is doing with your group by's which is probably giving you your odd results for your non calculated columns. I'll have to read up on that when I have a chance in the new year.