Laravel multiple relationship eager loading with select columns on laravel 9

[zerexei]

• Laravel Version: 9.43.0
• PHP Version: 8.1.12
• Database Driver & Version: 5.7.33 - MySQL Community Server (GPL)

Description:

the tasks relationship returns empty collection, I want to select specific fields on tasks table.

DraggableStatus::with(['tasks' => function ($query) use ($project) {
        return $query
            ->with('user:id,avatar')
            ->select(['id', 'name', 'project_id', 'user_id', 'due_date'])
            ->where('project_id', $project->id)
            ->orderBy('due_date');
    }])->get();

removing ->select() returns all collection items but I want to limit the columns of tasks

DraggableStatus::with(['tasks' => function ($query) use ($project) {
        return $query
            ->with('user:id,avatar')
            ->where('project_id', $project->id)
            ->orderBy('due_date');
    }])->get();

Steps To Reproduce:

1. follow code structure

[Ifriqiya]

Try this:

DraggableStatus::with(['tasks' => function ($query) use ($project) {
        return $query
            ->with('user:id,avatar')
            ->where('project_id', $project->id)
            ->orderBy('due_date');
    }])->get(['id', 'name', 'project_id', 'user_id', 'due_date']);

[zerexei]

@Ifriqiya SQLSTATE[42S22]: Column not found: 1054, also tried inside tasks query but not working.

[denizgolbas]

Can you share both of migration files and relational model methods?

[zerexei]

tasks migration

$table->foreignIdFor(\App\Models\User::class);
$table->foreignIdFor(\App\Models\DraggableStatus::class);

user model

public function tasks()
    {
        return $this->hasMany(Task::class);
    }

task model

public function draggable_status()
   {
       return $this->belongsTo(DraggableStatus::class, 'draggable_status_id', 'id');
   }

   public function user()
   {
       return $this->belongsTo(User::class);
   }

draggable status model

public function tasks()
    {
        return $this->hasMany(Task::class, 'draggable_status_id', 'id')->orderBy('order');
    }

[Ifriqiya]

Are you trying to load DraggableStatus with tasks and user relationship? This what I'll do:

DraggableStatus::with(['tasks:id,name,project_id,user_id,due_date', 'user:id,avatar'])
->where('tasks', function ($query) use ($project) {
	$query->where('id', $project->id);
})
->orderBy('due_date')
->get();

[zerexei]

load DraggableStatus with tasks and user relationship

yes, I want to eager load tasks and user relationship w/ specific columns.

unfortunately, the code above doesn't work. SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tasks' in 'where clause',

select * from `draggable_statuses` where `tasks` = (select * where `id` = 1) order by `due_date` asc

[Ifriqiya]

Can you share your tasks and draggable_statuses tables? The error means there's no 'tasks' column in your draggable_statuses table. This portion of the code should probably be

->where('task', function ($query) use ($project) {
	$query->where('id', $project->id);
})

where 'task' not 'tasks', assuming you do have that column.

[denizgolbas]

DraggableStatus::with(['tasks' => function ($query) use ($project) {
        return $query
            ->with('user:id,avatar')
            ->select(['id', 'name', 'project_id', 'user_id', 'due_date', 'draggable_status_id'])
            ->where('project_id', $project->id)
            ->orderBy('due_date');
    }])->get();

When you select some column into with method, you need to put Foreign Key and Primary Key as well.

[Ahmadgoma]

@denizgolbas You made my day!