Level 14
Aug 19, 2018
7
Level 1
Need help for query parent model constraints
I have 3 Models which is Chain, Customer & Chain-Invoice
class Chain extends Model
{
public function invoices() {
return $this->hasMany('\App\Invoice');
}
public function customer()
{
return $this->hasOne('\App\Customer', 'id', 'customer_id');
}
}
class Customer extends Model
{
public function chain()
{
return $this->belongsTo('\App\Customer');
}
}
class Invoice extends Model
{
public function chain()
{
return $this->belongsTo('\App\Chain');
}
}
So now I have this query to display all invoices.
$invoices = Invoice::with('chain.customer');
But I want filter the result with customer ids. Because the Customer in parent Model I couldn't figure out how to query.
$invoices = Invoice::with(['chain.customer => function($q) use ($customer_ids)'
{
$query->whereIn('chain.customer_id', $customer_ids);
}
]);
This results an error
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'chain.customer_id' in 'where clause' (SQL: select * from `customers` where `customers`.`id` in (1, 2) and `chain`.`customer_id` in (2))
Level 24
Please post the three tables' migrations.
Level 1
@staudenmeir here is my migrations
Schema::create('chains', function (Blueprint $table) {
$table->increments('id');
$table->integer('customer_id', FALSE, TRUE);
$table->foreign('customer_id')
->references('id')->on('customers');
});
Schema::create('invoices', function (Blueprint $table) {
$table->increments('id');
$table->integer('chain_id', FALSE, TRUE);
$table->foreign('chain_id')->references('id')->on('chains')->onDelete('cascade');
});
Schema::create('customers', function (Blueprint $table) {
$table->increments('id');
$table->string('name', 100)->unique();
});
Level 24
customer should be a BelongsTo relationship:
public function customer()
{
return $this->belongsTo('\App\Customer');
}
In the closure, you can only access the last relation's table. Choose one of these two queries:
$invoices = Invoice::with(['chain.customer' => function($query) use($customer_ids) {
$query->whereIn('customers.id', $customer_ids);
}]);
$invoices = Invoice::with(['chain' => function($query) use($customer_ids) {
$query->whereIn('chains.customer_id', $customer_ids);
}]);
1 like
Level 1
I used
$invoices = Invoice::with(['chain' => function($query) use($customer_ids) {
$query->whereIn('chains.customer_id', $customer_ids);
}]);
Now there are no any exceptions. But it seems not filtering the results. It returns all invoices.
$invoices = Invoice::with([ 'chain' => function ($query) use ($request) {
if ($request->customer_ids) {
$query->whereIn('chains.customer_id', $request->customer_ids);
}
}])->orderBy('invoices.id', 'DESC');
dd($invoices->get());
Level 24
If you want to filter the results, you have to use whereHas() instead of (or in addition to) with():
$invoices = Invoice::whereHas('chain', function ($query) use ($request) {
if ($request->customer_ids) {
$query->whereIn('chains.customer_id', $request->customer_ids);
}
})->orderBy('invoices.id', 'DESC');
1 like
Level 1
Thanks you very much for your guidance @staudenmeir.
Please or to participate in this conversation.