Liferay custom-sql using the IN statement

Question

I am using Liferay 6.1, Tomcat and MySQL. I have a custom-sql clause for a list portlet. Custom-sql uses two parameters: the groupIds array and the result limit.

SELECT count(articleId) as count, ... FROM comments WHERE groupId IN (?) GROUP BY articleId ORDER BY count DESC LIMIT 0, ? 

My FinderImpl class has this method:

  public List<Comment> findByMostCommented(String groupIds, long maxItems) { Session session = null; session = openSession(); String sql = CustomSQLUtil.get(FIND_MOST_COMMENTS); SQLQuery query = session.createSQLQuery(sql); query.addEntity("Comment", CommentImpl.class); QueryPos queryPos = QueryPos.getInstance(query); queryPos.add(groupIds); queryPos.add(maxItems); List<Comment> queryResult = query.list(); return queryResult; } 

This returns 0 results. If I remove WHERE IN (), it will work.

Is IN a valid statement? If not, how do I search in different groups?

Answer 1

Perhaps hibernate is quoting your groupIds string (presumably it is in the form "1,2,3,4" , and when hibernate translates this value into sql, does it put quotation marks there?

You might want to try something like this (from Liferay itself):

 String sql = CustomSQLUtil.get(FIND_BY_C_C); sql = StringUtil.replace(sql, "[$GROUP_IDS$]", groupIds); 

And include ([$GROUP_IDS$]) instead of (?) In your SQL