Difficulty with a relationship model one to many

lhagemann · June 2013

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.

admin · June 2013

Is this in .93 or 1.0 beta?

admin · June 2013

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);
       
    }

lhagemann · June 2013

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?

admin · June 2013

The on_after_save addresses both insert and update.

Howdy, Stranger!

Categories

Difficulty with a relationship model one to many

Comments