Order by another table with Liferay's DynamicQuery

Question

I have a problem while I'm making a Dynamic Query in Liferay 6. I'm trying to make a query to order JournalArticles based on their view count. The view count is specified in another table (AssetEntry).

I'm stuck with this:

DynamicQuery query = DynamicQueryFactoryUtil.forClass(
JournalArticle.class, "articleParent", PortalClassLoaderUtil.getClassLoader());

//adding criterions
query.add(...);

DynamicQuery dq0 = DynamicQueryFactoryUtil.forClass(AssetEntry.class, "asset", 
PortalClassLoaderUtil.getClassLoader())
.setProjection(ProjectionFactoryUtil.property("asset.classPK"))
.add(PropertyFactoryUtil.forName("asset.companyId")
.eqProperty("articleParent.companyId"))
.add(PropertyFactoryUtil.forName("asset.groupId")
.eqProperty("articleParent.groupId"));

query.add(PropertyFactoryUtil.forName("articleParent.resourcePrimKey").in(dq0))
            .addOrder(OrderFactoryUtil.desc("asset.viewCount"));

With this I get an error message saying: could not resolve property: asset of: com.liferay.portlet.journal.model.impl.JournalArticleImpl.

If I remove the addOrder-call, this error disappears. How should I add the order statement so the main query is aware of asset.viewCount?

score 1 · Answer 1 · answered Jul 19 '12 at 13:44

1

AssetEntryQuery assetEntryQuery = new AssetEntryQuery();
assetEntryQuery.setClassName(JournalArticle.class.getName());
assetEntryQuery.setXXX //adding criterions
assetEntryQuery.setOrderByCol1("viewCount");
List<AssetEntry> assetEntries = AssetEntryServiceUtil.getEntries(assetEntryQuery);

answered Jul 19 '12 at 13:44

Mark

17,887
13
66
93

I don't think this answers the question: You missed the first `query` with `//adding criterions query.add(...);`, your way returns the `AssetEntry` which the question is already returning through the second query `dq0`. How do you plan to return the `JournalArticle`s with additional criterions specified on `JournalArticle` and not on `AssetEntry`? – Prakash K Jul 20 '12 at 06:56
may have "n" number of criterions, like username, title, urltitle, description, content, version etc, which might not be there in `AssetQuery`. Though most of the criterion like title, description, username is already there in `AssetQuery`. – Prakash K Jul 20 '12 at 09:37

score 0 · Answer 2 · answered Jul 19 '12 at 12:38

0

I am afraid that there is no direct way to do this with the DynamicQuery API.

I think you would need to use Service builder Finders i.e. Custom Query with Service builder.

answered Jul 19 '12 at 12:38

Prakash K

11,669
6
51
109

score 0 · Answer 3 · answered Jul 20 '12 at 16:19

You can't use dynamic query because there is no direct reference from JournalArticle entity to AssetEntry entity.

One possibility is to retrieve ordered ids of articles from the AssetEntry table (basically you dq0), then do another query and do the sorting programatically (you have the ids ordered).

Finally I think that this line

query.add(PropertyFactoryUtil.forName("articleParent.resourcePrimKey").in(dq0))

doesn't do what you think it does. resoucePrimKey is reference to resource table for permissions. You need to use id column.

Order by another table with Liferay's DynamicQuery

3 Answers3