How to create subquery in active object

Jira Development Jira Server
,
1

For example I have query on SQL:

SELECT length
FROM (
  SELECT length, MIN(title) title
  FROM film
  GROUP BY length
) t
ORDER BY title

I want to create the same for Jira’s Active object:

Query query = Query.select()...............
ao.find(ENTITY_CLASS, query)

Using a subquery, I want to fix this example:

ao.find(USER_ENTITY_CLASS, Query.select()
                .distinct()
                .alias(USER_ENTITY_CLASS, "user")
                .alias(SALARY_ENTITY_CLASS, "salary")
                .join(SALARY_ENTITY_CLASS, "user.ID = salary.USER_ENTITY_ID")
                .order("salary.AVERAGE_SALARY ASC")

This example throws an error:
’Order by expression “salary.AVERAGE_SALARY” must be in the result list in this case’

In this query, I attach the Salary table to the Users table and sort the table by a field that will not be included in the final selection - AVERAGE_SALARY.

Without distinct() it work good, but I need to discard duplicate entries.
As I understand it, the “group buy” function does not work in an active object.
Perhaps there are other ways to solve this problem?

2 Likes
2

I think you have to do something like this

ao.find(USER_ENTITY_CLASS, Query.select("user.id, salary.AVERAGE_SALARY")

3

@clouless thank you, but your approach will not work. Because I have to retrieve fields to create an entity, so it has to be only fields from the user table without fields from the salary table but ordered by field from the salary table.

I founded why I can’t combine distinct and order, but I didn’t decide how to solve this problem yet.

4

I find a way: change distinct to group:

ao.find(USER_ENTITY_CLASS, Query.select()
                .group("user.ID")
                .alias(USER_ENTITY_CLASS, "user")
                .alias(SALARY_ENTITY_CLASS, "salary")
                .join(SALARY_ENTITY_CLASS, "user.ID = salary.USER_ENTITY_ID")
                .order("salary.AVERAGE_SALARY ASC")
2 Likes