WhereBetween Relationship query

Hi. I've got a "projects" table and a "project_details" table and i am trying to get all the projects between a date range. The dates are stored in the "project_details" table.

I've tried a few differant ways to grab the information and can get it if I grab 1 record, but not all the records.

// Relationship 
public function details(): \Illuminate\Database\Eloquent\Relations\hasOne
{
    return $this->hasOne(ProjectDetail::class, 'project_id', 'id');
}

// In livewire component
$this->projects = Project::with('details')->get();
$this->projects->details()->whereBetween('start', [$this->start_date, $this->end_date]);

What is the correct way to get all rows from a relationship. Or have I got the relationship completely wrong! Many thanks

Snapey

2 years ago

Best Answer

Level 122

You would need a whereHas, or a join

Using whereHas, something like;

use Illuminate\Database\Eloquent\Builder;

$this->projects = Project::query()
		->whereHas('details', function(Builder $q){
			$q->whereBetween('start', [$this->start_date, $this->end_date]);
        })
		->with('details')
		->get();

using a join would be more efficient but a little more complex

$this->projects = Project::query()
		->join('project_details', 'project_details.project_id','=', 'projects.id')
		->whereBetween('project_details.start', [$this->start_date, $this->end_date])
		->with('details')
		->get();

1 like

skoobi

2 years ago

Level 13

@Snapey Brilliant, thank you @snapey . The first one worked perfectly.

Please or to participate in this conversation.