This is an archived copy of the forum for reference purposes

many-to-many with ordering

posted by normany 9 years ago

i currently have a DB setup that involves 2 classes defined by 2 tables and a many-to-many joining table.


CREATE TABLE IF NOT EXISTS `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `sections` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `sections_items` (
  `section_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`section_id`,`photo_id`),
  KEY `photo_id` (`photo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

conceptually, i have a lot of *Item* objects that could be associated with multiple *Section* objects... so i would be able to say "Item *a* is /in/ Section *x* and also /in/ Section *y*"

my schema above functions fine. what I would like to do is to order the Items that are in Sections--ideally add a 3rd field 'order' to the *sections_items* table such that i could expect a certain order when i call $section->buildItems(). is there a way i could configure this using the ORM conventions in flourishlib?

If you add an ordering column to sections_items you will no longer be able to directly build a record set of Item objects, but you can use the fRecordSet build method to create another fRecordSet from the ordered linking table.

CREATE TABLE IF NOT EXISTS `sections_items` (
  `section_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `display_order` int(10) NOT NULL,
  UNIQUE (`section_id`, `display_order`),
  PRIMARY KEY (`section_id`,`photo_id`),
  KEY `photo_id` (`photo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

class Section extends fActiveRecord {
    protected function configure() {
        fORMRelated::setOrderBys($this, 'SectionsItem', array('display_order' => 'asc'));
    }
}
class SectionsItem extends fActiveRecord { }
class Item extends fActiveRecord { }

$items = $section->buildSectionsItems()->buildItems();

It would also be pretty trivial to writing your own Section::buildItems() method that just calls $section->buildSectionsItems()->buildItems().

posted by wbond 9 years ago