How to Write Many-To-Many Search Queries in MySQL and Hibernate

Java MySQL 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:

Table Schema

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
profile for serg at Stack Overflow, Q&A for professional and enthusiast programmers