Wednesday, August 29, 2012

Articles with tags queries : HQL/JPQL/MySql

http://www.sergiy.ca/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

No comments:

Blog Archive