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 controller products, I have a data listing view, where I want to filter by "provider_id", I have the following code that I create the application of the condition in SQL, for example:
if($filter_code){
$filter_all[] = ['code_1', '=', $filter_code];
}
if($filter_status){
$filter_all[] = ['status_id', '=', $filter_status];
}
My query:
$products = $this->product::where($filter_all)->orderBy('name')->with('category', 'status', 'providers')->paginate($this->total_page);
I get the provider_id of the view query, but how do I include it in the SQL condition, since it is a value that is in the relationship table.