Laravel: Select from Database

Question

in my Laravel App I have two tables:

Projects
- id
- user_id 
- name
- etc...

Images
- id
- project_id
- url

How can I achieve it, to show all the Projects each user has and all the connected images (each project can have up to 20 images (stored in FTP) and Link in Field URL - the project_id from table "Projects" will be saved in field images.project_id)?

I learned, that I can show the projects like this:

$projects = DB::table('projects')->where('user_id','=',$user->id)->get();

and I tried with

$images = DB::table('images')->where('project_id','=',$projects->id)->get();

But I get an error message:

Property [id] does not exist on this collection instance.

What I am missing? Thank you for pointing me into the correct direction :-)

Kind Regards,

Stefan

Answer 1

For your question i suggest to use eloquent way like set up your models

class Project extends Model
{
    public function images()
    {
        return $this->hasMany(\App\Models\Image::class, 'project_id');
    }

    public function user()
    {
        return $this->belongsTo(\App\Models\User::class, 'user_id');
    }
}

class Image extends Model
{
    public function project()
    {
        return $this->belongsTo(\App\Models\Project::class, 'project_id');
    }
}

Now to find projects with their images you can query as

$projects = Project::with('images')->get();

Each object in $projects will have collection of their associated images.

To add filter for user you can use whereHas on relations

$projects = Project::with('images')
                    ->whereHas('user', function ($query) use ($user) {
                        $query->where('id', '=', $user->id);
                    })->get();