[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1685) DetachedCriteria doesn't create alias on subcriteria

[John (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1685?page=comments#action_22827 ] 

John commented on HHH-1685:
---------------------------

Okay, here's a representative of the sql I wanted.  I need to find some rows with a certain status.  I need to limit the results, but I have to process the children grouped by parent, so the row limit has to be on the parent, hence the 'in' subquery.  The 'in' subquery has an exists because which parents qualify depends on the status of their other children.

select c.* 
from 
	child c join parent p on c.parent_id = p.id
where 
	c.status = 'findme'
	and p.id in (
		select top 50 p.id
		from 
			child c2
				join parent p2 on c2.parent_id = p2.id
		where 
			c2.status = 'findme'
			and not exists (select * from other_child oc where oc.parent_id = p2.id and oc.status != 'done')
	)

This is the Criteria used:

Criteria crit = session.createCriteria(Child.class, "c");
Criteria pCrit = crit.createCriteria("parent", "p");

crit.add(Restrictions.eq(Child.STATUS, 'findme'));

DetachedCriteria inSubq = DetachedCriteria.forClass(Child.class, "c2");
DetachedCriteria inPCrit = inSubq.createCriteria("parent", "p2");

inSubq.add(Restrictions.eq(Child.STATUS, 'findme'));

inPCrit.setProjection( Projections.id() );
pCrit.add( Subqueries.propertyIn(Parent.ID, inSubq) );

DetachedCriteria existsSubq = DetachedCriteria.forClass(OtherChild.class, "oc");
existsSubq.add(Restrictions.ne(OtherChild.STATUS, 'done'));
existsSubq.add( Restrictions.eqProperty("oc.parent.id", "p2.id") ); //didn't work without patch in HHH-1685
existsSubq.setProjection(Projections.id());
inSubq.add( Subqueries.notExists(existsSubq) );

inSubq.setMaxResults(50); //doesn't work - see HHH-912


Without the patch I indicated, the join from the exists query to the in query didn't work because the alias was never registered.  Also note that while I fixed this problem, I currently cannot complete this query because maxResults does not work on DetachedCriteria, nor on subqueries at all.  See HHH-912 for details.

> DetachedCriteria doesn't create alias on subcriteria
> ----------------------------------------------------
>
>          Key: HHH-1685
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1685
>      Project: Hibernate3
>         Type: Bug

>   Components: query-criteria
>     Versions: 3.1.3
>     Reporter: John

>
>
> DetachedCriteria has two createCriteria methods:
> 	public DetachedCriteria createCriteria(String associationPath)	throws HibernateException 
> 	public DetachedCriteria createCriteria(String associationPath, String alias) throws HibernateException
> However, the code for both was identical - they called the inner criteria's createCriteria(String) method. The version with the alias did not call the inner's version with the alias.  The following patch snipped shows the change:
>  	public DetachedCriteria createCriteria(String associationPath, String alias)
>  	throws HibernateException {
> -		return new DetachedCriteria( impl, criteria.createCriteria(associationPath) );
> +		return new DetachedCriteria( impl, criteria.createCriteria(associationPath, alias) );
>  	}
> This bug was discovered while trying to link a 2-deep subquery to its parent via aliases.  (Surfacing this issue might only be possible with the patch HHH-952 in place - I'm not sure.  Seems like a bug regardless, as it definitely fixed my alias reference issue.)  I'll get an example posted shortly.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira