Allow exists to be used within expressions

[gkaracha]

Hello! Is there a way to encode in Rel8 the following idiom?

SELECT ...
FROM table_name 
WHERE
  (condition1
  OR EXISTS (<some query here>)   -- this
  OR ...) 

More specifically, is there a way to use EXISTS in an Expr context? Currently exists seems to only work within Query, which makes it impossible to use it within a where_ clause

exists :: Query a -> Query (Expr Bool)

Is there a workaround for this at the moment? If not, are there any plans to implement this feature in the future?

For a little bit of context: in the project I'm currently working on we are trying to migrate from Hasql to Rel8 and a part of that is the conversion of filtering. The frontend sends potentially complex filters to the backend, which are then converted to an SQL expression that becomes part of a where_ clause. Hence the need to use exists within the Expr context.

Thanks for all the work you've put into this wonderful library!

[ocharles]

Oh, this an easy one - Rel8 already supports this! You're looking for present or with:

blah <- each table1Scheda
present do
  foo <- each table2Schema
  where_ $ foo.x ==. blah.y

Alternatively, you can use with to tidy this up a bit:

with hasFoo (each table1Schema)
  where
    hasFoo bar = do
      foo <- each fooSchema
      where_ $ foo.fooId ==. bar.fooId

Another option is to bind the result of your exists call to a variable, and then use that in where_:

hasFoo <- exists $ ...
where_ hasFoo

But this isn't quite the same as WHERE EXISTS, so you will probably want to check that one has acceptable performance.

[gkaracha]

Thanks for the suggestions @ocharles! Unfortunately none of them works for what I'm looking for. The last solution is perhaps the closest to a solution but that still totally breaks the modularity of the code. Currently the shape of things is roughly like so

interpretFilter :: BookFilter -> Book.Row Expr -> Expr Bool
interpretFilter fltr bookRow = ...

selectBooks :: BookFilter -> Statement () [ShipmentEntry]
selectBooks bookFilter = refineResult (mapM decode) statement
  where
    statement :: Statement () [Book.Row Result]
    statement = select $ orderBy ((.title) >$< asc) $ do
      book :: Books.Row Expr <- each Books.schema
      ... 
      -- Apply the filter
      where_ $ interpretFilter bookFilter book
      pure book

so the interpretation of the filter into an expression is very separate from the query itself.

The whole condition that is part of the where clause is directly (and hopefully semi-automatically) created from the giant filter structure the UI passes us, so it would be really inconvenient if the interpretation of the condition and the rest of the query had to be implemented in the same function. Every filter I've encountered so far in our system can be translated into Expr, apart from this type of filter:

SELECT *
FROM books
WHERE
     book.code = 'somecode'
  OR EXISTS (SELECT tag FROM UNNEST(book.tags) as tag WHERE tag ILIKE '%sometag%') -- *
  OR ... -- many more conditions here

(Note that the ORs are important here: if they were ANDs I could imagine modularly translating each condition into something of type Query () and then sequencing them, but that is not an option here because of the OR.)