0

I have a two part question.

  1. I am using Criteria and Projections to retrieve data. I am mapping the resultant data set to a DTO. I had to use SqlProjection to get some data, but it seems the sql projection does not accept the Entity name, but its table name in the DB.

ie. I cannot use something like the following

.add(Projections.sqlProjection("(select count(c.someData) 
     from EntityName c where c.id= " + 
     passedID + ") as someDataField",  
     new String[] { "someDataField" }, 
     new Type[] { IntegerType.INSTANCE }))

but if I replace with the column names and tablename it works. Like so,

.add(Projections.sqlProjection("(select count(c.SOME_DATA_COLUMN) 
         from TABLE_NAME c where c.ID_COLUMN= " + 
         passedID + ") as someDataField",  
         new String[] { "someDataField" }, 
         new Type[] { IntegerType.INSTANCE }))

Why is this happening?

  1. Also, I have an entity Entity which extends a class 'Auditable' which has a field 'createdBy'. Auditable includes fields which are common for almost all the tables like 'status of record', 'created by', 'date created' etc.

I am trying to retrieve the data from Users entity which corresponds to USERS_TABLE. I have First_Name and Last_Name columns and User_ID, all Varchar, in the table and fields are in entity.

Created_By column in every table refers to User_ID in USERS_TABLE.

Now, I want to retrieve the first name and last name with the createdBy field.

I wrote a sqlProjection along with other projections. It looks as follows.

.add(Projections.sqlProjection(
     "(select concat(user.First_Name, ' ', user.Last_Name) from USERS_TABLE user " 
                    + "where user.User_ID = {alias}.mappedParentEntity.createdBy) as createdBy",  
                     new String[] { "createdBy" }, 
                     new Type[] { StringType.INSTANCE }))

{alias} refers to the alias of root entity.

I get an error which says

Unknown column 'this_.mappedParentEntity.createdBy' in 'where clause'

What could be the solution for this one?

evyavan
  • 35
  • 8
  • Try to join first to the parent using the alias and then use in the where: parentAlias.createdBy instead {alias}.mappedParentEntity.createdBy – Najera Jan 03 '15 at 08:15
  • @Najera Are you suggesting I create an alias with 'createAlias' and Left Join and then use that alias name in sqlProjection? Will try and report back. But IDK if that's possible. – evyavan Jan 03 '15 at 08:47

0 Answers0