The where
clause allows you to narrow the list of instances returned. If no alias exists, you may refer to properties by name:
from Cat where name='Fritz'
If there is an alias, use a qualified property name:
from Cat as cat where cat.name='Fritz'
returns instances of Cat
named 'Fritz'.
select foo
from Foo foo, Bar bar
where foo.startDate = bar.date
will return all instances of Foo
for which there exists an instance of bar
with a date
property equal to the startDate
property of the Foo
. Compound path expressions make the where
clause extremely powerful. Consider:
from Cat cat where cat.mate.name is not null
This query translates to an SQL query with a table (inner) join. If you were to write something like
from Foo foo
where foo.bar.baz.customer.address.city is not null
you would end up with a query that would require four table joins in SQL.
The =
operator may be used to compare not only properties, but also instances:
from Cat cat, Cat rival where cat.mate = rival.mate
select cat, mate
from Cat cat, Cat mate
where cat.mate = mate
from Cat as cat where cat.id = 123
from Cat as cat where cat.mate.id = 69
The second query is efficient. No table join is required!
Properties of composite identifiers may also be used. Suppose
Person
has a composite identifier consisting of
country
and
medicareNumber
. Again, see
Section 14.5, “Refering to identifier property” for more information regarding referencing identifier properties.
from bank.Person person
where person.id.country = 'AU'
and person.id.medicareNumber = 123456
from bank.Account account
where account.owner.id.country = 'AU'
and account.owner.id.medicareNumber = 123456
Once again, the second query requires no table join.
Likewise, the special property class
accesses the discriminator value of an instance in the case of polymorphic persistence. A Java class name embedded in the where clause will be translated to its discriminator value.
from Cat cat where cat.class = DomesticCat
You may also use components or composite user types, or properties of said component types. See
Section 14.17, “Components” for more details.
An "any" type has the special properties id
and class
, allowing us to express a join in the following way (where AuditLog.item
is a property mapped with <any>
).
from AuditLog log, Payment payment
where log.item.class = 'Payment' and log.item.id = payment.id
Notice that log.item.class
and payment.class
would refer to the values of completely different database columns in the above query.