Level 39
Try with standard laravel primary key (id) , if that fits your needs/expectations, you should create unique field 'deliveryNumber' as the requirements you have.
1 like
May 14, 2021
3
Level 8
Typecast from varchar to int while relationship query executed.
I have a delivery table with deliveryNumber as the primary key of varchar 10, due to the special requirement: DeliverySart with "00" and has one to many relationships with Items and Packages.
Sql:
$order= Delivery::with(['deliveryitems', 'itempackages'])->where('DeliveryNumber', $request->id)->first();
Result query:
array:3 [
0 => array:3 [
"query" => "select * from `deliveries` where `DeliveryNumber` = ? limit 1"
"bindings" => array:1 [
0 => "0080000049"
]
"time" => 1.15
]
1 => array:3 [
"query" => "select * from `delivery_items` where `delivery_items`.`delivery_DeliveryNumber` in (80000049)"
"bindings" => []
"time" => 625.05
]
2 => array:3 [
"query" => "select * from `packages` where `packages`.`DeliveryNo` in (80000049)"
"bindings" => []
"time" => 395.28
]
]
Relationship:
/**hasmany to DeliveryItem
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function deliveryitems(){
return $this->hasMany(DeliveryItem::class, 'delivery_DeliveryNumber', 'DeliveryNumber');
}
/**hasmany to packages
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function itempackages(){
return $this->hasMany(Package::class, 'DeliveryNo', 'DeliveryNumber');
}
its cause a very signifcat time due the conversion to INT.
Fields type is varchar in each table, using Laravel 7.3
Level 73
Agreed, either use a regular id as your delivery no or create a column for it where you can use varchar and leading zeros as much as you want.
1 like
Level 8
I found the solution after spending few hours and writing a custom hasmany relation, Laravel provides a field protected $keyType = 'string' default value in integer and based on that when creating SQL Eloquent do the typecasting.
https://laravel.com/docs/8.x/eloquent#primary-keys
"If your model's primary key is not an integer, you should define a protected $keyType property on your model. This property should have a value of string:"
array:3 [
0 => array:3 [
"query" => "select * from `deliveries` where `DeliveryNumber` = ? limit 1"
"bindings" => array:1 [
0 => "0080000049"
]
"time" => 1.96
]
1 => array:3 [
"query" => "select * from `delivery_items` where `delivery_items`.`delivery_DeliveryNumber` in (?)"
"bindings" => array:1 [
0 => "0080000049"
]
"time" => 1.09
]
2 => array:3 [
"query" => "select * from `packages` where `packages`.`DeliveryNo` in (?)"
"bindings" => array:1 [
0 => "0080000049"
]
"time" => 0.92
]
]
Please or to participate in this conversation.