how to use dynamicQueryCount

[Alain Dresse，修改在11 年前。]

Hi,

I am using dynamic queries for a search container, and have an issue extracting both the count and the results from the same dynamic query.

I do the following:

Create the dynamic query, and set the conditions

	DynamicQuery dynamicQuery = MBThreadLocalServiceUtil.dynamicQuery();
	Criterion criterion = RestrictionsFactoryUtil.in("categoryId", categoryIds);
	dynamicQuery.add(criterion);

If I then do

	total="<%= MBThreadLocalServiceUtil.dynamicQueryCount(dynamicQuery) %>"
	results="<%= MBThreadLocalServiceUtil.dynamicQuery(
		dynamicQuery, searchContainer.getStart(), searchContainer.getEnd()) %>"

I get an error on the second line, becaus Long cannot be converted to MBThread.

The only workaround I have found is to create two dynamicQueries, one for the count and one for the entries. Is there a way to reuse the same dynamic query for both ? Wouldn't it make sense to have dynamicQueryCount not modify the dynamicQuery it receives (cloning it before setting the projection ? don't know if this is possible...)

Best regards,
Alain

[Punam Shah，修改在10 年前。]

Please try this :

  total="<%= (int)MBThreadLocalServiceUtil.dynamicQueryCount(dynamicQuery) %>"
results="<%= MBThreadLocalServiceUtil.dynamicQuery(
    dynamicQuery, searchContainer.getStart(), searchContainer.getEnd()) %>"

Hope, this will help you, instead of creating two queries.

[Alain Dresse，修改在10 年前。]

Thanks for the answer,

Unfortunately, I have reviewed my code since, moving the queries to custom sql, so it is no longer that easy to test the changes you suggest...

Best regards,
Alain

[Tamas Toth，修改在8 年前。]

Hi,

I know it is an old thread, but I am facing similar problems, so it may worth to add here.

I tried to create a DynamicQuery based search container, using the following code. (LoginLog is a custom entity which I use to collect some user statistics, and I want to filter the records for a time range, ordered by the timestamp.)

Criterion filter = RestrictionsFactoryUtil.between("timestamp", dateFrom, dateTo);
DynamicQuery query = DynamicQueryFactoryUtil.forClass(LoginLog.class).add(filter);
query.addOrder(OrderFactoryUtil.asc("timestamp"));

logs = LoginLogLocalServiceUtil.dynamicQuery(query, searchContainer.getStart(), searchContainer.getEnd());
total = (int)LoginLogLocalServiceUtil.dynamicQueryCount(query2);

I had two issues:
1. Without the ordering, the first page was working fine, but if I navigated to other pages, the total became 0 (so dynamicQueryCount returned this value).
2. After adding the order clause, I got an exception saying "invalid ORDER BY expression". I figured out that it was thrown by the dynamicQueryCount method.

When I created a second query with the filter but without the order, and called dynamicQueryCount on this secind one, it returned the correct result, so my search container is working fine. But I don't think it is the most effective way of solving it.

[G R Rajesh Babu，修改在10 年前。]

Hi Alain,

Please let us know whether the total="<%= (int)MBThreadLocalServiceUtil.dynamicQueryCount(dynamicQuery) %>" is working fine or not.