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

santaclaus

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.