Hi,
i'm facing an issue in which i would like to specify two restrictions over the same associaiton.
Let me explain the problem: i have a many-to-many association between a "product" entity and a "category" entity.
- A "product" can be such things as a table, a chair, a television, and so on
- A "category" can be "furniture", "electronics", "gardening"... BUT it can also be a brand name (choose whichever you want :))
a product can fall into multiple categories, and a category may contain several products, hence the many-to-many association. Though, most products are "automatically" put into one category: the one related to the brand that released the product.
When i want to make a selection of the products contained in 1, 2, ... n categories, i use the following query and everything works fine:
Code:
private static final String LIST_PRODUCTS =
"SELECT DISTINCT product " +
"FROM POJO_Product product " +
"LEFT OUTER JOIN product.categories category " +
"WHERE category.id IN (:categoriesId)";
But the problem occurs when i want to give the customer the ability to select "the products belonging to a, b and c categories (which are not brand categories) AND released by z (which is a brand category)"
I updated my query the following way:
Code:
private static final String LIST_PRODUCTS =
"SELECT DISTINCT product " +
"FROM POJO_Product product " +
"LEFT OUTER JOIN product.categories category " +
"WHERE category.id IN (:categoriesId) " +
"AND EXISTS ( " +
"SELECT DISTINCT product2 " +
"FROM POJO_Product product2 " +
"LEFT OUTER JOIN product2.categories category2 " +
"WHERE category2 .id = :brandCategoryId " +
"AND product2.id = product.id " +
")";
This query works, but i wonder if there isn't a smarter way to achieve what i want?
Since this project isn't yet in an advanced stage, i'm still able to update the model. I thought about declaring a "brand" entity and linking "brand" and "product" in a one-to-many association, which would make the "AND" clause of the query much easier ("AND product.brand.id = :brandId"), but i would rather avoid that*.
If there is any better way to write this query with hibernate, or if you have any article to suggest that covers such issue, i would be glad to hear it from you!
Thanks a lot
* To make things easier to understand, i used the "category" and "product" analogy, but in the application i'm developping, those entities are other things, and the "brand" is not strictly related to the "product". Even if such situation didn't occur until now, i definitely think a time could come where the "product" has to be part of two different "brands", which would immediately make this one-to-many association obsolete, and make the first design a better choice
Login
FAQ
Search