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

[amrk breitner, modifié il y a 13 années.]

Hello everybody, I have the task to retrieve all Users with certain roles, certain online status, names etc ... and this for > 10.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(requestCriteria);


		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>();
		}<br><br><br>But it just doesn´t work - I always get an Exception like this :<br><br><pre><code>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(CriteriaQueryTranslator.java:357)
    at org.hibernate.loader.criteria.CriteriaJoinWalker.<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(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.UserPersistenceImpl.findWithDynamicQuery(UserPersistenceImpl.java:2103)</init></init></init></code></pre><br><br><br><strong>Who can tell me how to do a Subquery or a Join with Liferay ??</strong></object>

[jelmer kuperus, modifié il y a 13 années.]

Try

DynamicQuery dynamicQuery =  DynamicQueryFactoryUtil.forClass(requestCriteria, PortalClassLoaderUtil.getClassLoader())

instead of

DynamicQuery dynamicQuery = new DynamicQueryImpl(requestCriteria);

[amrk breitner, modifié il y a 13 années.]

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(UserGroupRole.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>();
}<br><br><br>Did I do it right ? Or is there something wrong with the detached criteria ?</object>

[amrk breitner, modifié il y a 13 années.]

Ok, this is strange.

This works:

DynamicQuery dq1 = DynamicQueryFactoryUtil.forClass(User.class, "wacka", PortalClassLoaderUtil.getClassLoader())
   .add(PropertyFactoryUtil.forName("userId").in(uids))
   .setProjection(ProjectionFactoryUtil.property("screenName"));

This doesn´t work :

DynamicQuery dq1 = DynamicQueryFactoryUtil.forClass(
	UserGroupRole.class, "test",
	PortalClassLoaderUtil.getClassLoader()).setProjection(
	ProjectionFactoryUtil.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(AbstractPropertyMapping.java:67)
    at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:61)
    at org.hibernate.persister.entity.AbstractEntityPersister.getSubclassPropertyTableNumber(AbstractEntityPersister.java:1402)
    at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:54)
    at org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1377)
    at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:457)
    at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(CriteriaQueryTranslator.java:417)
    at org.hibernate.criterion.SimpleExpression.toSqlString(SimpleExpression.java:68)
    at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:357)
    at org.hibernate.loader.criteria.CriteriaJoinWalker.<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(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.findWithDynamicQuery(UserGroupRolePersistenceImpl.java:1501)</init></init></init>

[jelmer kuperus, modifié il y a 13 années.]

The problem is that while the UserGroupRoleModel interface has the attribute 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 prompted for a password enter username guest

So the correct way to query would be

ProjectionFactoryUtil.property("primaryKey.userId")

[jelmer kuperus, modifié il y a 13 années.]

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("primaryKey.roleId").eq(roleId))
                    .setProjection(ProjectionFactoryUtil.property("primaryKey.userId"))
            )
    );

[amrk breitner, modifié il y a 13 années.]

HOORAY

Thank you very much

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

[jelmer kuperus, modifié il y a 13 années.]

I don't think you can with the criteria api, the normal way to do joins in hibernate is via association mapping, and liferay does 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

[amrk breitner, modifié il y a 13 années.]

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

[amrk breitner, modifié il y a 13 années.]

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

[jelmer kuperus, modifié il y a 13 années.]

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 already found out it's not an issue