-->
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
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
Frantz
 Post subject: Query by example : complex subquery whith historized data
PostPosted: Wed Aug 09, 2006 10:11 am 
Newbie

Joined: Wed Aug 09, 2006 7:59 am
Posts: 9
Location: Nantes - France
Hello,

I'm using Hibernate 3.1.3 with MySQL 5.0 and Oracle 9 and I have a problem with a query by example which includes an historized field.
I tkink my context is a very classical scenario. But, I've done two days of googling and searching here, and I'm not finding something that helps me.

I have sone users with positions historiezd by timestamps :
Code:
table-> User  id {pk}, name, ...
table-> position id {pk}, user_id {fk}, date, status, office, ...

In the model the object User has a list of Position but only the earliest one is currently valid.
The end-user has a from screen to seach a user (so a query by sample is very usefull).


I'am scratching my head over this query and how to create a Criteria query to complete the query by sample...

I've, first, write this :
Code:
Example ex = Example.create(user);
Criteria crit = session.createCriteria(User.class).add(ex);
crit.createCriteria("positions", "pos")
    .add(Expression.eq("pos.status", userStatus);


It's ok, but (as I can predict it) it returns the same user each time a macthing row is found in the history of positions (unless isn't the ealier one).

So this is the SQL I want to translate :
Code:
SELECT u.*
FROM user u, position pos,
(SELECT user_id, MAX(date) AS last_date
FROM position GROUP BY user_id) last_pos
WHERE pos.user_id = last_pos.user_id
AND pos.date = last_pos.last_date;
AND u.id = pos.user_id
AND pos.status = ?
AND a.name =  ?
... //others user form fields here


So, I decided to define a subquery to obtain le last position.
Code:
DetachedCriteria subquery = DetachedCriteria.forClass(Position.class, "last_pos");
subquery.setProjection(
   Projections.projectionList()                  
             .add( Projections.max("last_pos.date"))
         .add( Projections.groupProperty("last_pos.userAgent")
);

Example ex = Example.create(user);
Criteria crit = session.createCriteria(User.class).add(ex);
crit.createCriteria("positions", "pos")
    .add(Property.forName("pos.date").eq(subquery))
    .add(Expression.eq("pos.status", userStatus);



and I obtain the following exception :
Quote:
java.lang.ClassCastException: org.hibernate.impl.CriteriaImpl$Subcriteria
at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:43)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:333)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1514)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
...


What I have done wrong ? (I've tried namy variations of that with the same result)

Currently, the cat takes my tongue.
Any kind of help, suggestions, or any link to complex Criteria query samples are welcome.

Thanks,

--
Frantz D.
Top
 Profile  
 
Frantz
 Post subject: I found a solution !
PostPosted: Thu Aug 10, 2006 4:42 am 
Newbie

Joined: Wed Aug 09, 2006 7:59 am
Posts: 9
Location: Nantes - France
Hello,

Gotcha ! It works (after 2 more days of hard work and a big headache ;( ).

First, the SQL query can be re-written (and simplified) using an explicit join instead of the theta-style one, as following :

Code:
SELECT u.*
FROM user u
INNER JOIN position pos,
ON u.id = pos.user_id
WHERE pos.date = (SELECT MAX(last_pos.date)
        FROM position last_pos
                  WHERE last_pos.userId = u.id)
AND pos.status = ?
AND a.name =  ?
... //others user form fields here

With the query written like this I have noticed that the subquery must be attached to the main Criteria object (not to the "positions" one). So, my code, now, looks like this :
Code:
Example ex = Example.create(user);

DetachedCriteria subquery = DetachedCriteria.forClass(Position.class, "last_pos");
subquery.setProjection(Projections.max("last_pos.date"));
subquery.add(Expression.eqProperty("last_pos.userId", "u.id"));

Criteria crit = session.createCriteria(Agent.class, "a")
crit.add(ex);               
crit.createCriteria("positions", "pos");
crit.add(Subqueries.propertyEq("pos.date", subquery));
crit.add(Expression.eq("pos.status", userStatus));

return crit.list();


It's all.

--
Frantz D.
Top
 Profile  
 
astubbs
 Post subject:
PostPosted: Wed Oct 17, 2007 8:16 am 
Newbie

Joined: Mon Sep 17, 2007 11:42 pm
Posts: 16
Location: Auckland, New Zealand
Have a look guys if you're still interested. I've raised an issue and submitted a patch against trunk to address this.

Criteria api does not allow a groupProperty to _NOT_ be included in the selected columns
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2893

Basically it allows you to specify a groupProperty like this:
Code:
Projections.groupProperty("id.variableId",true)

Javadoc:
Quote:
@param excludeFromSelect
indicates not to include this parameter in the select clause,
and _only_ in the GROUP BY clause.
Top
 Profile  
 
pvradhakrishna
 Post subject: Re: Query by example : complex subquery whith historized data
PostPosted: Tue Jul 27, 2010 2:40 pm 
Regular
Regular

Joined: Tue Feb 19, 2008 6:05 pm
Posts: 82
Nice work around!
Top
 Profile  
 
shuklabhanuprakash
 Post subject: Re: Query by example : complex subquery whith historized data
PostPosted: Mon Jun 23, 2014 6:03 am 
Newbie

Joined: Mon Jun 23, 2014 5:46 am
Posts: 1
Hi everyone,

I have created the complex subquery and I am not able to write criteria for this.Please do me some favor.

SELECT ua.id AS activity_id, ua.created_date, f.filter_name, uaf.filters AS filter_id
FROM user_activity ua
JOIN user_activity_filters uaf
ON ua.id = uaf.user_activity
JOIN filters f
ON uaf.filters = f.id
WHERE ua.id IN (
SELECT * FROM (
SELECT DISTINCT ua.id as activity_id
FROM user_activity ua
JOIN user_activity_filters uaf
ON ua.id = uaf.user_activity
WHERE ua.user_id = 2
ORDER BY ua.created_date DESC
LIMIT 3
)
AS f)
ORDER BY ua.created_date DESC
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
  [ 5 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.