How do I add conditions that possibly do not exist

There are some tables like the following in our system: locations, addresses, cities.

The locations has a address_id that refers to addresses, and addresses has city_id that refers to cities.

The addresses are optional for locations, and city is also optional for addresses, that means locations.address_id, addresses.city_id are nullable.

When there is a query keyword from client, we want to use SQL like to match one of these 3 items: locations.name, addresses.line1, cities.name, the later two could not exist at all.

I tried to use the clause similar to the following, the problem is the ...or...or will be evaluated too early.

dslContext.select(...)
  .from(
       LOCATIONS
           .leftJoin(
                ADDRESSES.leftJoin(CITIES).on(....)
           )
          .on(...)
   )
  .where(
     LOCATIONS.NAME.likeIgnoreCase(query)
         .or(ADDRESSES.LINE1.likeIgnoreCase(query))
         .or(CITIES.NAME.likeIngoreCase(query))
  )

If I moved ADDRESSES.LINE1.likeIgnoreCase(query) and CITIES.NAME.likeIngoreCase(query) to the on(...), from the query result, these clauses are not a OR relation to the LOCATIONS.NAME.likeIgnoreCase(query).

Assuming the ADDRESSES.LINE1 and CITIES.NAME fields are non-nullable, you can just add more predicates:

  .where(
     LOCATIONS.NAME.likeIgnoreCase(query)
         .or(ADDRESSES.LINE1.likeIgnoreCase(query))
         .or(ADDRESSES.LINE1.isNull())
         .or(CITIES.NAME.likeIngoreCase(query))
         .or(CITIES.NAME.isNull())
  )

If the fields are nullable, then you might need to take this semantics into account as well.