Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 28 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: select distinct entities using Criteria
PostPosted: Wed Apr 27, 2005 11:38 am 
Newbie

Joined: Mon Apr 25, 2005 11:47 am
Posts: 5
Location: Lithuania
Hibernate version: 3.0.2

Hi, i simply need to extract a number of entities using Criteria API. But i have a hard time figuring out how to implements this kind of behaviour:

select distinct p from Parent p left join p.children c where c.name in ('Some','Name')

i know maybe distinct is not necessary in here, but it's just an example. also i'd like to note, that i need to extract only parent entities (i have no need for children, of course this is simply done by setting a fetchmode = lazy on children).

distinct is implemented in Criteria API as a Projection, and as i understand if one sets a Projection, then a select list part is totally up to him (if no projection is referred, selected column list is constructed automatically). So, for it to work properly i'd need to do something like:
c.setProjection(Projections.distinct(Projections.entity(Parent.class)));
but the problem is, that Projections doesn't have such a method :)

---

Big thanks to Hibernate team for implementing distinct in Criteria API, for the past few days i've been looking for it, and today just out of nowhere i found it in H 3.0.2 :)


Top
 Profile  
 
 Post subject: Distinct
PostPosted: Thu Jul 07, 2005 4:20 pm 
Newbie

Joined: Thu Jun 09, 2005 12:47 pm
Posts: 5
Did you ever find a solution to this problem? I'm running into the same wall.

Thanks in Advance,
Jacob


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 07, 2005 4:52 pm 
Pro
Pro

Joined: Fri Nov 19, 2004 5:52 pm
Posts: 232
Location: Chicago, IL
I want to say it will give you want you want if you simply do the following,

Code:
from Parent p join p.children c where c.name in ('Some', 'Name')


i.e. just do a join instead of a left join. I could be wrong on this though. I'm not really an expert on HQL yet.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 07, 2005 4:59 pm 
Pro
Pro

Joined: Fri Nov 19, 2004 5:52 pm
Posts: 232
Location: Chicago, IL
I see that you want to do it using the Criteria API. I think the key is to use Criteria.setResultTransformer() with Criteria.DISTINCT_ROOT_ENTITY. This is how I did it. Note, this isn't EJB 3.0.

Code:
      Criteria c = s.createCriteria(Reservation.class)
         .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
         .createAlias("user", "u")
         .createAlias("items", "i")
         .addOrder(Order.asc("startTime"))
         .addOrder(Order.asc("u.userId"));
      int id = reservation.getId();
      if(id != 0) {
         c.add(Expression.eq("id", id));
      }
      Date startTime = reservation.getStartTime();
      if(startTime != null) {
         c.add(Expression.ge("startTime", startTime));
      }
      Date endTime = reservation.getEndTime();
      if(endTime != null) {
         c.add(Expression.lt("startTime", endTime));
      }
      String userId = reservation.getUser().getUserId().trim().replaceAll("\\*", "%");
      if(! userId.equals("")) {
         c.add(Expression.like("u.userId", userId));
      }


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 08, 2005 5:04 am 
Newbie

Joined: Mon Apr 25, 2005 11:47 am
Posts: 5
Location: Lithuania
Yes you're right Criteria.DISTINCT_ROOT_ENTITY does solve this problem partially, but it is implemented not in a database side - it's programically filterred. Lets say you have a query, which returns > 4000 (not distinct) records, and you need a set from 20th to 40th distinct element. So in this case you'll have to fetch all those records, to filter distinct records with result transformer and only then get a set from 20th to 40th record.


Top
 Profile  
 
 Post subject: Still a problem
PostPosted: Fri Jul 08, 2005 9:38 am 
Newbie

Joined: Thu Jun 09, 2005 12:47 pm
Posts: 5
Toqwaz you hit the nail dead on, searching through the forums and docs; I have noticed several people having the same issue with the criteria api but, with no solutions other than to use HQL or SQL. The point of using the criteria api is to abstract HQL or SQL from the programmer, which is what I want for my project. Not being able to select distinct records in which there are duplicates in the results, is a real deal breaker, such a common function in SQL programming. Anyways, thanks for your replies, I will write this functionality in SQL.

Thanks again.
Jacob


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 08, 2005 10:01 am 
Pro
Pro

Joined: Fri Nov 19, 2004 5:52 pm
Posts: 232
Location: Chicago, IL
I agree that there should be a way to perform the filtering using the SQL server. Personally, I find HQL to be easier to use though (it's a lot less API calls) unless I'm building the query dynamically. So, you're going to drop all the way back to using SQL instead of just using HQL?

Come to think about it, I don't think the EJB 3.0 API has an equivalent to the Criteria API...


Top
 Profile  
 
 Post subject: one of the solutions
PostPosted: Thu Jul 21, 2005 5:41 am 
Newbie

Joined: Mon Apr 25, 2005 11:47 am
Posts: 5
Location: Lithuania
Finally i found a solution, that i'm satisfied with. To use an AliasToBeanResultTransformer. It's accually pretty simple:

Code:
Criteria c = session.createCriteria(User.class)
       .createAlias("roles", "r")
       .add(Expression.or(Expression.eq("r.roleNr", new Long(410)), Expression.eq("r.roleNr", new Long(414))))
       .setProjection(Projections.distinct(Projections.projectionList().add(Projections.property("userId"), "userId").add(Projections.property("userName"), "userName")))
       .setResultTransformer(new AliasToBeanResultTransformer(User.class));
List users = c.list();
((User)users.get(0)).getUserName();


But a little inconveniece is that you must specify all the property's you wan't to be fetched (in the previous example only userId and userName will be set). But the good thing is that we can get them dynamically:

Code:
String[] properties = session.getSessionFactory().getClassMetadata(User.class).getPropertyNames();


also we can have a method that constructs a ProjectionList:

Code:
  public Projection formProjection(String[] properties) {
        ProjectionList list = Projections.projectionList();
        for (int i = 0; i < properties.length; ++i)
            list.add(Projections.property(properties[i]), properties[i]);
        return list;
  }

[/code]


Top
 Profile  
 
 Post subject: Error in SQL when using AliasToBeanResultTransformer
PostPosted: Thu Aug 11, 2005 5:19 am 
Newbie

Joined: Mon Aug 08, 2005 11:19 am
Posts: 5
Hi All,

Followed this thread with interest - and adopted solution offered by Toqwaz.

Also combined it with some Example criteria in order to dynamically work out the where clause.

In the end my code looked like:
Code:
reports = session.createCriteria(MatchReportHeader.class)
                                        .setProjection(Projections
                                                          .distinct(Projections.projectionList()
                                                          .add(Projections.property("id", ))
                                                          .add(Projections.property("dateProduced"))
                                                          .add(Projections.property("matchReportID"))
                                                          .add(Projections.property("matchStatus"))
                                                          .add(Projections.property("processedFlag"))))
                                        .add( Example.create(exampleHeader)
                                                     .excludeProperty("processedFlag")
                                                     .excludeZeroes()
                                                     .enableLike(MatchMode.ANYWHERE) )
                                         .createCriteria("samples")
                                         .add( Example.create(exampleSample)
                                                      .excludeZeroes()
                                                      .enableLike(MatchMode.ANYWHERE))
                                        .setMaxResults(10)
                                        .setFirstResult(3)
                                        .list();



This worked fine for when I searching on properties of the child (exampleSample) - but produced a SQL error when querying against properties of the parent (exampleHeader).

The SQL Server I got was

Code:
caused by: : java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Invalid column name 'y3_'.


and the sql was

Code:
select distinct top 13 this_.id as y0_, this_.dateProduced as y2_, this_.matchReportID as y3_, this_.matchStatus as y4_, this_.processedFlag as y5_ from matchReportHeader this_ inner join matchReportSample s1_ on this_.id=s1_.headerID left outer join matchReportHeader matchrepor4_ on s1_.headerID=matchrepor4_.id where (y3_=?) and (1=1)


You can see that the where clause is using the alias y3_ which SQL Server doesn't like.

Is this a bug?

And does anyone have a workaround?

Cheers


Top
 Profile  
 
 Post subject: same problem for me...
PostPosted: Wed Sep 14, 2005 1:05 pm 
Newbie

Joined: Thu Jun 02, 2005 11:20 am
Posts: 9
Quote:
select distinct top 13 this_.id as y0_, this_.dateProduced as y2_, this_.matchReportID as y3_, this_.matchStatus as y4_, this_.processedFlag as y5_ from matchReportHeader this_ inner join matchReportSample s1_ on this_.id=s1_.headerID left outer join matchReportHeader matchrepor4_ on s1_.headerID=matchrepor4_.id where (y3_=?) and (1=1)


You can see that the where clause is using the alias y3_ which SQL Server doesn't like.

Is this a bug?

And does anyone have a workaround?

Cheers


i'm getting the exact same problem:

select distinct this_.content_type_id as y0_, this_.title as y1_, this_.summary as y2_, this_.branch as y3_, this_.relative_path as y4_, this_.image_path as y5_, this_.creation_date as y6_, this_.expiration_date as y7_, this_.last_update_date as y8_, this_.last_update_user as y9_,
this_.content_id as y10_, this_.content_id as y11_, this_.content_id as y12_, this_.content_id as y13_ from iwsc_content this_ left outer join iwsc_news this_1_ on this_.content_id=this_1_.content_id left outer join iwsc_event this_2_ on this_.content_id=this_2_.content_id left outer join iwsc_document this_3_ on this_.content_id=this_3_.content_id inner join iwsc_content_category categories3_ on this_.content_id=categories3_.content_id inner join iwsc_category category1_ on categories3_.category_id=category1_.category_id where (y7_ is null or y7_>?) and category1_.category_id in (?, ?, ?, ?) and y3_ in (?)]

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'y7_'.

