Hello. I've got 2 pojos and 3 tables:
Entity: Article
Code:
mysql> select id, title from articles;
+----+----------+
| id | title |
+----+----------+
| 1 | foo |
| 2 | bar |
| 3 | test |
| 4 | example |
| 5 | demo |
+----+----------+
Entity: Category
Code:
mysql> select id, title from categories;
+----+----------+
| id | title |
+----+----------+
| 1 | cat1 |
| 2 | cat2 |
| 3 | cat3 |
+----+----------+
Code:
<set name="categories" table="article_categories">
<key column="article_id" />
<many-to-many column="category_id" class="com.Category" />
</set>
many-to-many table generated from the above hibernate mapping:
Code:
mysql> select * from article_categories;
+------------+-------------+
| article_id | category_id |
+------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
| 3 | 3 |
| 5 | 3 |
+------------+-------------+
I'd like to be able to do the following (expressed in sql) in hql:
Code:
mysql> select id from articles, article_categories where
articles.id=article_categories.article_id and category_id in (1,2);
+----+
| id |
+----+
| 1 |
| 2 |
+----+
Is it possible?
I did it with SQLQuery but not HQL and I need it in HQL in order to be able to use the query cache for it.
In HQL I managed to do it but it joins all 3 tables and I don't want that:
Code:
select a.id from Article a join a.categories as c where c.id in (1, 2)
Login
FAQ
Search