Difficulty with a relationship model one to many

I have a database schema that has (what is to me) a standard one-to-many relationship between tables:

What I want: I have a Module to populate information about beverages. A Beverage is available in different sizes, at different prices. I'd like the Module to support populating the (one record) beverage title & description, and the related (many records) of size & price.


database schema:

CREATE TABLE `w_brew_goods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `description` varchar(5000) NOT NULL, `cat_id` int(11) NOT NULL, `active` enum('yes','no') NOT NULL DEFAULT 'yes', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; CREATE TABLE `w_brew_size_price` ( `brew_id` int(11) NOT NULL, `size` enum('12','16','20') NOT NULL, `price` decimal (5,2), UNIQUE KEY `brew_size` (`brew_id`,`size`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; CREATE TABLE `w_product_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `relevant` enum('Baked','Brew') NOT NULL DEFAULT 'Baked' COMMENT 'Which area is this category relvant to?', `order` int(2) NOT NULL COMMENT 'Sort Order within relevant category', `active` enum('yes','no') NOT NULL DEFAULT 'yes', PRIMARY KEY (`id`), UNIQUE KEY `sort_order` (`relevant`,`order`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have the following models in application/models/

brew_goods_model.php
class Brew_goods_model extends Base_module_model { public $required = array('title','description'); public $foreign_keys = array('cat_id' => array('app' => 'product_categories_model', 'where' => array('relevant' => 'Brew')) ); function __construct() { parent::__construct('w_brew_goods'); // provide table name } function form_fields($values = array(),$related=array()) { $fields = parent::form_fields($values,$related); $sizes = array('12','16','20'); $fields['size'] = array('type' => 'enum', 'mode' => 'select', 'options' => $sizes, ); $fields['price'] = array(); return $fields; } ...

and
brew_sizes_model.php
class Brew_sizes_model extends Base_module_model { public $foreign_keys = array('brew_id' => array('app' => 'brew_goods_model')); function __construct() { parent::__construct('w_brew_size_price'); // provide table name } ...

I'm having difficulty figuring out the right combination of relationship methods to populate the brew_size_price table from the brew_goods Module.

If in brew_goods_model.php I use the $related array in the form_fields method like so:
$related = array('size' => 'brew_sizes_model', 'price' => 'brew_sizes_model');

I receive an error: 'Unable to locate the file 'size_model.php';

changing the key in $related to 'brew_sizes' removes the error, but then I end up with a multi-select field called 'brew_sizes' which expects to be populated with records from the brew_sizes_price table.

If i ignore the related array, and use the 'on_after_save' method in brew_goods_model, I can only save one field in my related table. (ie. I can save 'brew_id' && 'size' OR I can save 'brew_id' and 'price'; I can not save a single record of 'brew_id', 'size', and 'price')

function on_after_save($values) { $size = $this->normalized_save_data['size']; $price = $this->normalized_save_data['price']; $this->save_related('brew_sizes_model', array('brew_id' => $values['id']), array( 'size'=> array($size), 'price'=> array($price) ) ); }

I'm sure I'm missing something simple, but I've spent a few hours on this, and can't seem to find the right combination of relationship methods.

Comments

  • edited 11:22AM
    Is this in .93 or 1.0 beta?
  • edited 11:22AM
    The save_related is intended more for using a many to many lookup table where you are using multiple id values to associate with one another. Can you perhaps try doing it by loading in the brew_sizes_model and just saving it like so:
    function on_after_save($values) { $save['brew_id' = $values['id']; $save['size'] = $this->normalized_save_data['size']; $save['price'] = $this->normalized_save_data['price']; $CI =& get_instance(); $CI->load->model('brew_sizes_model'); $CI->brew_sizes_model->save($save); }
  • edited 11:22AM
    This is in version 1.0 (sorry, should have included that information)

    I can certainly do a standard write to the DB, I just thought there would be a relationship function I should use.

    As this is in the on_after_save function, and I'm calling ->save on the model, does this address both INSERT and UPDATE? Or should I use on_after_insert and on_after_update?
  • edited 11:22AM
    The on_after_save addresses both insert and update.
Sign In or Register to comment.