Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 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: 1038
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


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: 1038
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?

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


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: 1038
I'll answer with a blog post. Check out the solution on my blog.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


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  
Post new topic Reply to topic  [ 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:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.