How to do a Subquery / Join with Liferay ???

aquahellishΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 6 μήνες)

210 εμφανίσεις


How to do a Subquery / Join with Liferay ???

November 13, 2010 4:04 AM

amrk breitner

Rank: New Member

Posts: 7

Join Date: November 13, 2010

Recent Posts

Hello everybody, I have the task to retrieve all Users with certain roles, certain online status, names etc ... and this for
> 1
0.000 Users.


So I wanted to create a dynamic query handling this.


This is my code:

DetachedCriteria requestCriteria = DetachedCriteria.forClass(













User.class, "user");








DynamicQuery dynamicQuery = new DynamicQueryImpl(requestCriteri
a);









DetachedCriteria subCriteria =
DetachedCriteria.forClass(UserGroupRole.class,"group");














subCriteria.add(Restrictions.eqProperty("user.userId", "group.userId"));








requestCriteria.add(Subqueries.exists(subCriteria));








try {










return UserLocalServiceUtil.dynamicQuery(dynamicQuery);







} catch (SystemException e) {










// TODO Auto
-
generated catch block










e.printStackTrace();










return new ArrayList<Object>();







}




B
ut it just doesn´t work
-

I always get an Exception like this :

Caused by: org.hibernate.MappingException: Unknown entity:
com.liferay.portal.model.UserGroupRole





at
org.hibernate.impl.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:580)





at
org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:69)





at
org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryT
ranslator.java:357)





at
org.hibernate.loader.criteria.CriteriaJoinWalke
r.<init>(CriteriaJoinWalker.java:113)





at
org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)





at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:91)





at org.hibernate.impl.SessionImpl.list(S
essionImpl.java:1577)





at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)





at
com.liferay.portal.dao.orm.hibernate.DynamicQueryImpl.list(DynamicQueryImpl.java:95)





at
com.liferay.portal.dao.orm.hibernate.DynamicQueryImpl.list(DynamicQu
eryImpl.java:91)





at
com.liferay.portal.service.persistence.UserPersistenceImpl.findWithDynamicQuery(UserPe
rsistenceImpl.java:2103)




Who can tell me how to do a Subquery or a Join with Liferay ??


Flag


RE: How to do a
Subquery / Join with Liferay

November 13, 2010 4:07 AM

jelmer kuperus

Rank: Liferay Legend

Posts: 1094

Join Date: March 10, 2010

Recent Posts

Try


DynamicQuery dynamicQuery =


DynamicQueryFactoryUtil.forClass(requestCriteria,
PortalClassLoaderUtil.getClassLoader())



instead of


DynamicQuery dynamicQuery = new DynamicQueryImpl(requestCriteria);


Flag


RE: How to do a Subquery / Join with Liferay

November 13, 2010 7:58 AM

amrk breitner

Rank: New Member

Posts: 7

Join Date: November 13, 2010

Recent Posts

I couldn´t find the methods signature

as you posted it but I´ve tried the following (without success):

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",
PortalClassLoaderUtil.getClassLoader());

DetachedCriteria subCriteria = DetachedCriteria.forClass(UserGroupRo
le.class,
"group");


subCriteria.add(Restrictions.eqProperty("user.userId", "group.userId"));

dynamicQuery.add(new CriterionImpl(Subqueries.exists(subCriteria)));


try {




return UserLocalServiceUtil.dynamicQuery(dynamicQuery);

} catch (SystemException e)

{

// TODO Auto
-
generated catch block

e.printStackTrace();

return new ArrayList<Object>();

}




Did I do it right ? Or is there something wrong with the detached criteria ?


Flag


RE: How to do a Subquery / Join with Liferay

November 14, 2010 12:00 AM

amrk breitner

Rank: New Member

Posts: 7

Join Date: November 13, 2010

Recent Posts

Ok, this is strange.


This works:


DynamicQuery dq1 = DynamicQueryFactoryUtil.forClass(User.class, "wacka",
PortalClassLoaderUtil.getClassLoader())




.add(PropertyFactoryUtil.forName("userId").in(u
ids))




.setProjection(ProjectionFactoryUtil.property("screenName"));




This doesn´t work :


DynamicQuery dq1 = DynamicQueryFactoryUtil.forClass(





UserGroupRole.class, "test",





PortalClassLoaderUtil.getClassLoader()).setProjection(





ProjectionFa
ctoryUtil.property("userId"));




And it doesn´t work, because it can´t access property "userId" of UserGroupRoleImpl.


Now why is that ? The database table has the property, the class has the property ... what´s wrong ?



Here is the Exception:


