I have a massive dataset so pagination is essential.
Following the excellent videos on datatables by Caleb, I have the following working query/pagination combo:
public function getRowsQueryProperty()
{
$query = PartMaster::query()
->when($this->filters['cat'], fn($query) => $query->where('cat', $this->filters['cat']))
->when($this->filters['eu'], fn($query) => $query->where('eu', $this->filters['eu']))
->when($this->filters['description'], fn($query) => $query->where('description', 'like', '%'.$this->filters['description'].'%'))
->when($this->filters['variance'], fn($query) => $query->where('variance'))
->when($this->filters['repair'], fn($query) => $query->where('should_repair'))
->when($this->filters['performer'], fn($query) => $query->where('top_performer'))
->when($this->filters['amount-min'], fn($query, $amount) => $query->where('model_value', '>=', $amount))
->when($this->filters['amount-max'], fn($query, $amount) => $query->where('model_value', '<=', $amount))
->when($this->filters['search'], fn($query) => $query->where('part_number', 'like', '%'.$this->filters['search'].'%'));
return $this->applySorting($query);
} // end function
public function getRowsProperty()
{
// if($this->filters['overdue']) {
//return $this->rowsQuery->filter(function($i){
// return $i->overdue;
//})->paginate($this->perPage);
//} else {
return $this->cache(function () {
return $this->rowsQuery->paginate($this->perPage);
});
//} // end if
} // end function
In the getRowsProperty I am trying to further filter the results (I think I have to do it here because overdue is an mutator value and not a database column). If I uncomment those lines, I get the Call to undefined method Illuminate\Database\Eloquent\Builder::filter() which makes sense since it isn't a collection.
BUT I don't know how to use that mutator to limit the rows.
Mutator, or Accessor?
In any case, that is an object-level abstraction, so not available to the query (you are still in query-land until you paginate).
You would need to write a query scope that allows the database query to calculate overdue records, and use that scope in the getRowsQueryProperty method.
@tykus I added a boolean to that model called overdue so the filter works.
I need to set that boolean to false when that part is updated so I added this to the model
protected static function boot()
{
parent::boot();
PartMaster::saving(function ($model) {
$model->overdue = false;
});
} // end function
If I read the docs correctly, any record created or updated will no longer be overdue ... I am just confirming this.
I don't know about this... any saving operating on the model will change the overdue property to false; is this really what you want? I assumed your Model's accessor was computing overdue from some other data; in this context is it more dynamic than a stored value, i.e. it is overdue because the current date.
there is a last_edit column that is set to today() whenever any of the key fields of a master is edited. I run a 2am troll via Artisan to check if any masters are overdue based on business rules (each category has different days requirements).
Please or to participate in this conversation.