It's a bit weird i'm using whereRelation in my queries but not reffering the field i wanted

my table structure like so

Orders table :

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->string('shipment_id');
    $table->integer('qty');
    $table->string('item_code');
    $table->timestamps();
});

Shipments table :

Schema::create('shipments', function (Blueprint $table) {
    $table->id();
    $table->string('shipment_id')->unique(); // this reffering the ID from shipping lines
    $table->string('delivery_plan');
    $table->string('eta');
    $table->string('destination');
    $table->timestamps();
});

The relations

/**
* Get the order associated with its shipments.
*/
public function shipments()
{
    return $this->hasMany(Shipment::class);
}

/**
* Get the shipment associated with its order.
*/
public function order()
{
    return $this->belongsTo(order::class);
}

My query :

$orders = Order::whereRelation('shipments', function($query) {
    $query->whereIn('destination', ['USA', 'CANADA']);
})
->leftJoin('shipments', 'orders.shipment_id', '=', 'shipments.shipment_id')
->addselect(DB::raw('SUM((qty)) as total_qty'))
->groupBy('orders.sales_id')
->groupBy('orders.item_code');

The query result from relation :

where exists (select * from `shipments` where `orders`.`shipment_id` = `shipments`.`id`)

The query result i wanted :

where exists (select * from `shipments` where `orders`.`shipment_id` = `shipments`.`shipment_id`)

Its refer to shipments.id instead of shipments.shipment_id How can i solve this ?

CodePudding user response：

In laravel, default primary key for model is ID, either that you need to add below code in model Eloquent, Primary Keys

protected $primaryKey = 'your_pk';

and if your relationships not use ID as key, you need to add parameter in reladionship function Eloquent, Relationships

public function shipments()
{
    return $this->hasMany(Shipment::class, 'foreign_key', 'local_key');
}

public function order()
{
    return $this->belongsTo(order::class, 'foreign_key', 'owner_key');
}