Home » Support » Forum » No clue how to query with collection

No clue how to query with collection


Hi there!

Actually, what I have is something like:

class Address
    String getStreet();
    int getStreetNo();

class Contact
    Collection<Address> getAddresses();

Now what I want to do is to select all available Customer Entites and ORDER them by the first Address - Item (if any) in the collection with a concatenated value of street + streetNo (if not null). So I want something like this:

SELECT c, a FROM Customer INNER JOIN c.addresses
ORDER BY SIZE(c.addresses) > 0 ?
  CONCAT(c.addresses[0].street + c.addresses[1].streetNo) : null

any clue? I can't figure how to do it with regular JPA Query methodics only..





okay I've decided that selecting the first item of a collection ain't be a good idea.

now I can select the right item from the collection with a WHERE clause BUT in this query:

SELECT c, a FROM Customer INNER JOIN c.addresses a
ORDER BY a.street WHERE a.active = true

I have the problem that I do also need to select all Customer's which either don't have any item filled in the addresses collection of which don't have an Address item in the collection with active=true set.. how to do that?? All I actually care about is the ordering, not the selection itself...



Maybe something like:

FROM Customer LEFT JOIN c.addresses a
WHERE a IS NULL OR a.active = TRUE
ORDER BY a.street

If it doesn't work, consider maintaining a field in Contact just for ordering.

It might also improve performance.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Post Reply

To post a reply and/or subscribe to update notifications - please login