Combination View Flat View Tree View
Threads [ Previous | Next ]
Rob Lindbloom
Join with DynamicQuery
December 19, 2008 2:08 PM
Answer

Rob Lindbloom

Rank: New Member

Posts: 15

Join Date: October 16, 2008

Recent Posts

I'm trying to do a SQL inner join using DynamicQuery. I can't find too much information on how to do this. I'm trying to join JournalArticle with Ratings or RatingStats. Has anyone implemented this using JournalArticle.findByDynamicQuery()?

Thanks, Rob
Jonas Yuan
RE: Join with DynamicQuery
December 19, 2008 2:51 PM
Answer

Jonas Yuan

Rank: Liferay Master

Posts: 993

Join Date: April 26, 2007

Recent Posts

Hi Rob,

Here is a real example -getting most popular articles TOP 10 - abstracted from 1st draft of Chapter 8: Building My Community - Liferay DEV BOOK.

public List<TagsAsset> getMostPopularArticles(String companyId, String groupId, String type, int limit) {
List<TagsAsset> results = Collections.synchronizedList(new ArrayList<TagsAsset>());
DynamicQuery dq0 = DynamicQueryFactoryUtil.forClass(JournalArticle.class, "journalarticle")
.setProjection(ProjectionFactoryUtil.property("resourcePrimKey"))
.add(PropertyFactoryUtil.forName("journalarticle.companyId").eqProperty("tagsasset.companyId"))
.add(PropertyFactoryUtil.forName("journalarticle.groupId").eqProperty("tagsasset.groupId"))
.add(PropertyFactoryUtil.forName("journalarticle.type").eq("article-content"));
DynamicQuery query = DynamicQueryFactoryUtil.forClass(TagsAsset.class, "tagsasset")
.add(PropertyFactoryUtil.forName("tagsasset.classPK").in(dq0))
.addOrder(OrderFactoryUtil.desc("tagsasset.viewCount"));
try{
List<Object> assets = TagsAssetLocalServiceUtil.dynamicQuery(query);
int index = 0;
for (Object obj: assets) {
TagsAsset asset = (TagsAsset)obj;
results.add(asset);
index ++;
if(index == limit) break;
}
}catch (Exception e){
_log.debug("Error: " + e.getMessage());
return results;
}
return results;
}

Enjoy!

Jonas Yuan
Liferay Book: Liferay Portal Enterprise Intranets
Rob Lindbloom
RE: Join with DynamicQuery
December 19, 2008 3:25 PM
Answer

Rob Lindbloom

Rank: New Member

Posts: 15

Join Date: October 16, 2008

Recent Posts

Jonus... thanks for your quick reply on a Friday no less. I'll give this a try!

Gratefully, Rob
Ruben Aguilera
RE: Join with DynamicQuery
July 10, 2009 8:21 PM
Answer

Ruben Aguilera

Rank: Junior Member

Posts: 47

Join Date: February 25, 2009

Recent Posts

Hi Jonas,

First, congratulations for your new book, I have it and I think that's fantastic.

I was thinking if you could give me an example about join three tables with a dynamicquery. Specifically, I want to know how I make a join with TagsAsset, TagsEntry and TagsAssets_TagsEntries tables using a dynamicquery like example in first comment but with these three tables.

I need that to finish an important portlet in my job. Thank you very much.
Ezequiel Turovetzky
RE: Join with DynamicQuery
January 29, 2013 3:56 PM
Answer

Ezequiel Turovetzky

Rank: New Member

Posts: 12

Join Date: July 4, 2012

Recent Posts

Thanks a lot for that snippet!