Query where there is no relation for specific record through pivot?-CodePudding

Have been trying to solve this problem for hours but just can't seem to figure it out.

Here is the show method where I want to change my query

    public function show(DitoNumber $ditoNumber)
{
    $germanDismantlers = GermanDismantler::paginate(100);

    $relatedDismantlers = $ditoNumber->germanDismantlers;

    return view('admin.dito-numbers.show', compact('ditoNumber', 'germanDismantlers', 'relatedDismantlers'));
}

The problem is that I do not want to get the german dismantlers I am also getting through the relationship. So instead of just getting all the records and paginating them on the first line of the method I would prefer to exclude the ones that are already connect to a dito number.

Where this gets a bit complicated is that it is connected through a pivot table

Here is the table

    public function up()
{
    Schema::create('dito_number_german_dismantler', function (Blueprint $table) {
        $table->foreignId('dito_number_id')->constrained();
        $table->foreignId('german_dismantler_id')->constrained();

        $table->primary(['dito_number_id', 'german_dismantler_id'], 'pk');
    });
}

Here is my relationship for connecting germanDismantlers to ditoNumbers

public function germanDismantlers()
{
    return $this->belongsToMany(GermanDismantler::class);
}

And the oppisite

public function ditoNumbers()
{
    return $this->hasMany(DitoNumber::class);
}

CodePudding user response：

IF I understand it correct - you want to get all GermanDismantler(s) which are not associated with a DitoNumber and then paginate them right?

You can try the following query to get the desired output

public function show(DitoNumber $ditoNumber)
{
    //Get only those GermanDismantler records which are not associated with
    //the $ditoNumber record which is resolved from request parameter
    //Assume: id is PK column on table for DitoNumber
    $germanDismantlers = GermanDismantler::whereDoesntHave(
        'ditoNumbers', 
        fn($query) => $query->where('id', $ditoNumber->id)
    )
    ->paginate(100);

    $relatedDismantlers = $ditoNumber->germanDismantlers;

    return view('admin.dito-numbers.show', compact('ditoNumber', 'germanDismantlers', 'relatedDismantlers'));
}

Update:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dito_numbers.german_dismantler_id' in 'where clause' Is it because dito_numbers does not have any fk? the fk's are both in the pivot table

It's because you have defined the relationship on GermanDismantler as hasMany instead of belongsToMany.

For Many-to-Many relationship both model classes should have belongsToMany relationships.

Change the relationship to

//GermanDismantler.php
public function ditoNumbers()
{
    return $this->belongsToMany(DitoNumber::class);
}

Laravel Docs - Querying Relationship Absence

Laravel Docs - Eloquent Relationships - Many-to-Many