RE: Bug in Dynamic Query for Enterprise Edition 6 SP1 (range search)

[René Jurmann, modificado hace 13 años.]

Hi all,

I experienced a bug within the Enterprise Edition 6 SP1.
We have following scenario:

We search for users according their attributes within the custom fields. Therefore we have added one custom field "AccountID" to the user object (using Control Panel).
In order to search for users that match a range of account IDs we use following code:

String[] accountIds = ...;
ThemeDisplay themeDisplay = (ThemeDisplay) request.getAttribute(WebKeys.THEME_DISPLAY);
long companyId = themeDisplay.getCompanyId();
ExpandoColumn expColumn = ExpandoColumnLocalServiceUtil.getDefaultTableColumn(companyId, User.class.getName(), "AccountID");
Set<long> expValueUserIds = new HashSet<long>();
ClassLoader cl = PortalClassLoaderUtil.getClassLoader();
dynamicQuery = DynamicQueryFactoryUtil.forClass(ExpandoValue.class, cl);
dynamicQuery.add(RestrictionsFactoryUtil.eq("tableId", expColumn.getTableId()));
dynamicQuery.add(RestrictionsFactoryUtil.eq("columnId", expColumn.getColumnId()));
dynamicQuery.add(RestrictionsFactoryUtil.in("data", accountIds));
List<expandovalue> expValues = ExpandoValueLocalServiceUtil.getService().dynamicQuery(dynamicQuery);
if(expValues != null &amp;&amp; expValues.size() &gt; 0){
	for(ExpandoValue expValue : expValues){
		expValueUserIds.add(new Long(expValue.getClassPK()));
	}
}
</expandovalue></long></long>

This works for the CE portal version 6.0.5 (some of our development environments are running on this version).
But when we execute this code on EE portal version 6 SP1 no result is found although some should be found.
In order to get this search to work with EE version I need to rewrite the whole coding to:

String[] accountIds = ...;
ThemeDisplay themeDisplay = (ThemeDisplay) request.getAttribute(WebKeys.THEME_DISPLAY);
long companyId = themeDisplay.getCompanyId();
ExpandoColumn expColumn = ExpandoColumnLocalServiceUtil.getDefaultTableColumn(companyId, User.class.getName(), "AccountID");
Set<long> expValueUserIds = new HashSet<long>();
for(int count = 0; count &lt; accountIds.length; count++){
	ClassLoader cl = PortalClassLoaderUtil.getClassLoader();
	dynamicQuery = DynamicQueryFactoryUtil.forClass(ExpandoValue.class, cl);
	dynamicQuery.add(RestrictionsFactoryUtil.eq("tableId", expColumn.getTableId()));
	dynamicQuery.add(RestrictionsFactoryUtil.eq("columnId", expColumn.getColumnId()));
	dynamicQuery.add(RestrictionsFactoryUtil.like("data", accountIds[count]));
	List<expandovalue> expValues = ExpandoValueLocalServiceUtil.getService().dynamicQuery(dynamicQuery);
	if(expValues != null &amp;&amp; expValues.size() &gt; 0){
		for(ExpandoValue expValue : expValues){
			expValueUserIds.add(new Long(expValue.getClassPK()));
		}
	}
}
</expandovalue></long></long>

Can anyone tell me if searching for a range:

dynamicQuery.add(RestrictionsFactoryUtil.in("data", accountIds));

works in EE version 6 SP1 and how I should configure it?

regards
René Jurmann

[Ray Augé, modificado hace 13 años.]

Can you try:

PropertyFactoryUtil.forName("data").in(accountIds)

//in placed of

RestrictionsFactoryUtil.in("data", accountIds)

[René Jurmann, modificado hace 13 años.]

Hi Ray,

that still does not work.
Here as well - when I execute

PropertyFactoryUtil.forName("data").like(accountId)

in a loop all corresponding users are found. But as soon as I want to use the range search

PropertyFactoryUtil.forName("data").in(accountIds)

no result is returned

[Vincent Vanghelle, modificado hace 13 años.]

Hi.

It looks like i have the same problem..

On 6.0.5 it was OK, then I switched to a Liferay EE SP1 version..Here is the code sample :

