How to resolve activobject table name to make an sql query containing MAX function via findWithSql

[Ayoub Bhija]

I am working on a jira plugin and i have a problem.

I am trying to make an sql query with MAX(FIELD) and SUM() but the problem is that i tried to use them with find method which takes Query Object but the sql functions are ignored. So I tried with findWithSql but the problem is that i don't understand how i can resolve the activeObject Table name at runtime. Because the activeObjects prefixes are generated automatically.

Example :

Select NAME, SUM(TOTAL) as TOTAL order by NAME;

[Humashankar VJ]

Hi @Ayoub Bhija 

When working with the EntityManager in Jira, there are limitations to be aware of. Specifically, the EntityManager does not provide a direct method to retrieve the table name, such as getTableName().

Instead, table names are determined by the @Table annotation on the Active Object class or follow a naming convention, such as prefixing "AO_" followed by the class name.

To dynamically resolve table names, you can use reflection to access the @Table annotation or apply a fallback naming convention based on the class name.

Some key pointers:

• Get the table name dynamically from the @Table annotation.
• Construct the SQL query with SUM() and MAX().
• Execute the query using findWithSQL.

Hope this helps - Happy to help further!!
Thank you very much and have a great one!
Warm regards

[Ayoub Bhija]

Just to precise, i use ActiveObjects service and not Entity Manager.

The table names in the DB for an active object follow this pattern "AO_[generated_number]_TABLE_NAME". The problem is the generated number.

I don't understand how i can retrieve the table name from reflection on the @table annotation. 

[Humashankar VJ]

Hi @Ayoub Bhija 

Thank you for clarifying your use of Active Objects (AO).

• I understand that AO table names in the database follow a specific pattern, consisting of a prefix "AO_", a generated number derived from the MD5 hash of your plugin key, and the entity name.
• The generated number is taken from the last six characters of the hexadecimal value of the MD5 hash, and the entity name corresponds to your entity class name in uppercase. For instance, the table name for an entity class "MyObject" with a plugin key "com.atlassian.example.ao.myplugin" would be "AO_28BE2D_MY_OBJECT".

Besides all that Active Objects doesn't provide a direct method to retrieve the full table name, including the generated number, at runtime through reflection.

Hope this helps !!

[Ayoub Bhija]

Thanks, Guess i have to rethink my code to male my query less complex.