-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
Arrowx8
 Post subject: Is this type of query possible with Hibernate?
PostPosted: Sun Jan 03, 2016 5:56 pm 
Newbie

Joined: Tue Jun 19, 2007 2:11 pm
Posts: 9
Hello!
I would really appreciate if you guys can give me a hand on this one... I've been struggling for days..
I have two objects (case and tag) mapped bidirectionally ManyToMany.

Case
- caseId
- caseName

(Link table) - links caseId and tagId in ManytoMany fashion

Tag
- tagId
- tagName

Code:
Records:        Link                       Tags:
ID    Case       caseId  tagId        tagId  tagName
1    Case1       1         2               1       TagA
2    Case2       1         1               2       TagB
                    2         1             

I want to make a query to return cases that match to BOTH TagA and TagB

I have done:
Code:
query = "FROM Case AS c INNER JOIN Tag AS t WHERE t.tagName = 'TagA' AND t.tagName = 'TagB'"


However this doesn't return any records.
In MySQL i would need to inner join twice to make this work:
Code:
"FROM Case AS c INNER JOIN Tag AS t1 INNER JOIN Tag AS t2 WHERE t.tagName = 'TagA' AND t2.tagName = 'TagB'"


Is there an eloquent solution in Hibernate? (Hibernate doesn't allow double inner joins as far as i know)
Right now I just load all records for TagA and TagB, and loop through them to make sure they are in both.

Thank you so much!
Top
 Profile  
 
MadhusudanaSunnapu
 Post subject: Re: Is this type of query possible with Hibernate?
PostPosted: Sun Jan 03, 2016 11:39 pm 
Beginner
Beginner

Joined: Thu Nov 26, 2015 11:40 am
Posts: 33
Since it is a bidirectional association, I presume you might be having Tag class mapped something like
Code:
@ManyToMany List<Tag> tags    = new ArrayList<>();
(with field name tags) in your Case class.

With that assumption, you can try the below query that does inner join on Tag class twice.

Code:
query = "select c FROM Case AS c INNER JOIN c.tags AS t1 INNER JOIN c.tags t2 WHERE t1.tagName = 'TagA' AND t2.tagName = 'TagB'"
Top
 Profile  
 
vlad
 Post subject: Re: Is this type of query possible with Hibernate?
PostPosted: Mon Jan 04, 2016 6:23 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
It doesn't work because you need to use an OR and not an AND.
It doesn't make any sense to use an AND against the same filtering criteria because a tag row cannot have a name that's both 'TagA' and 'TagB'.

Instead of:

Code:
query = "FROM Case AS c INNER JOIN Tag AS t WHERE t.tagName = 'TagA' AND t.tagName = 'TagB'"


you should have:

Code:
query = "FROM Case AS c INNER JOIN Tag AS t WHERE t.tagName = 'TagA' OR t.tagName = 'TagB'"
Top
 Profile  
 
MadhusudanaSunnapu
 Post subject: Re: Is this type of query possible with Hibernate?
PostPosted: Mon Jan 04, 2016 6:54 am 
Beginner
Beginner

Joined: Thu Nov 26, 2015 11:40 am
Posts: 33
It is true that
mihalcea_vlad wrote:
It doesn't make any sense to use an AND against the same filtering criteria because a tag row cannot have a name that's both 'TagA' and 'TagB'.


But since Case & Tag are having many-to-many association, the way I understood Arrowx8 intent is- looking for an HQL query that fetches the Case object that is associated with two Tags having tag names as TagA and TagB.
Top
 Profile  
 
vlad
 Post subject: Re: Is this type of query possible with Hibernate?
PostPosted: Mon Jan 04, 2016 7:18 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
But then an OR should work and probably use a DISTINCT to filter same same `Case` entity references.
Top
 Profile  
 
Arrowx8
 Post subject: Re: Is this type of query possible with Hibernate?
PostPosted: Mon Jan 04, 2016 10:51 pm 
Newbie

Joined: Tue Jun 19, 2007 2:11 pm
Posts: 9
Hi there,

Thank you very much for the replies.

MadhusudanaSunnapu: As far as I am aware (unless I tested this wrong), you can't call Join twice on the same table in hibernate, which is where the problem comes from.

mihalcea_vlad: What you propose will get all cases with Tag A Or Tag B. I need cases to be BOTH tagged TagA and TagB (i.e. if Case is mapped to TagA and also mapped to TagB). I am not sure how filtering distinct will fix this issue... can you explain please?
Top
 Profile  
 
MadhusudanaSunnapu
 Post subject: Re: Is this type of query possible with Hibernate?
PostPosted: Mon Jan 04, 2016 11:10 pm 
Beginner
Beginner

Joined: Thu Nov 26, 2015 11:40 am
Posts: 33
I think joining on same table twice is possible with Hibernate. In fact, in my case I have similar domain model like yours wherein, I have Place and Tags objects (and tables) mapped as many-to-many bidirectional with places_tag as association table.

To retrieve Place object that is associated with both TagA and TagB, I have used the following HQL that does an inner join on the same table twice and able to get the right Place object.
Code:
Place place =  (Place) session.createQuery("select p from Place p inner join p.tags t1 inner join p.tags t2 where t1.name=:tag1 and t2.name=:tag2").setString("tag1", tag1).setString("tag2", tag2).uniqueResult();

and this HQL resulted in following SQL.
Code:
Query:["select place0_.id as id1_2_, place0_.name as name2_2_ from place place0_ inner join tag_place tags1_ on place0_.id=tags1_.places_id inner join tag tag2_ on tags1_.tags_id=tag2_.id inner join tag_place tags3_ on place0_.id=tags3_.places_id inner join tag tag4_ on tags3_.tags_id=tag4_.id where tag2_.name=? and tag4_.name=?"]


With your domain model, assuming Tag class is mapped as List<Tag> as below,
Code:
@ManyToMany List<Tag> tags    = new ArrayList<>();

the HQL would translate to
Code:
query = "select c FROM Case AS c INNER JOIN c.tags AS t1 INNER JOIN c.tags t2 WHERE t1.tagName = 'TagA' AND t2.tagName = 'TagB'"

This does does an inner join twice on c.tags. or Did I misinterpret your question?


Last edited by MadhusudanaSunnapu on Tue Jan 05, 2016 2:57 am, edited 1 time in total.

Top
 Profile  
 
Arrowx8
 Post subject: Re: Is this type of query possible with Hibernate?
PostPosted: Mon Jan 04, 2016 11:24 pm 
Newbie

Joined: Tue Jun 19, 2007 2:11 pm
Posts: 9
Thanks, i'll give it another try!
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.