Criteria Builder and Many-to-Many

Communities ColdBox HMVC
1

What I’m trying to achieve is not unusual but for the life of me I can’t work it out!

I have a many-to-many relationship defined as ORM entities in the form one-to-many > many-to-one - many-to-one < one-to-many for a traditional many Products has many Categories with a Product2Category link table.

What I’m trying to do is use Criteria Builder to find all the Products that belong to, at the moment, a single category.

I don’t have any attempts at getting this to work worth offering as a starter, so I’m be grateful of any guidance or example templates for me to take forward.

2

Hi Richard–

Can you share the relationship definitions that you have in your entities?

Let’s say, though, that in your Products entity you have a relationship to Categories like so:

property name=“Categories” fieldtype=“one-to-many” cfc=“Categories” fkcolumn=“ProductID” linktable=“Product2Category” inversejoincolumn=“CategoryID”;

With CriteriaBuilder, you could return Products that have a particular category by doing some like:

c = newCriteria();
c.createAlias( “Categories”, “ct” ).isEq( “ct.CategoryID”, somecategoryid );
results = c.list();

3

Thank for that Joel.

I’m away from my codebase at the moment but I’ll revisit and upload it.

I’m curious to see you’ve added the linktable attribute even though you’ve defined the fieldtype as one-to-many. Any reason behind that?

One idea I want to try later is something along the lines of the following pseudo code…

category = entityNew(“Category”).get(123);

c = newCriteria(“Product”);
c.createCriteria(“Product2Category”).add(c.restrictions.eq(“Category”, category);

results = c.list();

What do you think?

4

Okay, so here’s my entity definitions (paired down to the relevant) to help with the story…

component entityname=“Product” table=“products” persistent=“true” {

property name=“id” column=“id” fieldtype=“id” generator=“assigned” length=“35” notnull=“true”;

property name=“name” column=“name” ormtype=“string” length=“255” notnull=“true”;

// one Product can belong to many Product2Category

property name=“product2categories”
fieldtype=“one-to-many”
cfc=“Product2Category”
fkcolumn=“productID”
type=“array”
singularname=“product2category”";

public function init() {

variables.id = createUUID();

variables.name = “”;

return this;
}

}

component entityname=“Product2Category” table=“product2categories” persistent=“true” {

property name=“id” column=“id” fieldtype=“id” generator=“assigned” length=“35”;

// many Product2Categories have one Product
property name=“product” fieldtype=“many-to-one” cfc=“Product” fkcolumn=“productID”;

// many Product2Categories have one Category
property name=“category” fieldtype=“many-to-one” cfc=“Category” fkcolumn=“categoryID”;

public function init() {

variables.id = createUUID();

return this;
}
}

component entityname=“Category” table=“categories” persistent=“true” {

property name=“id” column=“id” fieldtype=“id” generator=“assigned” length=“35” notnull=“true”;

property name=“name” column=“name” ormtype=“string” length=“255” notnull=“true”;

// one Category can belong to many Product2Category (uni-directional)
property name=“product2Categories”
fieldtype=“one-to-many”
cfc=“Product2Category”
fkcolumn=“categoryID”
type=“array”
singularname=“product2Category”;

public function init() {
variables.id = createUUID();
variables.name = “”;

return this;

}
}

HTH

5

What has worked for me in apps is to create a relationship from Product to Category via a link table. I assume you don’t really care–from the perspective of your application, what the contents of your link table are…you only care about how products are related to categories, and visa-versa.

So instead of having a “product2categories” property on Product, make a “Categories” property that is joined to Property via the Product2Category link table.

The beautiful thing about this kind of relationship, as well, is especially illustrated when you start adding and deleting the “links”. If you go the link table route, you can do something like:

Product.addCategory( … )

and ORM should take care of the link table insertion for you.

If you want a good example of this, take a look at the “categories” property in BaseContent.cfc in contentbox: https://github.com/Ortus-Solutions/ContentBox/blob/master/modules/contentbox/model/content/BaseContent.cfc

6

Yes, I know using the many-to-many fieldtype has it’s benefits but I’ve been bitten on the bum too many times with changing briefs and then needing to add a property in the join table, so I always start my many-to-many relationships as one-to-many / many-to-one.

7

Hmm, ok, fair enough :slight_smile:

How about drilling down through the Product2Category to get to the Categories?

Something like:

var c = newCriteria();
c.createAlias( “product2categories”, “p2c” ).createAlias( “p2c.category”, “cat” ).isEq( “cat.id”, 2 );