Level 63
You probably have a filter for the project id.
->when($this->filters['project_id'], static fn($query, $project_id) => $query->where('project_id', '=', $project_id))
2 likes
Nov 21, 2023
8
Level 7
Cannot get all records from relationship
Hello. I have following 3 models:
// User model
/**
* Projects relationship
*/
public function projects(): BelongsToMany
{
return $this->belongsToMany(Project::class, 'projects_users', 'user_id');
}
/**
* Tasks related to user projects
*
* @return hasManyThrough
*/
public function projectsTasks(): HasManyThrough
{
return $this->hasManyThrough(Task::class, Project::class, 'id', 'project_id', '', 'id');
}
// Project model
/**
* Users relationship
*/
public function users(): BelongsToMany
{
return $this->belongsToMany(User::class, 'projects_users', 'project_id');
}
/**
* Tasks relationship
*/
public function tasks(): HasMany
{
return $this->hasMany(Task::class, 'project_id');
}
//Task model
/**
* Projects relationship
*/
public function project(): BelongsTo
{
return $this->belongsTo(Project::class, 'project_id');
}
In my Livewire component, I have this code, but it returns tasks only for the first project:
$tasksList = $this->user->projectsTasks()
->search('tasks.name', $this->search)
->when($this->filters['description'], static fn($query, $description) => $query->where('description', 'like', '%' . $description . '%'))
->when($this->filters['start_date'], static fn($query, $start_date) => $query->where('start_date', '>=', Carbon::parse($start_date)))
->when($this->filters['end_date'], static fn($query, $end_date) => $query->where('end_date', '<=', Carbon::parse($end_date)))
->when(
$this->filters['duration_hours_bigger'],
static fn($query, $duration_hours_bigger) => $query->where('duration_hours_bigger', '>', $duration_hours_bigger)
)
->when(
$this->filters['duration_hours_smaller'],
static fn($query, $duration_hours_smaller) => $query->where('duration_hours_smaller', '<', $duration_hours_smaller)
)
->when(
$this->filters['actual_time_bigger'],
static fn($query, $actual_time_bigger) => $query->where('actual_time_bigger', '>', $actual_time_bigger)
)
->when(
$this->filters['actual_time_smaller'],
static fn($query, $actual_time_smaller) => $query->where('actual_time_smaller', '<', $actual_time_smaller)
)
->when(
$this->filters['cost_per_hour_bigger'],
static fn($query, $cost_per_hour_bigger) => $query->where('cost_per_hour_bigger', '>', $cost_per_hour_bigger)
)
->when(
$this->filters['cost_per_hour_smaller'],
static fn($query, $cost_per_hour_smaller) => $query->where('cost_per_hour_smaller', '<', $cost_per_hour_smaller)
)
->when(
$this->filters['estimated_cost_bigger'],
static fn($query, $estimated_cost_bigger) => $query->where('estimated_cost_bigger', '>', $estimated_cost_bigger)
)
->when(
$this->filters['estimated_cost_smaller'],
static fn($query, $estimated_cost_smaller) => $query->where('estimated_cost_smaller', '<', $estimated_cost_smaller)
)
->when(
$this->filters['real_cost_bigger'],
static fn($query, $real_cost_bigger) => $query->where('real_cost_bigger', '>', $real_cost_bigger)
)
->when(
$this->filters['real_cost_smaller'],
static fn($query, $real_cost_smaller) => $query->where('real_cost_smaller', '<', $real_cost_smaller)
)
->when($this->filters['tasked_to'], static fn($query, $tasked_to) => $query->where('tasked_to', '=', $tasked_to))
->when($this->filters['project_id'], static fn($query, $project_id) => $query->where('project_id', '=', $project_id))
->when($this->filters['created_by'], static fn($query, $created_by) => $query->where('created_by', '=', $created_by))
->orderBy($this->sortColumn, $this->sortDirection)->paginate($this->perPage);
the dump of $this->user->projectsTasks()->toRawSql() is:
select * from `tasks`
inner join `projects` on `projects`.`id` = `tasks`.`project_id`
where `projects`.`id` = 1
How can I get ALL tasks for ALL projects, that belong to a user?
Level 7
@vincent15000 So I should remove the filter to see all tasks? But then, how in the view I will be able to filter tasks by project?
Surround the filter with if()?
P.S.: Removing the filter doesn't change anything.
1 like
Level 122
with
$this->user->load('projects.tasks')
2 likes
Level 7
@Snapey Instead of $this->user->projectsTasks()?
1 like
Level 7
@Snapey dd($this->user->load('projects.tasks')->get()); returns colelction of all users and dd($this->user->load('projects.tasks')->toRawSql()); returns select * from users so obviously not working. Not sure if I mentioned, but the relation between a user and a project is in a pivot table.
1 like
Level 7
For anyone interested, I achieved my goal by using a Pivot class:
\App\Models\User
public function projects(): BelongsToMany
{
return $this->belongsToMany(Project::class, 'projects_user', 'user_id');
}
public function projectsTasks(): HasManyThrough
{
return $this->hasManyThrough(
Task::class, // The model to access to
ProjectsUsers::class, // The intermediate table that connects the User with the Project.
'user_id', // The column of the intermediate table that connects to this model by its ID.
'project_id', // The column of the intermediate table that connects the Project by its ID.
'id', // The column that connects this model with the intermediate model table.
'project_id' // The column of the Task table that ties it to the Project.
);
}
\App\Models\Project
public function users(): BelongsToMany
{
return $this->belongsToMany(User::class, 'projects_user', 'project_id');
}
public function tasks(): HasMany
{
return $this->hasMany(Task::class, 'project_id');
}
\App\Models\Task
public function project(): BelongsTo
{
return $this->belongsTo(Project::class, 'project_id');
}
\App\Pivots\ProjectsUsers
public function users()
{
return $this->belongsTo(User::class);
}
public function projects()
{
return $this->belongsTo(Project::class);
}
public function tasks()
{
return $this->hasManyThrough(Task::class, Project::class);
}
Level 122
You are trying to use hasManyThrough which does not work with has-many-through-many
The example
$this->user->load('projects.tasks')
get all the users projects and then gets all the tasks within each project, so after, the user model has two nested datasets of projects and tasks.
1 like
Level 7
@Snapey In the view (blade) file, If I use what's suggested in fact I get all projects and tasks and I can go through them with this code:
@php
$all = $this->user->load('projects.tasks');
$json_data = json_decode($all,true);
@endphp
@foreach ($json_data['projects'] as $project)
@foreach ($project['tasks'] as $task)
// Show data
@endforeach
@endforeach
However, for some reason, in the livewire component $this->user->load('projects.tasks') returns collection fo all users instead of the nested data as in the view. Any clues?
1 like
Please or to participate in this conversation.