It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

ActiveObjects - left join

We have 2 entities User and Designation, User can have a Designation or it could be null. In User list view, we want to sort users by Designation name. This will need to join 2 entities so that I can sort on Designation name but join eliminates the Users don't have designation.

Having LEFT join on Designation will solve the issue, but there is no option to specify join type in ActiveObjects. Is there any other way doing this?

Designation is just one related entity there could be multiple relations and I want to sort User based on related entities' values as well as return paginated users.

1 answer

Hello there.

Have you found a solution? I'm facing the same problem.

We had to do most of the filtering in Java. But recently I tried a different approach; it probably depends on your goal and table structure, but for me it also worked to put a null value in the joined table, and I would filter on where() with exact match of a column, and where that column was null.

Not sure of the consequences of that. Haven't had the chance to extensively test this.

The where clause looked like this in the end;

String where = new StringBuilder()
.append("(IT.KEY = ? AND P.KEY = ?)")
.append(" OR (IT.KEY = ? AND P.KEY IS NULL)")
.append(" OR (P.KEY = ? AND IT.KEY IS NULL)")
.append(" OR (P.KEY IS NULL AND IT.KEY IS NULL)").toString();

where "IT" and "P" are aliases added with .alias() on, and there is a join() on primary key of a third table.

I also used distinct during testing because I was uncertain as to what this query looks in the end since I also haven't been able to find a way to get the executed query string.

This of course creates an unnormalized table when absence of data could play the same role. Probably a better solution in my case would be to have a column in the primary table with columns "global" to perform the same but I wanted that to be a composite attribute.


Hi @Tomislav Nikolic _venITure_ we couldn't find the way to implement using AO, we used native queries executed using TransactionalExecutor. But with that approach we have to take care DB specific syntax.

I think querydsl could be better approach as suggested in some posts, but I have not tried it.

Like Yuriy Harbuziuk likes this

Suggest an answer

Log in or Sign up to answer

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you