Liferay custom-sql using IN operator

Question

I am using Liferay 6.1, Tomcat and MySQL. I have a custom-sql sentence for a list portlet. The custom-sql uses two parameters: an array of groupIds and a 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 the WHERE IN(), it works.

Is IN a valid operator? If not, how can search within different groups?

Answer 1

Perhaps hibernate is quoting your string of groupIds (presumably it is in the form of "1,2,3,4" and when hibernate translates this to sql it is putting quotes around it for you?

You may 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$]) in place of the (?) in your SQL