Express Complex Sub-Select With Dynamic Query

Question

How would you replicate this SQL ( Sub-select ) with an equivalent Liferay DynamicQuery expression within a ServiceImpl Class:

SELECT * FROM journalarticle
WHERE (urlTitle,version) IN
( SELECT 
    urlTitle,MAX(version) 
FROM journalarticle 
WHERE structureId = 'structure-id' AND companyId = 10150 AND groupId = 10170
GROUP BY urlTitle ) 
ORDER BY createDate DESC 
LIMIT 0,4

There doesnt seems direct way to express such complext sub-select using DynamicQuery, but you can do it with some modification,if it fits to you — Pankaj Kathiriya, Jun 18 '13 at 06:13

score 0 · Answer 1 · answered Jun 18 '13 at 06:27

As I could not write too long comment , I am pasting code here which would result in what you want, I havent compiled or run it.

DynamicQuery dynamicQuery=DynamicQueryFactoryUtil.forClass(JournalArticle.class);
        dynamicQuery.addOrder(OrderFactoryUtil.desc("createDate"));
        dynamicQuery.setLimit(0, 4);

        DynamicQuery subQuery=DynamicQueryFactoryUtil.forClass(JournalArticle.class);
        subQuery.setProjection(ProjectionFactoryUtil.projectionList().add(ProjectionFactoryUtil.property("_id")).add(ProjectionFactoryUtil.max("version")));
        subQuery.add(PropertyFactoryUtil.forName("structureId ").eq("structure-id"));
        subQuery.add(PropertyFactoryUtil.forName("companyId").eq("10150"));
        subQuery.add(PropertyFactoryUtil.forName("groupId").eq("10170"));
        List<Long> ids=new ArrayList<Long>();
        try {
            List<Object[]> list= JournalArticleLocalServiceUtil.dynamicQuery(subQuery);

            for(Object[] object:list){
                //0th field would be _id
                ids.add((Long)object[0]);
            }
        } catch (SystemException e) {
            // TODO Auto-generated catch block
        }

        dynamicQuery.add(PropertyFactoryUtil.forName("_id").in(ids.toArray()));

        try {
            List<JournalArticle> journalArticles=JournalArticleLocalServiceUtil.dynamicQuery(dynamicQuery);
        } catch (SystemException e) {
            // TODO Auto-generated catch block
        }

I hope this might be useful to you.

I forgot to mention that this is what I have done previously. However it uses a subquery which includes logic that makes an extra call to the db. I was hoping there was a way to write one optimal expression. — Olaseni, Jun 18 '13 at 10:41

Express Complex Sub-Select With Dynamic Query

1 Answers1