DynamicQuery queryRegion = DynamicQueryFactoryUtil.forClass(ExpandoValue.class, cl);
queryRegion.setProjection(ProjectionFactoryUtil.property("classPK"));
queryRegion.add(RestrictionsFactoryUtil.eq("tableId",columnRegions.getTableId()));
queryRegion.add(RestrictionsFactoryUtil.eq("columnId",columnRegions.getColumnId()));
queryRegion.add(RestrictionsFactoryUtil.in("data", codesRegion));
/*codesRegion = array of string, also tried with the propertyFactoryUtil.forname("data").in() method */

Doesn't work, but this one works :

DynamicQuery queryRegion = DynamicQueryFactoryUtil.forClass(ExpandoValue.class, cl);
queryRegion.setProjection(ProjectionFactoryUtil.property("classPK"));
queryRegion.add(RestrictionsFactoryUtil.eq("tableId",columnRegions.getTableId()));
queryRegion.add(RestrictionsFactoryUtil.eq("columnId",columnRegions.getColumnId()));
queryRegion.add(RestrictionsFactoryUtil.eq("data",codesRegion[0]));

[Vincent Vanghelle, modificado hace 13 años.]

Okey, I found this while debugging :


In class com.liferay.portal.dao.orm.hibernate.StringType :

public Object getPropertyValue(Object component, int property) {
	return null;
}

With this method, a "in" restrictions construct a prepared statement with null values...

[Aritz Galdos, modificado hace 13 años.]

Hi,

Has it been reported as a bug?

regards!

[Vincent Vanghelle, modificado hace 13 años.]

Hi Aritz,

I haven't done anything yet, no much time (and never reported a bug on Liferay)

Where can we report this bug?

[René Jurmann, modificado hace 13 años.]

Hi Aritz,

no, I haven't done anything like this up to now.
(Would need second logon to issues.liferay.com, correct?)

regards
René

[Aritz Galdos, modificado hace 13 años.]

HI,

I can not report for Sp1 with my user.

I contacted Juan (Liferay Staff) and alerted him about the possible bug.

Hopefully (if it is a bug) will be solved soon.

Regards!!

[Ray Augé, modificado hace 13 años.]

I just tested again with 6.0.6 CE and a simple hook, and it worked exactly as expected.

The hook is attached.

[Aritz Galdos, modificado hace 13 años.]

Hi Ray:

I think it affects 6.0.11 (SP1) and trunk.

AS vincent says here, there is a suspicious "return null"

[Jack Daniels, modificado hace 13 años.]

Has this been verified yet by any Liferay staff member in EE 6.0 SP1 as originally reported?

Thanks.

[Aritz Galdos, modificado hace 13 años.]

Hi,

Seems to be a bug and I´ve been told Liferay guys are workin' on it. Surely will be solved in short.

[Jack Daniels, modificado hace 13 años.]

Thanks for the quick response Aritz, I appreciate it.

Cheers.

[Igor Spasić, modificado hace 13 años.]

We filed the issue: http://issues.liferay.com/browse/LPS-16064

The core problem is in the StringType:

public Object getPropertyValue(Object component, int property) {
    return null;
}

Hibernate v3.6.x uses this method when populating IN section while generating SQL. This issue was introduced when we migrated to Hibernate 3.6.0. Fix is committed to svn. For more details, please check the LPS.

[parth barot, modificado hace 13 años.]

Then what is the solution for this? Do we have any patch for this? Please reply, i need this urgently..stuck at IN operator.

Thanks

[parth barot, modificado hace 13 años.]

I am trying using the latest StringType in ext plugin...

http://svn.liferay.com/browse/portal/trunk/portal-impl/src/com/liferay/portal/dao/orm/hibernate/StringType.java?r1=69338&r2=76173&u=3

Thanks

[Aritz Galdos, modificado hace 13 años.]

Hi

If you are enterprise user, you should report the incident in the tool Liferay offers to its clients.

Our client, which is enterprise client of Liferay, already got the patch for this issue from liferay.

[Jack Daniels, modificado hace 13 años.]

Too bad they only send the patch to customers that ask for it as opposed to publishing it through the customer or partner portal.

I'll have to ask for it for our EE customer as well. Thanks for mentioning this.