Search code examples
eloquentmany-to-manypivot-tablewhere-clauselaravel-5.7

Laravel Eloquent Many-To-Many Query Producing Extra null WHERE clause


I'm using Eloquent to produce results from a query comprised of three tables:

photos              (id)
photos_to_photosets (photo_id, photoset_id)
photosets           (id)

My models have their many-to-many relationships defined as:

class Photo extends Model
{
    public function photosets()
    {
        return $this->hasMany(PhotoSet::class, 'photos_to_photo_sets');
    }
}

And

class PhotoSets extends Model
{
    public function photos()
    {
        return $this->belongsToMany(Photo::class, 'photos_to_photo_sets');
    }

}

Now, to fetch results I'm forced to use the following Eloquent code:

$photoData = $this->photoSets->photos()
                             ->orWhere('photo_set_id', '=', $id)
                             ->get();

This produces the following query:

SELECT * FROM `photos` 
INNER JOIN `photos_to_photo_sets` 
     ON `photos`.`id` = `photos_to_photo_sets`.`photo_id` 
WHERE `photos_to_photo_sets`.`photo_set_id` is null 
     OR `photo_set_id` = ?

This query works, but I can't seem to remove WHERE `photos_to_photo_sets`.`photo_set_id` is null from the query.

I've tried to just use ->where('photo_set_id', '=', $id) but the null clause still remains; even worse it produces the following WHERE clause:

... WHERE `photos_to_photo_sets`.`photo_set_id` IS NULL 
        AND `photo_set_id` = ?

Is there any way, utilizing Eloquent, to remove this null WHERE clause segment?

Ideally, I'd like to end up with the following query:

SELECT * FROM `photos` 
INNER JOIN `photos_to_photo_sets` 
     ON `photos`.`id` = `photos_to_photo_sets`.`photo_id` 
WHERE `photo_set_id` = ?

Thank you in advance for any help!

UPDATE

Based off @Jonas Staudenmeir's answer, the null WHERE clause has been removed.

To achieve this, I set the photoSet's model ID prior to running the query. The resulting code was:

$this->photoSets->id = $photoset_id;

$photoData = $this->photoSets->photos()->get();

Which produced:

SELECT * FROM `photos` 
INNER JOIN `photos_to_photo_sets` 
     ON `photos`.`id` = `photos_to_photo_sets`.`photo_id` 
WHERE `photo_set_id` = ?

Solution

  • The injected model doesn't have an id:

    $this->photoSet->id = $id;
    

    Then you don't need the additional constraint:

    $photoData = $this->photoSets->photos;