How To Define Relation With Through

Hi all,

I have three tables,

product


id int PK

code varchar

name varchar

unit varchar

category


id int PK

code varchar

name varchar

product_category


id int PK

productFk FK to product(id)

categoryFk FK to category(id)

My relationship is

ProductCategory.php




'category' => array(self::BELONGS_TO, 'Category', 'categoryFk'),

'product' => array(self::BELONGS_TO, 'Product', 'productFk'),



Product.php




'productCategory' => array(self::HAS_MANY, 'ProductCategory', 'productFk'),



Category.php




'productCategory' => array(self::HAS_MANY, 'ProductCategory', 'categoryFk'),



Are they correct?

In addition,

  1. how can I define the relation with through so that from product I can get its category?

  2. how can I define the relation with through to get the first product sort by product name from the category?

Thanks in advance.

Daniel

Hello. I’m just curious regarding the table structure, why would you use a blind integer key instead of using compound foreign key on an associative table (product_category) which does not have elements of its own ? :) I think it is best to use a many-to-many relations and therefore the relations should be :

In Product model :




...

'categories' => array(self::MANY_MANY, 'Category', 'tbl_product_to_category(product_id, category_id)'),

...

}



And in the Category model :




...

'products' => array(self::MANY_MANY, 'Product', 'tbl_product_to_category( category_id, product_id )'),

...



I agree with previous poster - use many:many if a junction table is used that has no other meta information.

That said, if you do need to go the way you are proposing I prefer to use nested ‘with’ statements in my AR instead of relations with through.

I haven’t really figured out how to nest through relationships. Sometimes you end up with code that has 3-5 layers of relationships and nested ‘with’ statements just work.

Thank you for your replies.

The thing is, how could I get the category quickly from product, if I have many to many relationship.

from product I can only have categories.

The relationship is that one product can only have one category while a category can have one or more products.

Also, how can I get the first product (sorted alphabetically) from category.

Thanks,

Daniel