Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version:
3.1.1
Mapping documents:
Code:
<class name="Item" table="items">
<id name="id" type="int">
<generator type="identity">
</id>
<property ... various properties />
<set name="tags" lazy="false">
<key column="itemId"/>
<one-to-many class="Tag"/>
</set>
</class>
Code:
<class name="Tag" table="tags">
<id name="id" type="int">
<generator type="identity">
</id>
<property name="itemId" type="int"/>
<property name="grouping" type="string"/>
<proeprty name="value" type="string"/>
</class>
MySQL 4.1
I'm trying to implement a "tag cloud" query. The idea is that I'd like to return the Items that have one or more associated Tags. For example, the tags table might contain the following values:
Code:
id itemId grouping value
------------------------------
1 1 state WA
2 1 color Blue
3 2 state MD
4 2 color Red
5 3 state WA
6 3 color Red
7 4 state WA
8 4 color Blue
Given that table, I'd like to be able to quickly find out that, for example, Item #1 and Item #4 both have a state of WA and a color of Blue.
In MySQL, I can implement a query like:
Code:
select i.* from items i left join tags t1 on i.id = t1.itemId left join tags t2 on i.id = t2.itemId where t1.grouping = 'state' and t1.value = 'WA' and t2.grouping = 'color' and t2.value = 'Blue';
or using a group:
Code:
select count(t.itemId) as count, i.* from items i left join tags t on i.id = t.itemId where (t.grouping = 'state' and t.value = 'WA') or (t.grouping = 'color' and t.value = 'Blue') group by t.itemId having count = 2;
But I would LOVE to be able to figure out how to do this in a extensible way using Criteria since they are so easy to work with programatically.
I was trying something along the lines of
Code:
Criteria c = session.createCriteria[Item.class];
Criteria t1 = c.createCriteria("tags", "t1");
t1.add(Restrictions.eq("grouping", "state");
t1.add(Restrictions.eq("value", "WA");
Criteria t2 = c.createCriteria("tags","t2");
t2.add(Restrictios.eq("grouping", "color");
t2.add(Restrictions.eq("value", "Blue");
But I get a "duplicate association path" error.
Has anyone implemented something similar using Criteria?
Thanks!
Login
FAQ
Search