Condition for table relationship in the Standard

Question

I have the following tables of my system, products, Provider and product_provider, have the following structures:

Schema::create('products', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('code_1');
        $table->string('code_2')->nullable();
        $table->string('code_3')->nullable();
        $table->string('ean')->nullable();
        $table->string('location');
        $table->text('name', 65535);
        $table->text('aplications', 65535)->nullable();
        $table->string('image')->nullable();
        $table->decimal('value_cost');
        $table->decimal('percentage_addition');
        $table->decimal('value_sale');
        $table->decimal('stock_minimum')->nullable();
        $table->decimal('stock_maximum')->nullable();
        $table->decimal('stock');
        $table->decimal('quantity_sold');
        $table->boolean('notifications_minimum');
        $table->boolean('notifications_zero');
        $table->integer('brand_id')->unsigned()->index('products_brand_id_foreign');
        $table->integer('category_id')->unsigned()->index('products_category_id_foreign');
        $table->integer('status_id')->unsigned()->index('products_status_id_foreign');
        $table->integer('unit_id')->unsigned()->index('products_unit_id_foreign');
        $table->integer('warranty_id')->unsigned()->index('products_warranty_id_foreign');
        $table->timestamps();
    });

Schema::create('providers', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('social_reason');
        $table->string('fantasy_name')->nullable();
        $table->string('contact');
        $table->string('cnpj');
        $table->string('state_registration')->nullable();
        $table->string('phone')->nullable();
        $table->string('cell_phone')->nullable();
        $table->string('email')->nullable();
        $table->string('informations')->nullable();
        $table->string('address')->nullable();
        $table->string('number')->nullable();
        $table->string('neighborhood')->nullable();
        $table->string('city');
        $table->integer('state_id')->unsigned()->index('providers_state_id_foreign');
        $table->string('zip_code')->nullable();
        $table->timestamps();
    });

Schema::create('product_provider', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('product_id')->unsigned()->index('product_provider_product_id_foreign');
        $table->integer('provider_id')->unsigned()->index('product_provider_provider_id_foreign');
        $table->decimal('value')->nullable();
        $table->timestamps();
    });

My model product exists the relation:

public function providers()
{
    return $this->belongsToMany(Provider::class)->withPivot(['value', 'created_at', 'updated_at']);
}

In my products controller, I have a data listing view, where I want to filter by "provider_id", I have the following code that creates the condition application in SQL, example:

if($filter_code){
        $filter_all[] = ['code_1', '=', $filter_code];
    }
    if($filter_status){
        $filter_all[] = ['status_id', '=', $filter_status];
    }

My consultation:

$products = $this->product::where($filter_all)->orderBy('name')->with('category', 'status', 'providers')->paginate($this->total_page);

I receive the provider_id of the view query, but how do I include it in the SQL condition, because it is a value that is in the relationship table.

Answer 1

To work, you need to get to the table product_provider with a join and making relation with the table produto, and then a filter where country provider_id, example:

$this->product::where($filter_all)
    ->join('product_provider', 'product_provider.product_id','=','produto.id')
    ->where('product_provider.provider_id',$value_provider_id) 
    ->orderBy('produto.name')
    ->with('category', 'status', 'providers')
    ->paginate($this->total_page);

the variable $value_provider_id is the one reported in the comments that comes from your view.

_{Reference: Laravel - Queries#joins}