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.tagidWHERE 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.idGROUP BY a.idHAVING 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.