Laravel eloquent whereHas where first date is after a specific date

I have two related tables:

publications:

| id | name    |
| 1  | Test    |
| 2  | Example | 

publication_dates:

| id | publication_id | date       |
| 1  | 1              | 2020-02-01 |
| 2  | 1              | 2020-02-10 |
| 3  | 1              | 2020-01-25 |
| 4  | 2              | 2020-01-10 |
| 5  | 2              | 2019-12-15 |

Now for example I would like to get all publications where the first date of publication_dates is after 2020-01-01. So I tried the following:

$publications = Publication::whereHas('dates', function($query) {
   $query->whereRaw("MIN(date) > '2020-01-10'");
});

But this returns the following error:

SQLSTATE[HY000]: General error: 1111 Invalid use of group function

which revers to MIN(date). So my question remains how can I get all publications with the first date being after 2020-01-01 or any other specific date. From the example above I would expect to get the publication with id 1 since the first date of 2 is 2019-12-15

2 answers

  • answered 2020-03-25 13:46 Shobi

    I assume the error is because you are using an aggregate function without a proper group by clause, You could use a join to tackle this. but here is a slightly different approach using the subquery join.

    $firstDates = PublicationDate::groupBy('publication_id')
    ->selectRaw('publication_id, MIN(date) as first_date'); // assuming you have a model called PublicationDate for the publication_dates table
    
    Publication::joinSub($firstDates, 'first_dates', function($join){
        $join->on('first_dates.publication_id', '=', 'publications.id')
             ->where('first_date', '>','2020-01-10');
    })->get();
    

  • answered 2020-03-25 16:23 Tpojka

    You should reverse querying logic and then you can use relationship absence.

    use Carbon\Carbon;
    use Illuminate\Database\Eloquent\Builder;
    
    $date = Carbon::create('2020-01-10');
    $publications = Publication::whereDoesntHave('dates', function(Builder $query) use ($date) {
        $query->where('date', '<', $date->toDateString());
    });
    

    You can also go further and make local scope method for query builder in model

    // Publication model
    
    
    /**
     * Scope a query to only include publications that has date newer than $date
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param string|object Carbon $date
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeDatesNewerThan($query, Carbon $date)
    {
        return $query->whereDoesntHave('dates', function(Builder $query) use ($date) {
            $query->where('date', '<', $date->toDateString());// or set '<=' if more appropriate
        });
    }
    

    Then somewhere in code i.e. newer than last 30 days

    // $publications = Publication::datesNewerThan(Carbon::now()->subDays(30))->get();