How to filter joining tables with OR instead of AND

[tiendq]

I want to search on name field of both RemindersList and Reminder, so query look like this (modified from here):

RemindersList
  .where { $0.name.like("%abc%") }
  .join(Reminder.where { $0.name.like("%abc%") }) {
    // ...
  }

SELECT
  "remindersLists".…,
  "reminders".…
FROM "remindersLists"
JOIN "reminders"
  ON "remindersLists"."id"
    = "reminders"."remindersListID"
WHERE ("remindersLists"."name" LIKE '%abc%')
  AND "reminders"."name" LIKE '%abc%'

while I need OR condition, is OR supported?

Thanks,

[mbrandonw]

Hi @tiendq, yes, you just need to move the where clauses after the joins:

RemindersList
  .join(Reminder.all) { … }
  .where { 
    $0.name.like("%abc%")
      .or($1.name.like("%abc%"))
  }

[tiendq]

Thanks, but there are two more issues:

1. I cannot use anonymous arguments with where (I actually use leftJoin):

RemindersList
  .leftJoin(Reminder.all) { … }
  .where { // Error: Instance method 'where' requires the types '(\_: Reminder?)' and '()' be equivalent
    $0.name.like("%abc%")
      .or($1.name.like("%abc%"))
  }

2. It's ok with named arguments, but then there is another issue with reminder (Reminder.name does exist):

RemindersList
  .leftJoin(Reminder.all) { … }
  .where { list, item in
    list.name.like("%abc%")
      .or(item.name.like("%abc%")) // Error: Value of type 'Optional<Reminder>.TableColumns' has no dynamic member 'name' using key path from root type 'Optional<Reminder>.TableColumns'equivalent
  }

Let's put a question mark there, it becomes .or(item?.name.like("%abc%")), the error disappears but there is a new familiar one at the end of whole query (which will disappear if I remove .or(item?.name.like("%abc%")) from the where clause).

The compiler is unable to type-check this expression in reasonable time; try breaking up the expression into distinct sub-expressions

Any idea?

[mbrandonw]

I'm not sure about the first one. We have plenty of examples in the tests of performing wheres after leftJoins in the test suite, so there must be something else wrong with the query. Can you share a full example?

And for the second, left joins optionalize the right side table (since it's possible to have reminders lists with no reminders). The operator like only works on QueryExpression<String>. So you can do something like:

reminder.name.ifnull("").like("%abc%")

…to turn reminder.name from a QueryExpression<String?> into a QueryExpression<String>.