“self-join” – relation between many to many rows from single table

I have table “item” [id, name, …] and table “item_relationships” [id, order, item_id]. Columns id and item_id in “item_relationships” are primary keys and represents id in “item”. Column order in “item_relationships” represents the order of related rows from “item” for a specific id.
Structure of item_relationships and foreign key:

CREATE TABLE IF NOT EXISTS `item_relationships` (
  `id` int(11) unsigned NOT NULL,
  `order` int(11) unsigned NOT NULL,
  `item_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`,`item_id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

ALTER TABLE `item_relationships`
  ADD CONSTRAINT `item_relationships_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

When I want to get related rows for some row in “item”, I write something like this:

foreach ($db->table("item_relationships")->select("item_id")->where(array("id" => $rowID))->order("order ASC") as $item_relationship) {
    echo $item_relationship->item->name;
}

Is it possible to reference (using Nette\Database) from item to item_relationships? (from item_relationships to item it works well)

Is there a better way of implementing such relation?

Last edited by oldrich.valek (2014-03-10 23:20)