This is an archived copy of the forum for reference purposes

Extra columns in a many-to-many join table

posted by belshire 9 years ago

I have a site I'm building regionalization into and I'm running into a problem. I have two models Products and Categories and the association between those two need to be based on another model Region. So I could have Product A in Category 1 in Region 1 and I also could have Product A in Category 2 in Region 2. The way I've been trying to accomplish this is to have the region_id as another column in the join table and filter there. However whenever I create this extra column it destroys the many-to-many relationship in Flourish and my code no longer works. Is there an effective way to accomplish this in Flourish that I'm unaware of?

There is some functionality in Flourish that can help, but really the joining table is no longer a joining table. Obviously there will be a little extra HTML in your forms since you are dealing with more data.

You can use the build action on an fRecordSet, such as in the following example:

$products->buildProductCategories()->buildCategories();

Depending on the amount of data you have, writing pure SQL may have better performance, but I can tell you that this Flourish construct only executes two SQL statements.

When you are creating the HTML, you will obviously need to create a full child-table set of inputs (such as on fActiveRecord#Populating) in order to associate products to a category and a region. The populate action will remove any ProductCategory records that are not present in the request data.

$product->populateProductCategories();

posted by wbond 9 years ago