hibernate-issues

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[<leg...@at...>]

The following comment has been added to this issue:

     Author: Michael Greer
    Created: Sat, 13 Dec 2003 9:14 AM
       Body:
<p>I believe this is similar to <a href="http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-123">Apply distinctness to HQL fetch of collection</a>, but I voted for this anyway.</p>
<p>The return of duplicates in an entity list if you eagerly fetch their children is unexpected behavior which, while easily remedied in the app, perhaps does not belong in this layer. I understand that adding "DISTINCT" to the query would be a major performance hit, so why not handle it programatically after returning the results? We have to handle it in any case, since it is an unwieldy and unexpected result, so we might as well hide it in the proper layer.</p>
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-520


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-520
    Summary: Add Distinct to Criteria API
       Type: New Feature

     Status: Unassigned
   Priority: Minor

    Project: Hibernate2
 Components: 
             core
   Versions:
             2.1 rc1

   Assignee: 
   Reporter: Stephen Owens

    Created: Thu, 4 Dec 2003 7:08 AM
    Updated: Sat, 13 Dec 2003 9:14 AM
Environment: Hibernate 2.1+, (Any DB)

Description:
Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 

"select distinct parent from Parent as parent join Parent.children as child where child.age>5"


---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[<no...@at...>]

The following comment has been added to this issue:

     Author: John Ramkawsky
    Created: Tue, 16 Aug 2005 1:21 PM
       Body:
It seems you may be able to accomplish what you want by using
 setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).

See: http://www.hibernate.org/hib_docs/api/net/sf/hibernate/Criteria.html#setResultTransformer(net.sf.hibernate.transform.ResultTransformer)
---------------------------------------------------------------------
View this comment:
  http://opensource.atlassian.com/projects/hibernate/browse/HB-520?page=comments#action_19372

---------------------------------------------------------------------
View the issue:
  http://opensource.atlassian.com/projects/hibernate/browse/HB-520

Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-520
    Summary: Add Distinct to Criteria API
       Type: New Feature

     Status: Unassigned
   Priority: Minor

 Original Estimate: Unknown
 Time Spent: Unknown
  Remaining: Unknown

    Project: Hibernate2
 Components: 
             core
   Versions:
             2.1 rc1

   Assignee: 
   Reporter: Stephen Owens

    Created: Thu, 4 Dec 2003 7:08 AM
    Updated: Tue, 16 Aug 2005 1:21 PM
Environment: Hibernate 2.1+, (Any DB)

Description:
Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 

"select distinct parent from Parent as parent join Parent.children as child where child.age>5"


---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[Artti J. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-520?page=comments#action_19631 ] 

Artti Jaakkola commented on HB-520:
-----------------------------------

Problem with setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) is that it's done only after the query is executed.

If you happen to have criteria.setMaxResults(10) and first 8 items equal with each other, then you get only 3 items as a result:

List results = session
    .createCriteria(User.class)
    .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
    .setMaxResults(itemCount.intValue())
    .list();

Now I don't know if I should fetch all the items from database and then just cut the first 10 or what...

> Add Distinct to Criteria API
> ----------------------------
>
>          Key: HB-520
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-520
>      Project: Hibernate2
>         Type: New Feature
>   Components: core
>     Versions: 2.1 rc1
>  Environment: Hibernate 2.1+, (Any DB)
>     Reporter: Stephen Owens
>     Priority: Minor

>
>
> Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 
> "select distinct parent from Parent as parent join Parent.children as child where child.age>5"

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

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[Erick D. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-520?page=comments#action_20362 ] 

Erick Dovale commented on HB-520:
---------------------------------

Another problem related to using setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) happens when using pagination. The counting the results is fairly easy and works fine even when counting distinct results, however, the count won't match the actual results if setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) is used afterward.


> Add Distinct to Criteria API
> ----------------------------
>
>          Key: HB-520
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-520
>      Project: Hibernate2
>         Type: New Feature
>   Components: core
>     Versions: 2.1 rc1
>  Environment: Hibernate 2.1+, (Any DB)
>     Reporter: Stephen Owens
>     Priority: Minor