i'm using Projections.distinct exactly as Toqwax suggests. anyone have an idea on this??


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 21, 2005 4:15 am 
Newbie

Joined: Wed Sep 21, 2005 3:59 am
Posts: 1
Using mySql i encountered the same problem.

I am trying to select a given number of distinct records that involves a M-to-M relationship join. It also doesn't work because of the use of aliases in the SQL WHERE clause:

Code:
Caused by: java.sql.SQLException: null,  message from server: "Unknown column 'y0_' in 'where clause'"



where the SQL is:

Code:
select distinct this_.JURIDISK_NAVN as y0_ from COMPANY_ACTIVE this_ inner join category_company_map c
ategories4_ on this_.PK_MMGUL=categories4_.company_id inner join category cat2_ on categories4_.category_id=cat2_.id inner join company_status_map statuses6_ on this_.PK_MMGUL=statuses6_.company_id inner join company_status stat1_ on statuses6_.status_id=stat1_.id
where y0_ like ? and stat1_.name=?    --  <--- this line
order by y0_ asc


From the mysql.com site:

Quote:
It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.


What am I missing here ?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 21, 2005 9:52 am 
Newbie

Joined: Thu Jun 02, 2005 11:20 am
Posts: 9
okay, so i got around this issue... this is a hack, but it works.. i only did this b/c i saw the that y0_ behavior was intentional ( Gavin has commented about how it is valid SQL and should be acceptable -- not sure of the msg id, but it's somewhere in this forum ). as such, i figured a hack was the only way around it.

basically what i do is define aliases for all of my projected parameters. from the previous posts, i assume you did something like this to generate the query:

Code:
List projectedTuples =
   session.createCriteria(Foo.class)
     .add( ... your where clauses ... )
     .setProjection(Projections.distinct(
                             Projections.property("juridesk_navn") // or whatever
                          ) 
                         )
                        .list();


Since you're projecting a column AND using it in the WHERE clause, this error occurs.

What I did was add a prefix to each column name and set that as the alias , then I use a result transformer to return the alias as it should be:

Code:

String prefix = "alias_";
List projectedTuples =
   session.createCriteria(Foo.class)
     .add( ... your where clauses ... )
     .setProjection(Projections.distinct(
                             Projections.property("juridesk_navn",prefix + "juridesk_navn") // or whatever
                          ) 
                         )
                        .setResultTransformer(new PrefixedAliastoBeanResultTransformer())
                        .list();



for some reason, this tricks hibernate into NOT using the y0_ in the where clause... now i just mod'd the AliasToBeanResultTransformer's transformTuple method to trim off the alias:

Code:

public static final String ALIAS_PREFIX = "alias_";

public Object transformTuple(Object[] tuple, String[] aliases)
  {
    Object result;

    try
    {
      if (setters == null)
      {
        setters = new Setter[ aliases.length ];

        for (int i = 0; i < aliases.length; i++)
        {
          String alias = aliases[ i ];

          if (alias != null)
          {
            if (alias.startsWith(ALIAS_PREFIX))
            {
              alias = alias.substring(ALIAS_PREFIX.length());

              if (mLogger.isDebugEnabled())
              {
                mLogger.debug("transformTuple: getting property: " + alias);
              }
            }

            setters[ i ] = propertyAccessor.getSetter(resultClass, alias);
          }
        }
      }

      result = resultClass.newInstance();

      for (int i = 0; i < aliases.length; i++)
      {
        if (setters[ i ] != null)
        {
          setters[ i ].set(result, tuple[ i ], null);
        }
      }
    }
    catch (InstantiationException e)
    {
      throw new HibernateException("Could not instantiate resultclass: " +
        resultClass.getName());
    }
    catch (IllegalAccessException e)
    {
      throw new HibernateException("Could not instantiate resultclass: " +
        resultClass.getName());
    }

    return result;
  }


then i also created a static buildProjection method in this class that creates the projected columns w/ aliases:

Code:
  /**
   * Builds a projection for a list of properties.
   * @param properties The list of properties to project.
   * @return A projection.
   */
  public Projection buildProjection(String[] properties)
  {
    ProjectionList list = Projections.projectionList();
    int count = properties.length;

    for (int i = 0; i < count; ++i)
    {
      list.add(Projections.property(properties[ i ]),
        ALIAS_PREFIX + properties[ i ]);
    }

    return list;
  }


[/code]

i don't really like this, but it works.

good luck!


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 14, 2005 12:39 pm 
Newbie

Joined: Thu Apr 15, 2004 4:31 pm
Posts: 7
Is there a JIRA issue associated with this? We could really do with a syntax such as

Code:

session.createCriteria(Entity.class).setFetchMode("children", FetchMode.JOIN).listDistinct();



Having a distinct projection is not really that heplful as illustrated above.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 27, 2006 12:27 pm 
Newbie

Joined: Fri Jul 22, 2005 8:51 am
Posts: 3
Hi,
I was trying to use the AliasToBeanResultTransformer, and all the properties I set are being populated as nulls. I was wondering if there was anything I could do to correct it.

The code is:

criteria = session.createCriteria(MyClass.class);

criteria.setProjection(Projections.projectionList().add(
Projections.distinct(Projections.property("myClassCode")))
.add(Projections.property("myClassName")));

criteria.setResultTransformer(new AliasToBeanResultTransformer(MyClass.class));

when I do the following:

criteria.list();

I get an array of all MyClass objects where the properties are null. But the number of objects are the same as the number of rows in the sql result set.

Appreciate the help.

Thanks,

Sid


Top
 Profile  
 
 Post subject:
PostPosted: Fri Apr 21, 2006 5:39 am 
Newbie

Joined: Fri Apr 21, 2006 3:43 am
Posts: 2
Location: England
Two replies and a comment really:

To tc/shanonvl/twistah:

the "Invalid column name 'y10_'." ... error in my case was caused by trying to select a distinct column of type "TEXT", which against Sybase isn't allowed.

A Meaningless error message from Hibernate, as the alias referenced is not the column with the problem (grrr ...)

Found this out by taking the Hibernate debug trace and running that SQL against the database directly.

To sb606:

You will only get data in the properties you've added to the Projection (read Toqwaz's post again). In your case I would only expect the 'myClassName' field to contain any data in your resulting List.

I'll add my voice to utuba's comment that there MUST/SHOULD be a way of returning a Distinct Collection of Objects where the work is done on the database side rather than 'client' side (as ".setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)" does).


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 28 posts ]  Go to page 1, 2  Next

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.