Caused
by: org.hibernate.QueryException: could not resolve property: userId of:
com.liferay.portal.model.impl.UserGroupRoleImpl





at
org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPrope
rtyMapping.java:67)





at
org.hibernate.p
ersister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.
java:61)





at
org.hibernate.persister.entity.AbstractEntityPersister.getSubclassPropertyTableNumber(
AbstractEntityPersister.java:1402)





at
org.hibernate.persister.entity.BasicEntit
yPropertyMapping.toColumns(BasicEntityPropert
yMapping.java:54)





at
org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersist
er.java:1377)





at
org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryT
ranslat
or.java:457)





at
org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(Criter
iaQueryTranslator.java:417)





at org.hibernate.criterion.SimpleExpression.toSqlString(SimpleExpression.java:68)





at
org.hibernate.loader.c
riteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryT
ranslator.java:357)





at
org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:113)





at
org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalke
r.java:82)





at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:91)





at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1577)





at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)





at
com.liferay.portal.dao
.orm.hibernate.DynamicQueryImpl.list(DynamicQueryImpl.java:95)





at
com.liferay.portal.dao.orm.hibernate.DynamicQueryImpl.list(DynamicQueryImpl.java:91)





at
com.liferay.portal.service.persistence.UserGroupRolePersistenceImpl.findWithDynamicQue
ry(UserG
roupRolePersistenceImpl.java:1501)


Flag


RE: How to do a Subquery / Join with Liferay

November 14, 2010 3:40 AM

jelmer kuperus

Rank:
Liferay Legend

Posts: 1094

Join Date: March 10, 2010

Recent Posts

The problem is that while the UserGroupRoleModel interface has the attrib
ute and the table has the column,
hibernate maps it to a composite key and knows only of that key


Eg. look at the UserGroupRoleImpl definition in


http://svn.liferay.com/repos/public/portal/tags/6.0.5/portal
-
impl/src/META
-
INF/portal
-
hbm.xml


*When prompte
d for a password enter username guest


So the correct way to query would be

ProjectionFactoryUtil.property("primaryKey.userId")


Flag


RE: How to do a Subquery / Join with Liferay

November 14, 2010 4:11 AM

jelmer kuperus

Rank: Liferay Legend

Posts: 1094

Join Date: March 10, 2010

Recent Posts

Here's one way to do what you want


DynamicQuery q = DynamicQueryFactoryUtil.forClass(User.class,
PortalClassLoaderUtil.getClassLoader())





.add(PropertyFactoryUtil.forName("userId")










.in(DynamicQueryFactoryUtil.forClass(UserGroupRole.class,
PortalClassLoaderUtil.getClassLoader())















.add(PropertyFactoryUtil.forName("prima
ryKey.roleId").eq(roleId))















.setProjection(ProjectionFactoryUtil.property("primaryKey.userId"))










)





);


Flag


RE: How to do a Subquery / Join with Liferay

November 14, 2010 9:46 AM

amrk breitner

Rank: New Member

Posts: 7

Join Date: November 13, 2010

Recent Posts

HOORAY



Thank you very much




One last question ... do you know a way to do exactly this with a join ?


Flag


RE: How to do a Subquery / Join with Liferay

November 14, 2010 10:38 AM

jelmer kuperus

Rank: Liferay Legend

Posts: 1094

Join Date: March 10, 2010

Recent Posts

I don't think you can with the criteria api, the normal way to do joins in hibernate is via association mapping, and
liferay doe
s not map associations


You might be able to do it via SQLQuery's. Though you probably won't gain much, the database will rewrite your
query to an optimal form anyway


Flag


RE: How to do a Subquery / Join with Liferay

November 14, 2010 12:50 PM

amrk breitner

Rank: New Member

Posts: 7

Join Date: November 13, 2010

Recent Posts

Oh, ok.


We have the problem that Oracle only allows 1000 elements in an "in" clause and that´s why I wanted to use a join
over a subquery.


But anyway
-

thanks a lot !!!


Flag


RE: How to do a Subquery / Join with Liferay

November 15, 2010 2:49 AM

amrk breitner

Rank: New Member

Posts: 7

Join Date: November 13, 2010

Recent Posts

Oracle is no problem ... the queries get optimized and using a subquery with more than 1000 resulting ids works



Flag


RE: How to do a Subquery / Join with Liferay

November 15, 2010 8:11 AM

jelmer kuperus

Rank: Liferay Legend

Posts: 1094

Join Date: March 10, 2010

Recent Posts

Oracle only has a limit on the number of arguments in a static in clause. The statement i posted uses a subquery, so
as you a
lready found out it's not an issue