Implementing "WHERE (col1, col2, col3) in (subselect)" as DynamicQuery

[Dave Kliczbor, geändert vor 8 Jahren.]

hello all,

Basically, I need to translate the following SQL query to a DynamicQuery, to be used in Liferay 6.2. It selects all SocialActivities on WikiPage objects made by a single user -- but for each distinct WikiPage entity, it only selects the newest activity.

select *
from SocialActivity 
where (classPK, classNameId, createDate) in 
(	select classPK, classNameId, max(createDate) 
	from SocialActivity 
	where userId=10199 and classNameId=10016
	group by classPK, classNameId
);

Currently, my code looks like this:

ProjectionList subQueryProjectionList = ProjectionFactoryUtil.projectionList()
	.add(ProjectionFactoryUtil.groupProperty("classPK"))
	.add(ProjectionFactoryUtil.groupProperty("classNameId"))
	.add(ProjectionFactoryUtil.max("createDate"));

DynamicQuery subQuery = SocialActivityLocalServiceUtil.dynamicQuery()
	.add(PropertyFactoryUtil.forName("userId").eq(user.getUserId()))
	.add(PropertyFactoryUtil.forName("classNameId").eq(PortalUtil.getClassNameId(com.liferay.portlet.wiki.model.WikiPage.class.getName())))
	.setProjection(subQueryProjectionList);

DynamicQuery query = SocialActivityLocalServiceUtil.dynamicQuery()
	.add(PropertyFactoryUtil.forName("classPK, classNameId, createDate").in(subQuery))
	.addOrder(OrderFactoryUtil.desc("createDate"));

List<socialactivity> activities = SocialActivityLocalServiceUtil.dynamicQuery(query, 0, max);
</socialactivity>

This code has one obvious error in line 12: PropertyFactoryUtil.forName() only accepts the name of one property. My line of thought always ends at "How do I create a list of properties on which I can call .in(subQuery)"? I did not find anything pointing in the right direction in the package com.liferay.portal.kernel.dao.orm... but maybe I'm just blind...

It boils down to the question: How can I translate the SQL construct WHERE (col1, col2, ...) IN (SELECT ...) into a dynamic query?

[David H Nebinger, geändert vor 8 Jahren.]

That's not even standard sql-92, is it?

[Dave Kliczbor, geändert vor 8 Jahren.]

Hey David,

I think it is valid SQL-92 -- if I read the SQL-92 standard document's BNFs correctly.

On page 212, the IN predicate is defined as:

         <in predicate> ::=
              <row value constructor>
                [ NOT ] IN <in predicate value>

         <in predicate value> ::=
                <left paren> <in value list> <right paren>

         <in value list> ::=
              <value expression> { <comma> <value expression> }...
<br>with the &lt;row value constructor&gt; being defined on page 173 as:<br><pre><code>         <row value constructor> ::=
                <row value constructor element>
              | <left paren> <row value constructor list> <right paren>
              | <row subquery>

         <row value constructor list> ::=
              <row value constructor element>
                  [ { <comma> <row value constructor element> }... ]

         <row value constructor element> ::=
                <value expression>
              | <null specification>
              | <default specification>
</default></null></value></row></row></comma></row></row></row></right></row></left></row></row></code></pre><br><br>So, I need the construct "&lt;left paren&gt; &lt;row value constructor list&gt; &lt;right paren&gt; IN &lt;table subquery&gt;", which looks perfectly valid to me according to these BNFs.<br><br>EDIT: Hibernate seems to support it, if <a href="http://docs.jboss.org/hibernate/orm/3.5/reference/en/html/queryhql.html#queryhql-tuple">this section about row value constructor syntax</a> is any indication.</value></comma></value></in></right></in></left><table subquery>
              | </table></in></in></row></in>

[David H Nebinger, geändert vor 8 Jahren.]

I haven't seen that kind of syntax used in DQ before. You might find it easier to try a custom query instead?

[Dave Kliczbor, geändert vor 8 Jahren.]

Not really, as I read somewhere that custom queries are bound to the DB engine and I cannot foresee which DB engines will be used in development and production. It may need to support at least HSQL, MySQL/MariaDB, Postgres and Oracle, and I don't have all of them at hand for testing.

A better way might be to individually check the three fields returned by the subquery against the corresponding table columns, but at the moment, I'm getting lost in projections while maintaining only one query/subquery execution call...

[David H Nebinger, geändert vor 8 Jahren.]

Custom queries can be built using HQL, not SQL, so they can rely on Hibernate to transform to correct low-level syntax.

One example: https://www.liferay.com/community/forums/-/message_boards/message/2078207
Another: http://proliferay.com/liferay-custom-sql-example/

[Dave Kliczbor, geändert vor 8 Jahren.]

Ah, that's a new information. Thanks David, I'll look into it.

[David H Nebinger, geändert vor 8 Jahren.]

Yeah, if you can do it in HQL that might give you the generic format that Hibernate will be able to transform in to native SQL...

[Punam Shah, geändert vor 8 Jahren.]

Kindly Refer : https://www.liferay.com/community/forums/-/message_boards/message/13173369, will get more clarity.

[Dave Kliczbor, geändert vor 8 Jahren.]

Sorry, I don't see any mention of Dynamic Queries there. Additionally, the SQL used there only refers to one column identifier in front of the IN keyword (which I know how to do in Dynamic Queries), but not two or more (which is my whole question).