No clue how to query with collection

Hi there!

Actually, what I have is something like:

@Embedable
class Address
{
    String getStreet();
    int getStreetNo();
}

@Entity
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..

thanks!

Alex

hi,

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...

Alex

Maybe something like:

SELECT c, a
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.