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 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 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?

Accepted Answer

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

Liferay custom-sql using IN operator

Solution

Problem