This is the weirdest thing, maybe someone can help me decipher this.
3 tables for this example but it's not JUST these 3, it's happening in multiple places.
Warehouses, PendingReceipts, ItemUnits
CREATE TABLE `warehouses` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '1',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `pending_receipts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_type_id` int(10) unsigned NOT NULL,
`complete` tinyint(1) NOT NULL,
`po` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`line` int(10) unsigned NOT NULL,
`qty_ordered` int(10) unsigned NOT NULL,
`qty_received` int(10) unsigned NOT NULL,
`item_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`item_description` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`ship_to` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`project_number` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`note_title` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
`note` text COLLATE utf8_unicode_ci,
`process_text` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pending_receipts_order_type_id_foreign` (`order_type_id`),
CONSTRAINT `pending_receipts_order_type_id_foreign` FOREIGN KEY (`order_type_id`) REFERENCES `order_types` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=331 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `item_units` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pending_receipt_id` int(10) unsigned NOT NULL,
`rfid` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`batch` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`item_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`item_description` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`unit_type` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
`qty` int(10) unsigned NOT NULL,
`warehouse` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`status` int(10) unsigned NOT NULL,
`rack_location` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
`project_number` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`po` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`line` int(10) unsigned NOT NULL,
`order_type` int(10) unsigned NOT NULL,
`callout_id` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `rfid` (`rfid`),
KEY `item_units_pending_receipt_id_foreign` (`pending_receipt_id`),
KEY `item_units_batch_index` (`batch`),
KEY `warehouse` (`warehouse`),
CONSTRAINT `item_units_pending_receipt_id_foreign` FOREIGN KEY (`pending_receipt_id`) REFERENCES `pending_receipts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1254 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
The first thing to notice is that is no foreign key or index on pending_receipts.ship_to while there are keys on item_units.warehouse and warehouses.code
The pending_receipts.ship_to is a warehouse code that may or may not match one of the warehouses.code rows, wherein, the warehouses table is a listing of our warehouses, but not all ship_to warehouses are ours, so there is no foreign key to refrence these.
Yet in my PendingReceipt model, I have a belongTo relationship to the warehouses table:
public function warehouse() {
return $this->belongsTo(Warehouse::class, 'ship_to', 'code');
}
Inversely on my Warehouse model I have a hasMany relationship:
public function receipts() {
return $this->hasMany(PendingReceipt::class, 'ship_to', 'code');
}
When I eager load either model, both return perfectly with the properly loaded relationships.
$warehouses = Warehouse::with('receipts')->get(); // loads all pending_receipts associated with the warehouse
$receipts = PendingReceipt::with('warehouse')->get(); // loads the warehouse associated with the receipt
This seemed strange since there is no foreign key or index on the ship_to column. So my though was ok, perhaps you can just use the column names without keys. But alas, even if I add a key to my item_units table ie the warehouse column I cannot get these relationships to function.
There should be a relationship between the warehouses.code column and the item_units.warehouse column. In my Warehouse model:
public function items() {
return $this->hasMany(ItemUnit::class, 'warehouse', 'code');
}
// and in the ItemUnit model
public function warehouse() {
return $this->belongsTo(Warehouse::class, 'warehouse', 'code');
}
Eager loading either of these 2 models I get no relationships created at all, despite the fact that it works perfectly on the other two.
Both:
$items = ItemUnit::with('warehouse')->get(); // warehouse relation is null
$warehouses = Warehouse::with('items')->get(); // items relation is null
What the heck am I missing, why does one of these work perfectly and the other not work at all?
Thanks.