How to filter `ListTable`?

[worm2fed]

Hello! Hope you can help me..

I have this query

query = do
  individual@IndividualSchema{..} <- Sql.each individualSchema
  whenJust mq $ \q ->
    Sql.where_ $
      (q `Sql.iLikeCast` isName)
        Sql.||. (q `Sql.iLikeCast` isMission)
        Sql.||. (q `Sql.iLikeCast` isPhone)
  interests <- Sql.many $ getInterestByIndividualPkQ isIndividualPk
  pure (individual, interests)

And it works good. There is some individuals and they can have interests (via many-2-many). In this example I just filter by individual's fields.

But now I want to include results filtered by interests as well. So, for example I have an individual with interest "Bonsai", and when mq = Just "Bonsai" - I want to filter by it, similar as by other fields – ... Sql.||. (qSql.iLikeInterest.isName interests)

The best solution I was able to find is this (which is works partially):

query = do
  individual@IndividualSchema{..} <- Sql.each individualSchema
  interests <- Sql.many $ getInterestByIndividualPkQ isIndividualPk
  interestsExpr <- Sql.catListTable interests
  whenJust mq $ \q ->
    Sql.where_ $
      (q `Sql.iLikeCast` isName)
        Sql.||. (q `Sql.iLikeCast` isMission)
        Sql.||. (q `Sql.iLikeCast` isPhone)
        Sql.||. (q `Sql.iLike` Interest.isName interestsExpr)
  pure (individual, interests)

Problem with it that it excludes individuals without any interest (even when mq = Nothing) and duplicates entries (I guess - each per interest). The second problem can be solved with distinct, but not first one.

Another solution I've tried is to make to queries - one from the example at the beginning and second one similar to second example, but with proper usage of aggregate, and at the top - union - and this is works, but it's so ugly...

Seems like the main problem is that I do not understand how to work with ListTable.

Thank you!

[worm2fed]

Seems like I found a solution.

query = do
  individual@IndividualSchema{..} <- Sql.each individualSchema
  interests <- Sql.many $ getInterestByIndividualPkQ isIndividualPk
  whenJust mq $ \q ->
    Sql.where_ . Sql.foldOr $
      (q `Sql.iLikeCast` isName)
        :| [ q `Sql.iLikeCast` isMission
            , q `Sql.iLikeCast` isPhone
            , q `Sql.iLikeCast` (Interest.isName Sql.$* interests)
            ]
  pure (individual, interests)

I had to make a projection $*