-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: HQL query over a Period using PostgresDB
PostPosted: Wed Jan 18, 2017 6:30 am 
Newbie

Joined: Wed Jan 18, 2017 6:08 am
Posts: 4
Hello,

i have 3 tables:

table1: stable
s_id | s_title
1 | stable1
2 | stable2

tabl2: animal
a_id | a_name
1 | Linda
2 | Berta
3 | Siggi

table3: removals
re_id | re_date | re_a_id | re_s_id
1 | 2017-01-10 | 1 | 1
2 | 2017-01-11 | 1 | 2
3 | 2017-01-11 | 2 | 1
4 | 2017-01-12 | 1 | 1
5 | 2017-01-13 | 1 | 2
6 | 2017-01-14 | 3 | 1


now i will check, how many animals were in Stable 1 on 2017-01-12 (answer: 2)

Code:
Query qu = s.createQuery("from Animal as animal  where animal IN (select removal.animal from Removal as removal where re_s_id ="+stable.getSId()+")");

//INFO 4 the forum: stable.getSId() returns 1


returns 3, cause I don't know how to realize it with the date

I hope you can help me :-)

Traxxas


Top
 Profile  
 
 Post subject: Re: special HQL on PostgresDB and Period
PostPosted: Wed Jan 18, 2017 7:18 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
It's not clear what the removal table represent. What is the re_date? Is it the date the animal was removed from the stable? But then, how do you know when it was added to the stable? Try explaining how this table is supposed to work first.


Top
 Profile  
 
 Post subject: Re: special HQL on PostgresDB and Period
PostPosted: Wed Jan 18, 2017 7:57 am 
Newbie

Joined: Wed Jan 18, 2017 6:08 am
Posts: 4
Hello Vlad,

thanks for you fast response.

the removal table looks like this:
table3: removals
re_id | re_date | re_a_id | re_s_id
1 | 2017-01-10 | 1 | 1
2 | 2017-01-11 | 1 | 2
3 | 2017-01-11 | 2 | 1
4 | 2017-01-12 | 1 | 1
5 | 2017-01-13 | 1 | 2
6 | 2017-01-14 | 3 | 1

explanation:
Linda join stable 1 on 2017-01-10 *
Linda join stable 2 on 2017-01-11 *
Berta join stable 1 on 2017-01-11
Linda join stable 1 on 2017-01-12

Berta join stable 2 on 2017-01-13
Siggi join stable 1 on 2017-01-14


* On this two lines you can see that Linda was in stable 1 from 2017-01-10 till 2017-01-11

I look for the two bold Lines.
Berta joined on 2017-01-11 and Linda on 2017-01-12


Top
 Profile  
 
 Post subject: Re: special HQL on PostgresDB and Period
PostPosted: Wed Jan 18, 2017 8:23 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
So, these 3 records

Code:
Linda join stable 1 on 2017-01-10
Linda join stable 2 on 2017-01-11
Linda join stable 1 on 2017-01-12


Means that Linda has first joined Stable1, then Stable2, and then it joined Stable1 again, right?


Top
 Profile  
 
 Post subject: Re: special HQL on PostgresDB and Period
PostPosted: Wed Jan 18, 2017 8:40 am 
Newbie

Joined: Wed Jan 18, 2017 6:08 am
Posts: 4
vlad wrote:
So, these 3 records

Code:
Linda join stable 1 on 2017-01-10
Linda join stable 2 on 2017-01-11
Linda join stable 1 on 2017-01-12


Means that Linda has first joined Stable1, then Stable2, and then it joined Stable1 again, right?


Yes


Top
 Profile  
 
 Post subject: Re: special HQL on PostgresDB and Period
PostPosted: Wed Jan 18, 2017 11:57 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
I'll answer with a blog post. Check out the solution on my blog.


Top
 Profile  
 
 Post subject: Re: special HQL on PostgresDB and Period
PostPosted: Wed Jan 18, 2017 12:55 pm 
Newbie

Joined: Wed Jan 18, 2017 6:08 am
Posts: 4
vlad wrote:
I'll answer with a blog post. Check out the solution on my blog.



Thanks a lot!! worked for me :-)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

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.