Laravel relationship where clause returns all records parent

Question

I have three tables, File, FileFolder and Folder. These tables all have their own Laravel Model, with their respective names.

File

FileFolder

Folder

Now, I need to retrieve all files that are in folder 1. Our project already contains an Eloquent relationship within the File model, which looks like this:

    public function folderRelation()
    {
        return $this->belongsTo(FolderFileRelation::class, 'FileId', 'FileId');
    }

Looking at the official Laravel docs and these questions on stackoverflow:

where clause inside a relationships - laravel

Laravel query relationship where clause

Laravel relationship where clause is returning records outside relationship

WHERE clause with relationship to another table in Laravel

It clearly states, that for my use case, I need to use the standard with function, to include my relationship, so I can use a where clause on my FileFolder model.

My code now looks like this:

        $files = File::with(['folderRelation' => function($query) {
                $query->where('FolderId', 1); 
        }])->get();

Expected outcome The where-clause on my FileFolder relationship is used, and only the contents from the File table, where the File is in folder 1 get queried from the database.

Returned in $files

Actual outcome The where-clause on my FileFolder relationship is not used, and all the contents within the File table get queried from the database.

Returned in $files

Did I implement my query correctly, or is it better to use a raw-query in this use case?

Possible solutions

Raw query

Using a raw-query, which would work exactly as I'd want it too. The biggest downside to this method is not being able to use the collections as needed.

Querying folder, then retrieving files

I could query like this:

   $files = FileFolder::where('FolderId', $folder)->get()->files();

But, one of my requirements is querying only "image" files, looping over all the files, and dropping keys that are not images, would create an unnecessary extra loop, which would eat away server resources when deployed to production.

(sidenote, sorry for adding the tables as images. I made them with the MD-editor, but couldn't seem to post them due to stackoverflow incorrectly identifying them as code, treating them as inline-code as stated here does not seem to work either)

Answer 1

See here for more detail on the difference between whereHas and with Laravel: Difference between whereHas and with contains constraint

The right code would be the following

$files = File::with(['folderRelation'])->whereHas('folderRelation', function ($query) {
    $query->where('FolderId', 0); 
})->get();