http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate/
Lets review basic many-to-many relationship between tables and build common search queries in MySQL and Hibernate HQL. We will take this site's database schema which has article-tag many-to-many relationship as an example and will try to build search queries to find articles by specific tags.
Here is our database schema that implements many-to-many relationship using intermediate "article_tag" table:
Corresponding Hibernate mappings:
<hibernate-mapping>
<class name=ca.sergiy.model.Tag" table="tag">
<cache usage="read-write"/>
<id name="id" column="id" type="long">
<generator class="native"/>
</id>
<property name="name" column="name"/>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class name="ca.sergiy.model.Article" table="article">
<cache usage="read-write" />
<id name="id" column="id" type="long">
<generator class="native" />
</id>
<property name="title" column="title" />
<set name="tags" table="article_tag" lazy="false">
<key column="articleid" />
<many-to-many class="ca.sergiy.model.Tag" column="tagid" />
</set>
</class>
</hibernate-mapping>
#1. Find all articles that are tagged with any of tag1, tag2, ..., tagn
MySQL query to select all articles that have "Java" or "Hibernate" among their assigned tags:
SELECT DISTINCT a.*
FROM `article` a
INNER JOIN article_tag at
ON at.articleid = a.id
INNER JOIN tag t
ON t.id = at.tagid
WHERE t.name IN ("Java", "Hibernate")
Corresponding Hibernate HQL:
String[] tags = {"Java", "Hibernate"};
String hql = "select distinct a from Article a " +
"join a.tags t " +
"where t.name in (:tags)";
Query query = session.createQuery(hql);
query.setParameterList("tags", tags);
List<Article> articles = query.list();
This query will also work for a single tag (find all articles tagged with "Java")
#2. Find all articles that have no tags assigned
MySQL query:
SELECT a.*
FROM `article` a
LEFT JOIN article_tag at
ON at.articleid = a.id
GROUP BY a.id
HAVING Count(at.tagid) = 0
Corresponding Hibernate HQL:
String hql = "select a from Article a " +
"left join a.tags t " +
"group by a " +
"having count(t)=0";
Query query = session.createQuery(hql);
List<Article> articles = query.list();
Note that this query uses
LEFT JOIN.#3. Find all articles that are tagged with at least tag1, tag2, ..., tagn
MySQL query to select all articles that have at least both "Java" and "Hibernate" among their assigned tags:
SELECT a.*
FROM article a
INNER JOIN (SELECT at.articleid
FROM article_tag at
INNER JOIN article a
ON a.id = at.articleid
INNER JOIN tag t
ON t.id = at.tagid
WHERE t.name IN ("Java","Hibernate")
GROUP BY at.articleid
HAVING Count(at.articleid) = 2) aa
ON a.id = aa.articleid
Hibernate HQL, looks much cleaner:
String[] tags = {"Java", "Hibernate"};
String hql = "select a from Article a " +
"join a.tags t " +
"where t.name in (:tags) " +
"group by a " +
"having count(t)=:tag_count";
Query query = session.createQuery(hql);
query.setParameterList("tags", tags);
query.setInteger("tag_count", tags.length);
List<Article> articles = query.list();
#4. Find all articles that are tagged with exactly tag1, tag2, ..., tagn
MySQL query to select all articles that are tagged with exactly "Java" and "Hibernate" tags (no other tags assigned):
SELECT a.*
FROM article a
INNER JOIN (SELECT at.articleid
FROM article_tag at
WHERE at.articleid IN (SELECT at2.articleid
FROM article_tag at2
INNER JOIN article a2
ON a2.id = at2.articleid
GROUP BY at2.articleid
HAVING Count(at2.articleid) = 2)
AND at.tagid IN (SELECT id
FROM tag t
WHERE t.name IN ("Java","Hibernate"))
GROUP BY at.articleid
HAVING Count(at.articleid) = 2) aa
ON a.id = aa.articleid
Hibernate HQL:
String[] tags = {"Java", "Hibernate"};
String hql = "select a from Article a " +
"join a.tags t " +
"where t.name in (:tags) " +
"and a.id in (" +
"select a2.id " +
"from Article a2 " +
"join a2.tags t2 " +
"group by a2 " +
"having count(t2)=:tag_count) " +
"group by a " +
"having count(t)=:tag_count";
Query query = session.createQuery(hql);
query.setParameterList("tags", tags);
query.setInteger("tag_count", tags.length);
List<Article> articles = query.list();
Basically it is query #3 with extra condition applied: total number of tags should be n.
Aug 21, 2009
