Relationship :: query where date before today

@MoFish that error is in your view? render.blade.php

@MoFish can you find the code in the view that throws the error? Maybe it can help explain the problem

@Sinnbeck It's a little confusing to trace down as the views are dynamically generated.

Could it be because this is being done in a component?

or Could it be due to release_at being null for all records at the moment?

I'm trying to identify if it is finding any results.

Oddly the original version below without the filter works. Strange huh.

public function render()
{
    $data = CollectionModel::with('items')->where('name', $this->name)->first();
    if($data)
    {
        file_put_contents(resource_path("/views/".$this->file.".blade.php"), trim($data->source));
        return view($this->file)->with('data', $data);
    }
}

@MoFish it could be due to how you use the items. But you can use dd to compare it

dd($data->items);

@Sinnbeck can i do the filter in the model itself?

class Collection extends Model
{
    public function itemsWithFilter()
    {
        return $this->hasMany(CollectionItem::class)->where('release_at', '<', Carbon::now())->whereNotNull('release_at')->orderBy('position');
    }
}

@MoFish yeah, but it shouldn't do any do any difference on the end result

@Sinnbeck

OK so i've identified that the following is returning no results:

#relations: array:1 [▼
    "itemsWithFilter" => Illuminate\Database\Eloquent\Collection {#1336 ▼
        #items: []
    }
]

    public function itemsWithFilter()
    {
        return $this->hasMany(CollectionItem::class)->where('release_at', '<', Carbon::now())->whereNotNull('release_at');
    }

Despite me having several rows with the release_at set to be null.

I'm expecting a result returned if they are null which most records will be .. and expecting it to be excluded if release_at is before today..

@MoFish ah so either null or before today?

$data = CollectionModel::with('items', function($q){
    $q->where('release_at', '<', Carbon::now())->orWhereNull('release_at');
})->where('name', $this->name)->first();  

@Sinnbeck Exactly! thank you... that's it now

@MoFish awesome. If it is solved, please mark a best answer to set the thread as solved

@Sinnbeck

I've introduced a expire_at field. eeekkkkk :)

The below lines work as expected when run separately; but when joined together do not appear to behave as expected.

// return $this->hasMany(CollectionItem::class)->where('release_at', '<=', Carbon::now())->orWhereNull('release_at');
// return $this->hasMany(CollectionItem::class)->where('expire_at', '>=', Carbon::now())->orWhereNull('expire_at');

return $this->hasMany(CollectionItem::class)->where('release_at', '<=', Carbon::now())->orWhereNull('release_at')->where('expire_at', '>=', Carbon::now())->orWhereNull('expire_at');

The idea of this is as follows:

• if release_at is null it will display

• If i set only a release_at date before or equal to today it will display

• If i set only a release_at date after today it will not display

• If expiry_at is null it will display

• If i set only a expiry_at date after today it will display

• If i set only a expiry_at date before or equal to today it will not display

• If i set both a release_at and expiry_at i expect the same rules to apply as above with both fields.

@MoFish when you have an "or" you need to use logical grouping https://laravel.com/docs/9.x/queries#logical-grouping

@Sinnbeck Thank you, i have gave this a go but does not appear correct still despite my efforts.

public function items()
{
    return $this->hasMany(CollectionItem::class)
    ->where(function ($query) {
        $query->where('release_at', '<=', Carbon::now())->orWhereNull('release_at')
        ->orWhere('expire_at', '>=', Carbon::now())->orWhereNull('expire_at');
    });
}

release_at on one record is set to be 2022-04-16.

I would not be expecting this result but it is coming back

The above gives me this SQL

select * from `collections_items` where (`release_at` <= '2022-04-15' or `release_at` is null or `expire_at` >= '2022-04-15' or `expire_at` is null) and `collections_items`.`collection_id` in (5)

@MoFish sounds like you want to check them seperately

public function items()
{
    return $this->hasMany(CollectionItem::class)
    ->where(function ($query) {
        $query->where('release_at', '<=', Carbon::now())->orWhereNull('release_at');
    })->where(function ($query) {
        
        $query->where('expire_at', '>=', Carbon::now())->orWhereNull('expire_at');
    }); 
}

@Sinnbeck You are a star. Thank you very much for all your help.

I learned some new stuff along the way too.

Much appreciated.

@MoFish happy to help and great to hear