ActiveObjects - left join

[Parag Bhole]

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.

[Tomislav Nikolic]

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 Query.select(), 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.

Cheers

[Parag Bhole]

Hi @Tomislav Nikolic 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.