>
>
> Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 
> "select distinct parent from Parent as parent join Parent.children as child where child.age>5"

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

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[Bill B. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-520?page=comments#action_21552 ] 

Bill Burke commented on HB-520:
-------------------------------

Any decision on this matter? I'm writing OReilly EJB3 book and need to know sooner or later. Couldn't this be as simple as taking the results and putting them into a LinkedhashMap and then doing a new List(map.keySet());?

> Add Distinct to Criteria API
> ----------------------------
>
>          Key: HB-520
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-520
>      Project: Hibernate2
>         Type: New Feature
>   Components: core
>     Versions: 2.1 rc1
>  Environment: Hibernate 2.1+, (Any DB)
>     Reporter: Stephen Owens
>     Priority: Minor

>
>
> Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 
> "select distinct parent from Parent as parent join Parent.children as child where child.age>5"

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

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[Caleb P. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-520?page=comments#action_21553 ] 

Caleb Phillips commented on HB-520:
-----------------------------------

Would the results of that be any different from using the ResultsTransformer?  I had the same issue as mentioned above - I need the distinct to be applied before the maxResults and since the maxResults is accomplished in the database (at least in the Oracle implementation), it seems like the distinct would have to be done there too.

> Add Distinct to Criteria API
> ----------------------------
>
>          Key: HB-520
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-520
>      Project: Hibernate2
>         Type: New Feature
>   Components: core
>     Versions: 2.1 rc1
>  Environment: Hibernate 2.1+, (Any DB)
>     Reporter: Stephen Owens
>     Priority: Minor

>
>
> Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 
> "select distinct parent from Parent as parent join Parent.children as child where child.age>5"

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

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[Pavel G. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-520?page=comments#action_22865 ] 

Pavel Grushetzky commented on HB-520:
-------------------------------------

It would be nice if hibernate could fetch associations this way
1. Select root entities, apply max rows limit
2. In one shot select required associations with something like "WHERE root_entity_id IN (....)"


> Add Distinct to Criteria API
> ----------------------------
>
>          Key: HB-520
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-520
>      Project: Hibernate2
>         Type: New Feature

>   Components: core
>     Versions: 2.1 rc1
>  Environment: Hibernate 2.1+, (Any DB)
>     Reporter: Stephen Owens
>     Priority: Minor

>
>
> Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 
> "select distinct parent from Parent as parent join Parent.children as child where child.age>5"

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

[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-520) Add Distinct to Criteria API

[Sylvain T. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-520?page=comments#action_22991 ] 

Sylvain Tremblay commented on HB-520:
-------------------------------------

I have been working on a new Projection called RootProjection which can be used to solve this problem. 
This projection can be compared to the PropertyProjection but all fields from the Root criteria are returns. 

You can then set the projection on the Criteria and chain it with the Distinct Projection:

Criteria myCriteria = mySession.createCriteria(MyRootClass.class);
myCriteria.createCriteria("association1").createCriteria("association2)......add(Restrictions.eq("someField", "someValue");

myCriteria.setProjection(Projections.distinct(Projections.RootProjection()));


The select generated will then look like this:

select root.field1, root.field2, .... from root inner join association1......inner join associations2........where..........


Then you use the myCriteria.list() to get DISTINCT root entities.

However to make this work minor changes needs to be done in Hibernate. 
I will be submitting a patch so this gets integrated in future version.

Sylvain

> Add Distinct to Criteria API
> ----------------------------
>
>          Key: HB-520
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-520
>      Project: Hibernate2
>         Type: New Feature

>   Components: core
>     Versions: 2.1 rc1
>  Environment: Hibernate 2.1+, (Any DB)
>     Reporter: Stephen Owens
>     Priority: Minor

>
>
> Need the ability to specify that results of a Criteria query should be 'DISTINCT', especially when joining to a collection. So the Criteria equivalent of the HQL statement 
> "select distinct parent from Parent as parent join Parent.children as child where child.age>5"

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