Can't use "group by" in Confluence Active Objects

Question

I'm developing Confluence plugin that it uses AO for storing data in db.

I have an Entity in AO(let it be MY_ENTITY and table in database has the same name). Suppose it has such fields: ID; a; b; and c. And I want to get count of all entities that is unique by field 'b'. How sql would looks like:

SELECT COUNT(*) FROM (SELECT b FROM MY_ENTITY GROUP BY b) as INNER;

I want implement the same query using Active Objects libarary. What I've done:

public int countUniqueMyEntitiesByB() {
   Query query = Query
      .select(b) /* if it change to .select() in MySQL it works :) */
      .group(b);

MY_ENTITY[] groups = ao.find(MY_ENTITY.class, query);

return groups != null ? groups.length : 0;
}

But I facesed with ugly bug, for example for H2 database:

com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:136)
caused by: org.h2.jdbc.JdbcSQLException: Column "ID" must be in the GROUP BY list; SQL statement:
SELECT b,ID,a,c FROM MY_ENTITY GROUP BY b [90016-176]

As you can see it select all fields even I specified select only one field. If I add field "ID" to the group list query really works but fetch not what I need.  The question is if it's possible to implement such query using AO and if yes how?

ΞΔ · Answer

Hello,

I am currently improving this future and I noticed that you need to have defined Preload annotation with non default value for your entity for 'group by' to work. I don't know why It was set this way but I will try to release new version of this plugin soon to fix it.

KUMAI Ryosuke[Ricksoft] · Answer

Has anyone solved this problem? I am troubled with the same problem.

I did not set @Preload at my entity and new Query("") results in a compile error.

I think GROUP BY query using AO does not work.

Sahal Saad · Answer

Hi,

Did you set @Preload at your entity ?

I think you can try use new Query("").