CodeIgniter Forums
Need help modeling a very simple scenario with DataMapper ORM - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: Need help modeling a very simple scenario with DataMapper ORM (/showthread.php?tid=51564)

Need help modeling a very simple scenario with DataMapper ORM - El Forum - 05-09-2012

[eluser]Stolz[/eluser]
Hi,

I'm using Codeigniter 2.1.0 and I'm working on a fictitious project in order to learn DataMapper 1.8.21. I'm trying to deal with one relationship but I still don't really understand DataMapper enough and how should I create the model.


The fake projet is a simple orders system with orders, products and extras for products. These are the columns and relationships between them:

EXTRAS:
- Each extra must have a name and a price.

PRODUCTS:
- Each product must have a name and price.
- A product can can have many extras (not required).

ORDERS:
- Each oder must have a creation datetime.
- An order must have at least one product (they can be repeated).
- The current price of both the product and its extras must be stored in the relationship for allowing price changes in the future without affecting the old orders.

So far the tables look like this: (pseudo-sql for the sake of simplicity)

Code:
TABLE products (
id smallint(1) unsigned NOT NULL auto_increment PRIMARY KEY,
name varchar(128) NOT NULL UNIQUE,
price float NOT NULL
)

TABLE extras (
id smallint(1) unsigned NOT NULL auto_increment PRIMARY KEY,
name varchar(64) NOT NULL UNIQUE,
price float NOT NULL
)

TABLE extras_products (
id int(3) unsigned NOT NULL auto_increment PRIMARY KEY,
product_id smallint(1) unsigned NULL REFERENCES products(id) ON UPDATE CASCADE ON DELETE CASCADE,
extra_id smallint(1) unsigned NULL REFERENCES extras(id) ON UPDATE CASCADE ON DELETE CASCADE,
UNIQUE(product_id,extra_id)
)

TABLE orders (
id int(4) unsigned NOT NULL auto_increment PRIMARY KEY,
created datetime NOT NULL,
)

TABLE orders_products (
id int(3) unsigned NOT NULL auto_increment PRIMARY KEY,
product_id smallint(1) unsigned NULL REFERENCES products(id) ON UPDATE CASCADE ON DELETE RESTRICT,
order_id int(4) unsigned NULL REFERENCES orders(id) ON UPDATE CASCADE ON DELETE CASCADE,
qty tinyint unsigned NOT NULL,
price float NOT NULL
)

TABLE foo (
id int(3) unsigned NOT NULL auto_increment PRIMARY KEY,
orders_products_id int(3) unsigned REFERENCES orders_products(id) ON UPDATE CASCADE ON DELETE CASCADE,
extra_id smallint(1) unsigned REFERENCES extras(id) ON UPDATE CASCADE ON DELETE RESTRICT,
UNIQUE(orders_products_id,extra_id),
price float NOT NULL
)

And the models look like this:


Code:
class Product extends DataMapper {

public $has_many = array('extra','order');

public $validation = array(
  'name' => array(
   'label' => 'Name',
   'rules' => array('trim','required','alpha','max_length'=>128,'unique')
  ),
  'price' => array(
   'label' => 'Price',
   'rules' => array('trim','required','numeric','abs')
  )
);
}

Code:
class Extra extends DataMapper {

public $has_many = array('product');

public $validation = array(
  'name' => array(
   'label' => 'Name',
   'rules' => array('trim','required','alpha','max_length'=>64,'unique')
  ),
  'price' => array(
   'label' => 'Price',
   'rules' => array('trim','required','numeric','abs')
  ),
);
}

Code:
class Order extends DataMapper {

public $has_many = array('product');

public $validation = array(
  //Relationships validation
  //Order must have at least one product
  'product' => array(
   'label' => 'Product',
   'rules' => array('required','min_size' => 1)
  ),
);
}


The problem is I don't know the correct name for the table "foo" and how to define that relationship in the Order model.

Can anyone please guide me to the right direction?

Thanks.

Need help modeling a very simple scenario with DataMapper ORM - El Forum - 05-09-2012

[eluser]WanWizard[/eluser]
First problem is in your relationship tables:

products_extras -> extras_products
products_orders -> orders_products

Table names are combined in alphabetical order, so e for p, and o for p.

As to your 'foo' question, the column names in there confuse me. A column called 'dishes_orders_id' suggests you have a model called 'dishes_orders' somewhere, but it's not in your list.

If it's goal is to register a special price for a specific combination of order and extra, it defined a many to many between order and extra, and it would be logical to call it extras_orders, containing 'extra_id' and 'order_id'.

And maybe a tip:

If you intent to work a lot with join fields (extra columns in relationship tables), it might be worthwhile to look into breaking up your many to many relations, and make a model for the relationship table (which will then have a has_one to both parents).

This will allow you to query them via their model objects, instead of the quite complicated join fields construction.

Need help modeling a very simple scenario with DataMapper ORM - El Forum - 05-09-2012

[eluser]Stolz[/eluser]
[quote author="WanWizard" date="1336593714"]First problem is in your relationship tables. Table names are combined in alphabetical order,...[/quote]

Thanks for pointing out that but acctually I was translating my code on the fly from Spanish to English and I forgot to rearrange the correct table names. In my orriginal code the alphabetical order is correct.

[quote author="WanWizard" date="1336593714"]As to your 'foo' question, ... a column called 'dishes_orders_id' suggests you have a model called 'dishes_orders' somewhere, but it's not in your list.[/quote]

Sorry again, another stupid typo mistake I made. Where it states 'dishes' instead it actually should state 'products'. So the right column name would be 'orders_products_id' and it should refer to the 'orders_products' table right above. I've just correted both mistakes in the original post.

The goal of the join fields is to store the price that the products had in the moment the relationship was created. In this way, if a 'product' or an 'extra' changes its price it won't affect the 'orders' made previously.

So the question is, what name should have the table 'foo' and how can I make my models aware of the relationship it represents?

[quote author="WanWizard" date="1336593714"]
And maybe a tip:

If you intent to work a lot with join fields, it might be worthwhile to look into breaking up your many to many relations, and make a model for the relationship table. This will allow you to query them via their model objects, instead of the quite complicated join fields construction.[/quote]
Thanks for the tip. I will consider your suggestion as soon as I understand the bascis of ORM. Right now I still didn't need to query anithing, I'm just learning the basics of modeling.

Thank you very much Wan for your help.