Dynamic Query help

[Sandeep Nair，修改在14 年前。]

Hi everyone,

I need a help in Dynamic Query or with anything that can solve this. I have been using jdbc for writing complex queries. I want to know if this is possible using DynamicQuery or any api in liferay

I have following three tables

OrgType
orgtypeid
orgtypename
isActive

Location
locationid
location_name
isActive

CustomOrg
orgId
orgtypeid
locationId
orgname

I want to find all the organizations whose location and orgType are active. Is it possible using DynamicQuery or any liferayapi

Regards,
Sandeep

[jelmer kuperus，修改在14 年前。]

Something like this should work :

DynamicQuery q = DynamicQueryFactoryUtil.forClass(CustomOrg.class, PortalClassLoaderUtil.getClassLoader())
    .add(PropertyFactoryUtil.forName("orgtypeid")
            .in(DynamicQueryFactoryUtil.forClass(OrgType.class, PortalClassLoaderUtil.getClassLoader())
                    .add(PropertyFactoryUtil.forName("isActive").eq(true))
                    .setProjection(ProjectionFactoryUtil.property("orgtypeid"))
            )
    )
    .add(PropertyFactoryUtil.forName("locationid")
            .in(DynamicQueryFactoryUtil.forClass(Location.class, PortalClassLoaderUtil.getClassLoader())
                    .add(PropertyFactoryUtil.forName("isActive").eq(true))
                    .setProjection(ProjectionFactoryUtil.property("locationid"))
            )
    )

[Sandeep Nair，修改在14 年前。]

Thanks Jelmer.

[Antonio Musarra，修改在12 年前。]

Hi Jelmer,
How would you do the query SELECT * FROM AnnouncementsEntry WHERE entryId NOT IN (SELECT entryId FROM AnnouncementsFlag WHERE entryId = AnnouncementsEntry.entryId and value=-1) in Dynamic SQL Query?

I was having trouble replicate the condition:

entryId = AnnouncementsEntry.entryId

Thx,
Antonio.

[Antonio Musarra，修改在12 年前。]

I solved it this way:

DynamicQuery announcementsEntryDynamicQuery = DynamicQueryFactoryUtil.forClass(AnnouncementsEntry.class, "AnnouncementsEntry");

announcementsEntryDynamicQuery.addOrder(OrderFactoryUtil.desc("type"));
announcementsEntryDynamicQuery.addOrder(OrderFactoryUtil.desc("priority"));
announcementsEntryDynamicQuery.add(RestrictionsFactoryUtil.eq("userId", user.getUserId()));
announcementsEntryDynamicQuery.add(PropertyFactoryUtil.forName("entryId").notIn(
DynamicQueryFactoryUtil.forClass(AnnouncementsFlag.class,"AnnouncementsFlag")
.add(PropertyFactoryUtil.forName("value").eq(flagValue))
.add(PropertyFactoryUtil.forName("userId").eq(user.getUserId()))
.add(PropertyFactoryUtil.forName("AnnouncementsFlag.entryId").eqProperty("AnnouncementsEntry.entryId"))
.setProjection(ProjectionFactoryUtil.property("entryId"))
));

Thx,
Antonio.