content: {-# LANGUAGE BangPatterns, TypeFamilies, DataKinds, PolyKinds, TypeApplications, ScopedTypeVariables, ConstraintKinds, TypeOperators, GADTs, UndecidableInstances, StandaloneDeriving, FunctionalDependencies, FlexibleContexts, InstanceSigs, AllowAmbiguousTypes, DeriveAnyClass #-}
  {-|
  Module: IHP.QueryBuilder
  Description:  Tool to build simple sql queries
  Copyright: (c) digitally induced GmbH, 2020
  QueryBuilder is mainly used for doing simple `SELECT` sql queries. It allows dynamic
  creation of sql queries in a type safe way.
  
  For more complex sql queries, use 'IHP.ModelSupport.sqlQuery'.
  -}
  module IHP.QueryBuilder
  ( query
  , QueryBuilder (..)
  , In (In)
  , orderBy
  , orderByJoinedTable
  , orderByAsc
  , orderByAscJoinedTable
  , orderByDesc
  , orderByDescJoinedTable
  , limit
  , offset
  , queryUnion
  , queryUnionList
  , queryOr
  , DefaultScope (..)
  , filterWhere
  , filterWhereCaseInsensitive
  , filterWhereNot
  , filterWhereIn
  , filterWhereInCaseInsensitive
  , filterWhereIdIn
  , filterWhereNotIn
  , filterWhereLike
  , filterWhereILike
  , filterWhereMatches
  , filterWhereIMatches
  , filterWhereJoinedTable
  , filterWhereNotJoinedTable
  , filterWhereInJoinedTable
  , filterWhereNotInJoinedTable
  , filterWhereLikeJoinedTable
  , filterWhereILikeJoinedTable
  , filterWhereMatchesJoinedTable
  , filterWhereIMatchesJoinedTable
  , filterWherePast
  , filterWhereFuture
  , labelResults
  , EqOrIsOperator
  , filterWhereSql
  , FilterPrimaryKey (..)
  , distinctOn
  , distinct
  , toSQL
  , toSQL'
  , buildQuery
  , SQLQuery (..)
  , OrderByClause (..)
  , innerJoin
  , innerJoinThirdTable
  , HasQueryBuilder
  , JoinQueryBuilderWrapper
  , NoJoinQueryBuilderWrapper
  , LabeledQueryBuilderWrapper
  , getQueryBuilder
  , NoJoins
  , Condition (..)
  , Join (..)
  , OrderByDirection (..)
  , injectQueryBuilder
  , FilterOperator (..)
  , toEqOrIsOperator
  )
  where
  import IHP.Prelude
  import Database.PostgreSQL.Simple.Types (In (In))
  import Database.PostgreSQL.Simple.ToField
  import IHP.ModelSupport
  import qualified Data.ByteString.Builder as Builder
  import IHP.HSX.ToHtml
  import qualified Data.ByteString.Char8 as ByteString
  import qualified Data.ByteString.Lazy as LByteString
  import qualified Control.DeepSeq as DeepSeq
  import qualified Data.Text.Encoding as Text
  import Data.Text (toLower)
  import qualified GHC.Generics
  
  class DefaultScope table where
      defaultScope :: QueryBuilder table -> QueryBuilder table
  
  instance {-# OVERLAPPABLE #-} DefaultScope table where
      {-# INLINE defaultScope #-}
      defaultScope queryBuilder = queryBuilder
  
  instance Table (GetModelByTableName table) => Default (QueryBuilder table) where
      {-# INLINE def #-}
      def = NewQueryBuilder { selectFrom = tableNameByteString @(GetModelByTableName table), columns = columnNames @(GetModelByTableName table) }
  
  data MatchSensitivity = CaseSensitive | CaseInsensitive deriving (Show, Eq)
  
  data FilterOperator
      = EqOp -- ^ @col = val@
      | NotEqOp -- ^ @col != val@
      | InOp -- ^ @col IN (set)@
      | NotInOp -- ^ @col NOT IN (set)@
      | IsOp -- ^ @col IS val@
      | IsNotOp -- ^ @col IS NOT val@
      | LikeOp !MatchSensitivity -- ^ @col LIKE val@
      | NotLikeOp !MatchSensitivity -- ^ @col NOT LIKE val@
      | MatchesOp !MatchSensitivity -- ^ @col ~ pattern@
      | SqlOp -- ^ Used by 'filterWhereSql'
      deriving (Show, Eq)
  
  
  compileOperator :: FilterOperator -> ByteString
  compileOperator EqOp = "="
  compileOperator NotEqOp = "!="
  compileOperator InOp = "IN"
  compileOperator NotInOp = "NOT IN"
  compileOperator IsOp = "IS"
  compileOperator IsNotOp = "IS NOT"
  compileOperator (LikeOp CaseSensitive) = "LIKE"
  compileOperator (LikeOp CaseInsensitive) = "ILIKE"
  compileOperator (NotLikeOp CaseSensitive) = "NOT LIKE"
  compileOperator (NotLikeOp CaseInsensitive) = "NOT ILIKE"
  compileOperator (MatchesOp CaseSensitive) = " ~ "
  compileOperator (MatchesOp CaseInsensitive) = " ~* "
  compileOperator SqlOp = ""
  {-# INLINE compileOperator #-}
  
  -- | Returns the "NOT" version of an operator
  --
  -- >>> negateFilterOperator EqOp
  -- NotEqOp
  --
  negateFilterOperator :: FilterOperator -> FilterOperator
  negateFilterOperator EqOp = NotEqOp
  negateFilterOperator InOp = NotInOp
  negateFilterOperator IsOp = IsNotOp
  negateFilterOperator (LikeOp matchSensitivity) = (NotLikeOp matchSensitivity)
  negateFilterOperator (MatchesOp matchSensitivity) = error "not supported"
  negateFilterOperator SqlOp = SqlOp
  
  data OrderByClause =
      OrderByClause
      { orderByColumn :: !ByteString
      , orderByDirection :: !OrderByDirection }
      deriving (Show, Eq, GHC.Generics.Generic, DeepSeq.NFData)
  
  -- Types implementing a type level list to record joined tables. EmptyModelList and ConsModelList correspond to the data constructors [] and :. NoJoins is like the empty List but cannot be extended.
  data NoJoins
  data EmptyModelList
  data ConsModelList model models
  
  -- Type class to represent the true list type EmptyModelList ConsModelList.
  class ModelList a
  
  instance ModelList EmptyModelList
  instance ModelList (ConsModelList model models)
  
  -- Typeclass to quer containment in the type-level list.
  class IsJoined a b
  
  instance (ModelList b) => IsJoined a (ConsModelList a b)
  instance {-# OVERLAPPABLE #-} (ModelList b, IsJoined a b) => IsJoined a (ConsModelList c b)
  
  -- Class to generalise over different QueryBuilder-providing types. The actual query builder can be extracted with 'getQueryBuilder' and injected with 'injectQueryBuilder'. Also assigns a join reqister to a queryBilderProvider. 
  class HasQueryBuilder queryBuilderProvider joinRegister | queryBuilderProvider -> joinRegister where
      getQueryBuilder :: queryBuilderProvider table -> QueryBuilder table
      injectQueryBuilder :: QueryBuilder table -> queryBuilderProvider table
      getQueryIndex :: queryBuilderProvider table -> Maybe ByteString
      getQueryIndex _ = Nothing
      {-# INLINABLE getQueryIndex #-}
  
  -- Wrapper for QueryBuilders resulting from joins. Associates a joinRegister type.
  newtype JoinQueryBuilderWrapper joinRegister table = JoinQueryBuilderWrapper (QueryBuilder table)
  
  -- Wrapper for QueryBuilder that must not joins, e.g. queryUnion.
  newtype NoJoinQueryBuilderWrapper table = NoJoinQueryBuilderWrapper (QueryBuilder table)
  
  -- Wrapper for QueryBuilders with indexed results.
  newtype LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table = LabeledQueryBuilderWrapper (QueryBuilder table)
  
  -- QueryBuilders have query builders and the join register is empty.
  instance HasQueryBuilder QueryBuilder EmptyModelList where
      getQueryBuilder = id
      {-# INLINE getQueryBuilder #-}
      injectQueryBuilder = id
      {-# INLINE injectQueryBuilder #-}
  
  -- JoinQueryBuilderWrappers have query builders
  instance HasQueryBuilder (JoinQueryBuilderWrapper joinRegister) joinRegister where
      getQueryBuilder (JoinQueryBuilderWrapper queryBuilder) = queryBuilder
      {-# INLINABLE getQueryBuilder #-}
      injectQueryBuilder = JoinQueryBuilderWrapper 
      {-# INLINABLE injectQueryBuilder #-}
  
  -- NoJoinQueryBuilderWrapper have query builders and the join register does not allow any joins
  instance HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins where
      getQueryBuilder (NoJoinQueryBuilderWrapper queryBuilder) = queryBuilder
      {-# INLINABLE getQueryBuilder #-}
      injectQueryBuilder  = NoJoinQueryBuilderWrapper 
      {-# INLINABLE injectQueryBuilder #-}
  
  instance (KnownSymbol foreignTable, foreignModel ~ GetModelByTableName foreignTable , KnownSymbol indexColumn, HasField indexColumn foreignModel indexValue) => HasQueryBuilder (LabeledQueryBuilderWrapper foreignTable indexColumn indexValue) NoJoins where
      getQueryBuilder (LabeledQueryBuilderWrapper queryBuilder) = queryBuilder
      {-# INLINABLE getQueryBuilder #-}
      injectQueryBuilder = LabeledQueryBuilderWrapper
      {-# INLINABLE injectQueryBuilder #-}
      getQueryIndex _ = Just $ symbolToByteString @foreignTable <> "." <> (Text.encodeUtf8 . fieldNameToColumnName) (symbolToText @indexColumn)
      {-# INLINABLE getQueryIndex #-}
  
  
  data QueryBuilder (table :: Symbol) =
      NewQueryBuilder { selectFrom :: !ByteString, columns :: ![ByteString] }
      | DistinctQueryBuilder   { queryBuilder :: !(QueryBuilder table) }
      | DistinctOnQueryBuilder { queryBuilder :: !(QueryBuilder table), distinctOnColumn :: !ByteString }
      | FilterByQueryBuilder   { queryBuilder :: !(QueryBuilder table), queryFilter :: !(ByteString, FilterOperator, Action), applyLeft :: !(Maybe ByteString), applyRight :: !(Maybe ByteString) }
      | OrderByQueryBuilder    { queryBuilder :: !(QueryBuilder table), queryOrderByClause :: !OrderByClause }
      | LimitQueryBuilder      { queryBuilder :: !(QueryBuilder table), queryLimit :: !Int }
      | OffsetQueryBuilder     { queryBuilder :: !(QueryBuilder table), queryOffset :: !Int }
      | UnionQueryBuilder      { firstQueryBuilder :: !(QueryBuilder table), secondQueryBuilder :: !(QueryBuilder table) }
      | JoinQueryBuilder       { queryBuilder :: !(QueryBuilder table), joinData :: Join}
      deriving (Show, Eq)
  
  data Condition = VarCondition !ByteString !Action | OrCondition !Condition !Condition | AndCondition !Condition !Condition deriving (Show, Eq)
  
  -- | Display QueryBuilder's as their sql query inside HSX
  instance KnownSymbol table => ToHtml (QueryBuilder table) where
      toHtml queryBuilder = toHtml (toSQL queryBuilder)
  
  data Join = Join { table :: ByteString, tableJoinColumn :: ByteString, otherJoinColumn :: ByteString }
      deriving (Show, Eq)
  
  data OrderByDirection = Asc | Desc deriving (Eq, Show, GHC.Generics.Generic, DeepSeq.NFData)
  data SQLQuery = SQLQuery
      { queryIndex :: !(Maybe ByteString)
      , selectFrom :: !ByteString
      , distinctClause :: !(Maybe ByteString)
      , distinctOnClause :: !(Maybe ByteString)
      , whereCondition :: !(Maybe Condition)
      , joins :: ![Join]
      , orderByClause :: ![OrderByClause]
      , limitClause :: !(Maybe ByteString)
      , offsetClause :: !(Maybe ByteString)
      , columns :: ![ByteString]
      } deriving (Show, Eq)
  
  -- | Needed for the 'Eq QueryBuilder' instance
  deriving instance Eq Action
  
  -- | Need for the 'Eq QueryBuilder' instance
  --
  -- You likely wonder: Why do we need the 'Eq SQLQuery' instance if this causes so much trouble?
  -- This has to do with how has-many and belongs-to relations are models by the SchemaCompiler
  --
  -- E.g. given a table users and a table posts. Each Post belongs to a user. The schema compiler will
  -- add a field 'posts :: QueryBuilder "posts"' with the default value @query |> filterWhere (#userId, self.id)@ to all users by default.
  --
  -- This is needed to support syntax like this:
  --
  -- > fetch user.posts
  --
  instance Eq Builder.Builder where
      a == b = (Builder.toLazyByteString a) == (Builder.toLazyByteString b)
  
  instance SetField "queryIndex" SQLQuery (Maybe ByteString) where setField value sqlQuery = sqlQuery { queryIndex = value }
  instance SetField "selectFrom" SQLQuery ByteString where setField value sqlQuery = sqlQuery { selectFrom = value }
  instance SetField "distinctClause" SQLQuery (Maybe ByteString) where setField value sqlQuery = sqlQuery { distinctClause = value }
  instance SetField "distinctOnClause" SQLQuery (Maybe ByteString) where setField value sqlQuery = sqlQuery { distinctOnClause = value }
  instance SetField "whereCondition" SQLQuery (Maybe Condition) where setField value sqlQuery = sqlQuery { whereCondition = value }
  instance SetField "orderByClause" SQLQuery [OrderByClause] where setField value sqlQuery = sqlQuery { orderByClause = value }
  instance SetField "limitClause" SQLQuery (Maybe ByteString) where setField value sqlQuery = sqlQuery { limitClause = value }
  instance SetField "offsetClause" SQLQuery (Maybe ByteString) where setField value sqlQuery = sqlQuery { offsetClause = value }
  
  
  
  -- | Represent's a @SELECT * FROM ..@ query. It's the starting point to build a query.
  -- Used together with the other functions to compose a sql query.
  --
  -- Example:
  --
  -- > toSQL (query @User)
  -- > -- Returns: ("SELECT id, firstname, lastname FROM users", [])
  --
  -- Example: Fetching all users
  --
  -- > allUsers <- query @User |> fetch
  -- > -- Runs a 'SELECT * FROM users' query
  --
  -- You can use it together with 'filterWhere':
  --
  -- > activeUsers :: [User] <-
  -- >    query @User
  -- >     |> filterWhere (#active, True)
  -- >     |> fetch
  query :: forall model table. (table ~ GetTableName model, Table model) => DefaultScope table => QueryBuilder table
  query = (defaultScope @table) NewQueryBuilder { selectFrom = tableNameByteString @model, columns = columnNames @model }
  {-# INLINE query #-}
  
  {-# INLINE buildQuery #-}
  buildQuery :: forall table queryBuilderProvider joinRegister. (KnownSymbol table, HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> SQLQuery
  buildQuery queryBuilderProvider = buildQueryHelper $ getQueryBuilder queryBuilderProvider
      where
      buildQueryHelper NewQueryBuilder { selectFrom, columns } =
          SQLQuery
              {     queryIndex = getQueryIndex queryBuilderProvider 
                  , selectFrom = selectFrom
                  , distinctClause = Nothing
                  , distinctOnClause = Nothing
                  , whereCondition = Nothing
                  , joins = []
                  , orderByClause = []
                  , limitClause = Nothing
                  , offsetClause = Nothing
                  , columns
                  }
      buildQueryHelper DistinctQueryBuilder { queryBuilder } = queryBuilder
              |> buildQueryHelper
              |> setJust #distinctClause "DISTINCT"
      buildQueryHelper DistinctOnQueryBuilder { queryBuilder, distinctOnColumn } = queryBuilder
              |> buildQueryHelper
              |> setJust #distinctOnClause ("DISTINCT ON (" <> distinctOnColumn <> ")")
      buildQueryHelper FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, operator, value), applyLeft, applyRight } =
                  let
                      applyFn fn value = case fn of
                              Just fn -> fn <> "(" <> value <> ")"
                              Nothing -> value
  
                      condition = VarCondition (applyFn applyLeft columnName <> " " <> compileOperator operator <> " " <> applyFn applyRight "?") value
                  in
                      queryBuilder
                          |> buildQueryHelper
                          |> modify #whereCondition \case
                                  Just c -> Just (AndCondition c condition)
                                  Nothing -> Just condition
      buildQueryHelper OrderByQueryBuilder { queryBuilder, queryOrderByClause } = queryBuilder
              |> buildQueryHelper
              |> modify #orderByClause (\value -> value <> [queryOrderByClause] ) -- although adding to the end of a list is bad form, these lists are very short
      buildQueryHelper LimitQueryBuilder { queryBuilder, queryLimit } =
                      queryBuilder
                      |> buildQueryHelper
                      |> setJust #limitClause (
                              (Builder.byteString "LIMIT " <> Builder.intDec queryLimit)
                              |> Builder.toLazyByteString
                              |> LByteString.toStrict
                          )
      buildQueryHelper OffsetQueryBuilder { queryBuilder, queryOffset } = queryBuilder
              |> buildQueryHelper
              |> setJust #offsetClause (
                      (Builder.byteString "OFFSET " <> Builder.intDec queryOffset)
                      |> Builder.toLazyByteString
                      |> LByteString.toStrict
                  )
      buildQueryHelper UnionQueryBuilder { firstQueryBuilder, secondQueryBuilder } =
                  let
                      firstQuery = buildQueryHelper firstQueryBuilder
                      secondQuery = buildQueryHelper secondQueryBuilder
                      isSimpleQuery query = null (orderByClause query) && isNothing (limitClause query) && isNothing (offsetClause query) && null (joins query)
                      isSimpleUnion = isSimpleQuery firstQuery && isSimpleQuery secondQuery
                      unionWhere =
                          case (whereCondition firstQuery, whereCondition secondQuery) of
                              (Nothing, whereCondition) -> whereCondition
                              (whereCondition, Nothing) -> whereCondition
                              (Just firstWhere, Just secondWhere) -> Just $ OrCondition firstWhere secondWhere
                  in
                      if isSimpleUnion then
                          firstQuery { whereCondition = unionWhere }
                      else
                          error "buildQuery: Union of complex queries not supported yet"
      
      buildQueryHelper JoinQueryBuilder { queryBuilder, joinData } =
          let 
              firstQuery = buildQueryHelper queryBuilder
           in firstQuery { joins = joinData:joins firstQuery }
      
  -- | Transforms a @query @@User |> ..@ expression into a SQL Query. Returns a tuple with the sql query template and it's placeholder values.
  --
  -- __Example:__ Get the sql query that is represented by a QueryBuilder
  --
  -- >>> let postsQuery = query @Post |> filterWhere (#public, True)
  -- >>> toSQL postsQuery
  -- ("SELECT posts.* FROM posts WHERE public = ?", [Plain "true"])
  toSQL :: (KnownSymbol table, HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> (ByteString, [Action])
  toSQL queryBuilderProvider = toSQL' (buildQuery queryBuilderProvider)
  {-# INLINE toSQL #-}
  
  toSQL' :: SQLQuery -> (ByteString, [Action])
  toSQL' sqlQuery@SQLQuery { queryIndex, selectFrom, distinctClause, distinctOnClause, orderByClause, limitClause, offsetClause, columns } =
          (DeepSeq.force theQuery, theParams)
      where
          !theQuery =
              ByteString.intercalate " " $
                  catMaybes
                      [ Just "SELECT"
                      , distinctClause
                      , distinctOnClause
                      , Just selectors
                      , Just "FROM"
                      , Just fromClause
                      , joinClause
                      , whereConditions'
                      , orderByClause'
                      , limitClause
                      , offsetClause
                      ]
  
          selectors :: ByteString
          selectors = ByteString.intercalate ", " $ (catMaybes [queryIndex]) <> selectFromWithColumns
              where
                  -- Generates a string like: `posts.id, posts.title, posts.body`
                  selectFromWithColumns :: [ByteString]
                  selectFromWithColumns = 
                      columns
                      |> map (\column -> selectFrom <> "." <> column)
          fromClause :: ByteString
          fromClause = selectFrom
  
          !theParams =
              case whereCondition sqlQuery of
                  Just condition -> compileConditionArgs condition
                  Nothing -> mempty
  
          toQualifiedName unqualifiedName = selectFrom <> "." <> unqualifiedName
  
          whereConditions' = case whereCondition sqlQuery of
                  Just condition -> Just $ "WHERE " <> compileConditionQuery condition
                  Nothing -> Nothing
  
          orderByClause' :: Maybe ByteString
          orderByClause' = case orderByClause of
                  [] -> Nothing
                  xs -> Just ("ORDER BY " <> ByteString.intercalate "," ((map (\OrderByClause { orderByColumn, orderByDirection } -> orderByColumn <> (if orderByDirection == Desc then " DESC" else mempty)) xs)))
          joinClause :: Maybe ByteString
          joinClause = buildJoinClause $ reverse $ joins sqlQuery
          buildJoinClause :: [Join] -> Maybe ByteString
          buildJoinClause [] = Nothing
          buildJoinClause (joinClause:joinClauses) = Just $ "INNER JOIN " <> table joinClause <> " ON " <> tableJoinColumn joinClause <> " = " <>table joinClause <> "." <> otherJoinColumn joinClause <> maybe "" (" " <>) (buildJoinClause joinClauses)
  
  
  {-# INLINE toSQL' #-}
  
  {-# INLINE compileConditionQuery #-}
  compileConditionQuery :: Condition -> ByteString
  compileConditionQuery (VarCondition var _) =  var
  compileConditionQuery (OrCondition a b) =  "(" <> compileConditionQuery a <> ") OR (" <> compileConditionQuery b <> ")"
  compileConditionQuery (AndCondition a b) =  "(" <> compileConditionQuery a <> ") AND (" <> compileConditionQuery b <> ")"
  
  {-# INLINE compileConditionArgs #-}
  compileConditionArgs :: Condition -> [Action]
  compileConditionArgs (VarCondition _ arg) = [arg]
  compileConditionArgs (OrCondition a b) = compileConditionArgs a <> compileConditionArgs b
  compileConditionArgs (AndCondition a b) = compileConditionArgs a <> compileConditionArgs b
  
  class FilterPrimaryKey table where
      filterWhereId :: Id' table -> QueryBuilder table -> QueryBuilder table
  
  -- | Adds a simple @WHERE x = y@ condition to the query.
  --
  -- __Example:__ Only show projects where @active@ is @True@.
  --
  -- > activeProjects <- query @Project
  -- >     |> filterWhere (#active, True)
  -- >     |> fetch
  -- > -- SELECT * FROM projects WHERE active = True
  --
  -- __Example:__ Find book with title @Learn you a Haskell@.
  --
  -- > book <- query @Book
  -- >     |> filterWhere (#title, "Learn you a Haskell")
  -- >     |> fetchOne
  -- > -- SELECT * FROM books WHERE name = 'Learn you a Haskell' LIMIT 1
  --
  --
  -- __Example:__ Find active projects owned by the current user.
  --
  -- > projects <- query @Project
  -- >     |> filterWhere (#active, True)
  -- >     |> filterWhere (#currentUserId, currentUserId)
  -- >     |> fetch
  -- > -- SELECT * FROM projects WHERE active = true AND current_user_id = '..'
  --
  --
  -- For dynamic conditions (e.g. involving @NOW()@), see 'filterWhereSql'.
  --
  -- For @WHERE x IN (a, b, c)@ conditions, take a look at 'filterWhereIn' and 'filterWhereNotIn'.
  --
  -- For @WHERE x LIKE a@ or @WHERE x ~ a@  conditions, see 'filterWhereLike' and 'filterWhereMatches' respectively.
  -- For case-insensitive versions of these operators, see 'filterWhereILike' and 'filterWhereIMatches'.
  --
  -- When your condition is too complex, use a raw sql query with 'IHP.ModelSupport.sqlQuery'.
  filterWhere :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhere (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, toEqOrIsOperator value, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhere #-}
  
  -- | Like 'filterWhere', but takes a type argument specifying the table which holds the column that is to be compared. The column must have been joined before using 'innerJoin' or 'innerJoinThirdTable'. Example: 
  --
  -- __Example:__ get posts by user Tom.
  --
  -- > tomPosts <- query @Post
  -- >                    |> innerJoin @User (#createdBy, #id)
  -- >                    |> filterWhereJoinedTable @User (#name, "Tom" :: Text)
  -- >                    |> fetch
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name = 'Tom'
  --
  filterWhereJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, toEqOrIsOperator value, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereJoinedTable #-}
  
  -- | Like 'filterWhere' but negates the condition.
  --
  -- __Example:__ Only show projects created by other users.
  --
  -- > activeProjects <- query @Project
  -- >     |> filterWhereNot (#userId, currentUserId)
  -- >     |> fetch
  -- > -- SELECT * FROM projects WHERE user_id != '23d5ea33-b28e-4f0a-99b3-77a3564a2546'
  --
  filterWhereNot :: forall name table model value. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, Table model) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table
  filterWhereNot (name, value) queryBuilder = FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, negateFilterOperator (toEqOrIsOperator value), toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
  {-# INLINE filterWhereNot #-}
  
  -- | Like 'filterWhereNotJoinedTable' but negates the condition.
  --
  -- __Example:__ Only show projects not created by user Tom.
  --
  -- > tomPosts <- query @Post
  -- >                    |> innerJoin @User (#createdBy, #id)
  -- >                    |> filterWhereNotJoinedTable @User (#name, "Tom" :: Text)
  -- >                    |> fetch
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name = 'Tom'
  --
  filterWhereNotJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereNotJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, negateFilterOperator (toEqOrIsOperator value), toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereNotJoinedTable #-}
  -- | Adds a @WHERE x IN (y)@ condition to the query.
  --
  -- __Example:__ Only show projects where @status@ is @Draft@ or @Active@.
  --
  -- > visibleProjects <- query @Project
  -- >     |> filterWhereIn (#status, [Draft, Active])
  -- >     |> fetch
  -- > -- SELECT * FROM projects WHERE status IN ('draft', 'active')
  --
  -- For negation use 'filterWhereNotIn'
  --
  filterWhereIn :: forall name table model value queryBuilderProvider (joinRegister :: Type). (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, EqOrIsOperator value, Table model) => (Proxy name, [value]) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereIn (name, value) queryBuilderProvider =
          case head nullValues of
              Nothing -> injectQueryBuilder whereInQuery -- All values non null
              Just nullValue ->
                  let
                      isNullValueExpr = FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, IsOp, toField nullValue), applyLeft = Nothing, applyRight = Nothing }
                  in
                      case head nonNullValues of
                          Just nonNullValue -> -- Some non null values, some null values
                              injectQueryBuilder $ UnionQueryBuilder
                                  (injectQueryBuilder whereInQuery)
                                  (injectQueryBuilder isNullValueExpr)
                          Nothing -> injectQueryBuilder isNullValueExpr -- All values null
      where
          -- Only NOT NULL values can be compares inside the IN expression, NULL values have to be compares using a manual appended IS expression
          -- https://github.com/digitallyinduced/ihp/issues/906
          --
          (nonNullValues, nullValues) = value |> partition (\v -> toEqOrIsOperator v == EqOp)
  
          whereInQuery = FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, InOp, toField (In nonNullValues)), applyLeft = Nothing, applyRight = Nothing }
  
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereIn #-}
  
  -- Like 'filterWhereIn', but case insensitive.
  --
  -- __Example:__ Only show users where @email@ is @User1@example.com@ or @User2@example.com@.
  --
  -- > users <- query @User
  -- >     |> filterWhereInCaseInsensitive (#email, ['User1@example.com', 'User2@example.com'])
  -- >     |> fetch
  -- > -- SELECT * FROM users WHERE LOWER(email) IN ('user1@example.com', 'user2@example.com')
  --
  filterWhereInCaseInsensitive :: forall name table model value queryBuilderProvider (joinRegister :: Type). ( KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, EqOrIsOperator value, Table model) => (Proxy name, [Text]) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereInCaseInsensitive (name, values) queryBuilderProvider =
          case head nullValues of
              Nothing -> injectQueryBuilder whereInQuery
              Just nullValue ->
                  let
                      isNullValueExpr = FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, IsOp, toField nullValue), applyLeft = Nothing, applyRight = Nothing }
                  in
                      case head nonNullValues of
                          Just _ ->
                              injectQueryBuilder $ UnionQueryBuilder
                                  (injectQueryBuilder whereInQuery)
                                  (injectQueryBuilder isNullValueExpr)
                          Nothing -> injectQueryBuilder isNullValueExpr
      where
          (nonNullValues, nullValues) = values |> partition (\v -> toEqOrIsOperator v == EqOp)
  
          lowerValues = map toLower nonNullValues
  
          whereInQuery = FilterByQueryBuilder { queryBuilder, queryFilter = (lowerColumnName, InOp, toField (In lowerValues)), applyLeft = Nothing, applyRight = Nothing }
  
          lowerColumnName = "LOWER(" <> columnName <> ")"
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  
  {-# INLINE filterWhereInCaseInsensitive #-}
  
  -- | Like 'filterWhereIn', but takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using 'innerJoin' or 'innerJoinThirdTable'.
  -- 
  -- __Example:__ get posts by Tom and Tim.
  --
  -- > tomOrTimPosts <- query @Post 
  -- >    |> innerJoin @User (#createdBy, #id) 
  -- >    |> filterWhereInJoinedTable @User (#name, ["Tom","Tim"]) 
  -- >    |> fetch
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name IN ('Tom', 'Tim') 
  --
  filterWhereInJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, [value]) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereInJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, InOp, toField (In value)), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereInJoinedTable #-}
  
  
  -- | Adds a @WHERE x NOT IN (y)@ condition to the query.
  --
  -- __Example:__ Only show projects where @status@ is not @Archived@
  --
  -- > visibleProjects <- query @Project
  -- >     |> filterWhereNotIn (#status, [Archived])
  -- >     |> fetch
  -- > -- SELECT * FROM projects WHERE status NOT IN ('archived')
  --
  -- The inclusive version of this function is called 'filterWhereIn'.
  --
  filterWhereNotIn :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, EqOrIsOperator value) => (Proxy name, [value]) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereNotIn (_, []) queryBuilder = queryBuilder -- Handle empty case by ignoring query part: `WHERE x NOT IN ()`
  filterWhereNotIn (name, value) queryBuilderProvider =
          case head nullValues of
              Nothing -> injectQueryBuilder whereNotInQuery -- All values non null
              Just nullValue ->
                  case head nonNullValues of
                      Just nonNullValue -> injectQueryBuilder FilterByQueryBuilder { queryBuilder = whereNotInQuery, queryFilter = (columnName, IsNotOp, toField nullValue), applyLeft = Nothing, applyRight = Nothing } -- Some non null values, some null values
                      Nothing -> injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, IsNotOp, toField nullValue), applyLeft = Nothing, applyRight = Nothing } -- All values null
      where
          -- Only NOT NULL values can be compares inside the IN expression, NULL values have to be compares using a manual appended IS expression
          -- https://github.com/digitallyinduced/ihp/issues/906
          --
          (nonNullValues, nullValues) = value |> partition (\v -> toEqOrIsOperator v == EqOp)
  
          whereNotInQuery = FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, NotInOp, toField (In nonNullValues)), applyLeft = Nothing, applyRight = Nothing }
  
          columnName = Text.encodeUtf8 (symbolToText @table) <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereNotIn #-}
  
  -- | Like 'filterWhereNotIn', but takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using 'innerJoin' or 'innerJoinThirdTable'.
  --
  -- __Example:__ get posts by users not named Tom or Tim.
  --
  -- > notTomOrTimPosts <- query @Post
  -- >    |> innerJoin @User (#createdBy, #id)
  -- >    |> filterWhereNotInJoinedTable @User (#name, ["Tom","Tim"])
  -- >    |> fetch
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name NOT IN ('Tom', 'Tim')
  filterWhereNotInJoinedTable :: forall model name table  value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, [value]) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereNotInJoinedTable (_, []) queryBuilderProvider = queryBuilderProvider -- Handle empty case by ignoring query part: `WHERE x NOT IN ()`
  filterWhereNotInJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, NotInOp, toField (In value)), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereNotInJoinedTable #-}
  
  
  -- | Adds a @WHERE x LIKE y@ condition to the query.
  --
  -- __Example:__ Find titles matching search term.
  --
  -- > articles <- query @Article
  -- >     |> filterWhereLike (#title, "%" <> searchTerm <> "%")
  -- >     |> fetch
  -- > -- SELECT * FROM articles WHERE title LIKE '%..%'
  filterWhereLike :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereLike (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, LikeOp CaseSensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereLike #-}
  
  -- | Like 'filterWhereLik'e, but takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using 'innerJoin' or 'innerJoinThirdTable'.
  --
  -- __Example:__ Serach for Posts by users whose name contains "olaf" (case insensitive)
  --
  -- > olafPosts <- query @Post  
  -- >                |> innerJoin @User (#createdBy, #id)  
  -- >                |> filterWhereLikeJoinedTable @User (#name, "%Olaf%")
  -- >                |> fetch
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name LIKE '%Olaf%'
  filterWhereLikeJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol name, KnownSymbol table, table ~ GetTableName model, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereLikeJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, LikeOp CaseSensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereLikeJoinedTable #-}
  
  
  -- | Adds a @WHERE x ILIKE y@ condition to the query. Case-insensitive version of 'filterWhereLike'.
  --
  -- __Example:__ Find titles matching search term.
  --
  -- > articles <- query @Article
  -- >     |> filterWhereILike (#title, "%" <> searchTerm <> "%")
  -- >     |> fetch
  -- > -- SELECT * FROM articles WHERE title ILIKE '%..%'
  filterWhereILike :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereILike (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, LikeOp CaseInsensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereILike #-}
  
  -- | Like 'filterWhereILike'; case-insensitive version of filterWhereLikeJoinedTable, takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using 'innerJoin' or 'innerJoinThirdTable'.
  --
  -- __Example:__ Serach for Posts by users whose name contains "olaf" (case insensitive)
  --
  -- > olafPosts <- 
  -- >    query @Post  
  --      |> innerJoin @User (#createdBy, #id) 
  --      |> filterWhereILikeJoinedTable @User (#name, "%Olaf%")
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name ILIKE '%Olaf%' 
  filterWhereILikeJoinedTable :: forall model table name table' model' value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, model' ~ GetModelByTableName table', HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereILikeJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, LikeOp CaseInsensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereILikeJoinedTable #-}
  
  
  -- | Adds a @WHERE x ~ y@ condition to the query.
  --
  -- __Example:__ Find names with titles in front.
  --
  -- > articles <- query @User
  -- >     |> filterWhereMatches (#name, "^(M(rs|r|iss)|Dr|Sir). ")
  -- >     |> fetch
  -- > -- SELECT * FROM articles WHERE title ~ '^(M(rs|r|iss)|Dr|Sir). '
  filterWhereMatches :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereMatches (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, MatchesOp CaseSensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereMatches #-}
  
  -- | Adds a @WHERE x ~ y@ condition to the query, where the column x is held by a joined table.
  --
  -- __Example:__ Find Posts by people with names with titles in front.
  --
  -- > articles <- query @Post
  -- >     |> innerJoin @User (#createdBy, #id)
  -- >     |> filterWhereMatchesJoinedTable (#title, "^(M(rs|r|iss|s)|Dr|Sir). ")
  -- >     |> fetch
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.title ~ '^(M(rs|r|iss|s)|Dr|Sir). '
  
  filterWhereMatchesJoinedTable :: forall model table name value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereMatchesJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, MatchesOp CaseSensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereMatchesJoinedTable #-}
  
  
  -- | Adds a @WHERE x ~* y@ condition to the query. Case-insensitive version of 'filterWhereMatches'.
  filterWhereIMatches :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereIMatches (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, MatchesOp CaseInsensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereIMatches #-}
  
  -- | Case-insensitive version of 'filterWhereMatchesJoinedTable'
  filterWhereIMatchesJoinedTable :: forall model table name value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
  filterWhereIMatchesJoinedTable (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, MatchesOp CaseInsensitive, toField value), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereIMatchesJoinedTable #-}
  
  
  -- | Filter all rows by whether a field is in the past, determined by comparing 'NOW()' to the field's value.
  --
  -- Opposite of 'filterWhereFuture'
  --
  -- __Example:__ Fetch all posts scheduled for the past.
  --
  -- > publicPosts <- query @Post
  -- >     |> filterWherePast #scheduledAt
  -- >     |> fetch
  -- > -- SELECT * FROM posts WHERE scheduled_at <= NOW()
  filterWherePast
      :: ( KnownSymbol table
         , KnownSymbol name
         , ToField value
         , HasField name (GetModelByTableName table) value
         , HasQueryBuilder queryBuilderProvider joinRegister
         , Table (GetModelByTableName table)
         )
      => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
  filterWherePast name = filterWhereSql (name, "<= NOW()")
  {-# INLINE filterWherePast #-}
  
  -- | Filter all rows by whether a field is in the future, determined by comparing 'NOW()' to the field's value.
  --
  -- Opposite of 'filterWherePast'
  --
  -- __Example:__ Fetch all posts scheduled for the future.
  --
  -- > hiddenPosts <- query @Post
  -- >     |> filterWhereFuture #scheduledAt
  -- >     |> fetch
  -- > -- SELECT * FROM posts WHERE scheduled_at > NOW()
  filterWhereFuture
      :: ( KnownSymbol table
         , KnownSymbol name
         , ToField value
         , HasField name (GetModelByTableName table) value
         , HasQueryBuilder queryBuilderProvider joinRegister
         , Table (GetModelByTableName table)
         )
      => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereFuture name = filterWhereSql (name, "> NOW()")
  {-# INLINE filterWhereFuture #-}
  
  
  -- | Allows to add a custom raw sql where condition
  --
  -- If your query cannot be represented with 'filterWhereSql', take a look at 'IHP.ModelSupport.sqlQuery'.
  --
  -- __Example:__ Fetching all projects created in the last 24 hours.
  --
  -- > latestProjects <- query @Project
  -- >     |> filterWhereSql (#startedAt, "< current_timestamp - interval '1 day'")
  -- >     |> fetch
  -- > -- SELECT * FROM projects WHERE started_at < current_timestamp - interval '1 day'
  --
  filterWhereSql :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, ByteString) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereSql (name, sqlCondition) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, SqlOp, Plain (Builder.byteString sqlCondition)), applyLeft = Nothing, applyRight = Nothing }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereSql #-}
  
  -- | Adds a @WHERE LOWER(x) = LOWER(y)@ condition to the query.
  --
  -- __Example:__ Get a user by an email address, ignoring case
  --
  -- > user <- query @User
  -- >     |> filterWhereCaseInsensitive (#email, "marc@digitallyinduced.com")
  -- >     |> fetchOne
  -- > -- SELECT * FROM users WHERE LOWER(email) = 'marc@digitallyinduced.com'
  --
  -- For high performance it's best to have an index for @LOWER(field)@ in your Schema.sql
  --
  -- >>> CREATE UNIQUE INDEX users_email_index ON users ((LOWER(email)));
  --
  filterWhereCaseInsensitive :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereCaseInsensitive (name, value) queryBuilderProvider = injectQueryBuilder FilterByQueryBuilder { queryBuilder, queryFilter = (columnName, toEqOrIsOperator value, toField value), applyLeft = Just "LOWER", applyRight = Just "LOWER" }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE filterWhereCaseInsensitive #-}
  
  
  filterWhereIdIn :: forall table model queryBuilderProvider (joinRegister :: Type). (KnownSymbol table, Table model, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => [Id model] -> queryBuilderProvider table -> queryBuilderProvider table
  filterWhereIdIn values queryBuilderProvider =
      -- We don't need to treat null values differently here, because primary keys imply not-null
      let
          pkConditions = map (primaryKeyConditionForId @model) values
  
          queryBuilder = getQueryBuilder queryBuilderProvider
  
          whereInQuery = FilterByQueryBuilder {queryBuilder, queryFilter = (primaryKeyConditionColumnSelector @model, InOp, toField (In pkConditions)), applyLeft = Nothing, applyRight = Nothing}
       in
          injectQueryBuilder whereInQuery
  {-# INLINE filterWhereIdIn #-}
  
  
  -- | Joins a table to an existing QueryBuilder (or something holding a QueryBuilder) on the specified columns. Example:
  -- >    query @Posts 
  -- > |> innerJoin @Users (#author, #id)
  -- > -- SELECT users.* FROM users INNER JOIN posts ON users.id = posts.author ...
  innerJoin :: forall model' table' name' value' model table name value queryBuilderProvider joinRegister.
                              (
                                  KnownSymbol name, 
                                  KnownSymbol table,
                                  HasField name model value,
                                  KnownSymbol name', 
                                  KnownSymbol table',
                                  HasQueryBuilder queryBuilderProvider joinRegister,
                                  ModelList joinRegister,
                                  HasField name' model' value', 
                                  value ~ value',
                                  model ~ GetModelByTableName table,
                                  table' ~ GetTableName model'
                              ) => (Proxy name, Proxy name') -> queryBuilderProvider table -> JoinQueryBuilderWrapper (ConsModelList model' joinRegister) table 
  innerJoin (name, name') queryBuilderProvider = injectQueryBuilder $ JoinQueryBuilder (getQueryBuilder queryBuilderProvider) $ Join joinTableName leftJoinColumn rightJoinColumn 
      where 
          baseTableName = symbolToByteString @table
          joinTableName = symbolToByteString @table'
          leftJoinColumn = baseTableName <> "." <> (Text.encodeUtf8 . fieldNameToColumnName) (symbolToText @name)
          rightJoinColumn = (Text.encodeUtf8 . fieldNameToColumnName) (symbolToText @name')
  {-# INLINE innerJoin #-}
  
  -- | Index the values from a table with values of a field from a table joined by 'innerJoin' or 'innerJoinThirdTable'. Useful to get, e.g., the tags to a set of posts in such a way that the assignment of tags to posts is preserved.
  --
  --
  -- __Example:__ Fetch a list of all comments, each paired with the id of the post it belongs to.
  --
  -- > labeledTags <-
  -- >  query @Tag
  -- >     |> innerJoin @Tagging (#id, #tagId)
  -- >     |> innerJoinThirdTable @Post @Tagging (#id, #postId)
  -- >     |> labelResults @Post #id
  -- >     |> fetch
  -- > -- SELECT posts.id, tags.* FROM comments INNER JOIN taggings ON tags.id = taggings.tagId INNER JOIN posts ON posts.id = taggings.postId 
  -- 
  -- labeledTags is then a list of type ['LabeledData' (Id' "posts") Tag] such that "LabeledData postId tag" is contained in that list if "tag" is a tag of the post with id postId.
  --
  labelResults :: forall foreignModel baseModel foreignTable baseTable name value queryBuilderProvider joinRegister.
                  (
                      KnownSymbol foreignTable,
                      KnownSymbol baseTable,
                      foreignTable ~ GetTableName foreignModel, 
                      baseModel ~ GetModelByTableName baseTable,
                      HasField name foreignModel value,
                      HasQueryBuilder queryBuilderProvider joinRegister,
                      KnownSymbol name,
                      IsJoined foreignModel joinRegister
                  ) => Proxy name -> queryBuilderProvider baseTable -> LabeledQueryBuilderWrapper foreignTable name value baseTable
  labelResults name queryBuilderProvider = LabeledQueryBuilderWrapper $ getQueryBuilder queryBuilderProvider
                      
  -- | Joins a table on a column held by a previously joined table. Example:
  -- > query @Posts 
  -- > |> innerJoin @Users (#author, #id)
  -- > |> innerJoinThirdTable @City @Users (#id, #homeTown)
  -- > -- SELECT posts.* FROM posts INNER JOIN users ON posts.author = users.id INNER JOIN cities ON user.home_town = cities.id
  --
  innerJoinThirdTable :: forall model model' name name' value value' table table' baseTable baseModel queryBuilderProvider joinRegister.
                          ( 
                              KnownSymbol name,
                              KnownSymbol table,
                              HasField name model value,
                              KnownSymbol name',
                              KnownSymbol table',
                              HasQueryBuilder queryBuilderProvider joinRegister,
                              ModelList joinRegister,
                              HasField name' model' value',
                              value ~ value',
                              table ~ GetTableName model,
                              table' ~ GetTableName model',
                              baseModel ~ GetModelByTableName baseTable 
                          ) => (Proxy name, Proxy name') -> queryBuilderProvider baseTable -> JoinQueryBuilderWrapper (ConsModelList model joinRegister) baseTable
  innerJoinThirdTable (name, name') queryBuilderProvider = injectQueryBuilder $ JoinQueryBuilder (getQueryBuilder queryBuilderProvider) $ Join joinTableName leftJoinColumn rightJoinColumn
       where 
          baseTableName = symbolToByteString @table'
          joinTableName = symbolToByteString @table
          leftJoinColumn = baseTableName <> "." <> (Text.encodeUtf8 . fieldNameToColumnName) (symbolToText @name')
          rightJoinColumn = (Text.encodeUtf8 . fieldNameToColumnName) (symbolToText @name)
  {-# INLINE innerJoinThirdTable #-}
  
  -- | Adds an @ORDER BY .. ASC@ to your query.
  --
  -- Use 'orderByDesc' for descending order.
  --
  -- __Example:__ Fetch the 10 oldest books.
  --
  -- > query @Book
  -- >     |> orderBy #createdAt -- >     |> limit 10
  -- >     |> fetch
  -- > -- SELECT * FROM books LIMIT 10 ORDER BY created_at ASC
  orderByAsc :: forall name model table value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
  orderByAsc !name queryBuilderProvider = injectQueryBuilder OrderByQueryBuilder { queryBuilder, queryOrderByClause = OrderByClause { orderByColumn = columnName, orderByDirection = Asc } }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE orderByAsc #-}
  
  -- | Adds an @ORDER BY .. ASC@ on a joined table column to your query.
  --
  -- Use 'orderByDescJoinedTable' for descending order.
  --
  -- __Example:__ Order joined `User` records by `username` ascending.
  --
  -- > query @Project
  -- >     |> innerJoin @User (#id, #projectId)
  -- >     |> orderByAscJoinedTable #username
  -- >     |> fetch
  -- > -- SELECT ... FROM projects
  -- > -- INNER JOIN users ON projects.id = users.project_id
  -- > -- ORDER BY users.username ASC
  orderByAscJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. ( KnownSymbol table, KnownSymbol name, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => Proxy name -> queryBuilderProvider table' -> queryBuilderProvider table'
  orderByAscJoinedTable !name queryBuilderProvider = injectQueryBuilder OrderByQueryBuilder { queryBuilder = queryBuilder, queryOrderByClause = OrderByClause { orderByColumn = columnName, orderByDirection = Asc } }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE orderByAscJoinedTable #-}
  
  -- | Adds an @ORDER BY .. DESC@ to your query.
  --
  -- Use 'orderBy' for ascending order.
  --
  -- __Example:__ Fetch the 10 newest projects (ordered by creation time).
  --
  -- > query @Project
  -- >     |> orderByDesc #createdAt
  -- >     |> limit 10
  -- >     |> fetch
  -- > -- SELECT * FROM projects LIMIT 10 ORDER BY created_at DESC
  orderByDesc :: forall name model table value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
  orderByDesc !name queryBuilderProvider = injectQueryBuilder OrderByQueryBuilder { queryBuilder, queryOrderByClause = OrderByClause { orderByColumn = columnName, orderByDirection = Desc } }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE orderByDesc #-}
  
  -- | Adds an @ORDER BY .. DESC@ on a joined table column to your query.
  --
  -- Use 'orderByAscJoinedTable' for ascending order.
  --
  -- __Example:__ Order joined `User` records by `username` descending.
  --
  -- > query @Project
  -- >     |> innerJoin @User (#id, #projectId)
  -- >     |> orderByDescJoinedTable #username
  -- >     |> fetch
  -- > -- SELECT ... FROM projects
  -- > -- INNER JOIN users ON projects.id = users.project_id
  -- > -- ORDER BY users.username DESC
  orderByDescJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. ( KnownSymbol table, KnownSymbol name, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => Proxy name -> queryBuilderProvider table' -> queryBuilderProvider table'
  orderByDescJoinedTable !name queryBuilderProvider = injectQueryBuilder OrderByQueryBuilder { queryBuilder = queryBuilder, queryOrderByClause = OrderByClause { orderByColumn = columnName, orderByDirection = Desc } }
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE orderByDescJoinedTable #-}
  
  -- | Alias for 'orderByAsc'
  orderBy :: (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
  orderBy !name = orderByAsc name
  {-# INLINE orderBy #-}
  
  -- | Alias for 'orderByAscJoinedTable'
  orderByJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => Proxy name -> queryBuilderProvider table' -> queryBuilderProvider table'
  orderByJoinedTable !name = orderByAscJoinedTable @model @name @table @value @queryBuilderProvider @joinRegister @table' name
  {-# INLINE orderByJoinedTable #-}
  
  -- | Adds an @LIMIT ..@ to your query.
  --
  --
  -- __Example:__ Fetch 10 posts
  --
  -- > query @Post
  -- >     |> limit 10
  -- >     |> fetch
  -- > -- SELECT * FROM posts LIMIT 10
  limit :: (HasQueryBuilder queryBuilderProvider joinRegister) => Int -> queryBuilderProvider model -> queryBuilderProvider model
  limit !queryLimit queryBuilderProvider = injectQueryBuilder LimitQueryBuilder { queryBuilder, queryLimit }
      where
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE limit #-}
  
  -- | Adds an @OFFSET ..@ to your query. Most often used together with @LIMIT...@
  --
  --
  -- __Example:__ Fetch posts 10-20
  --
  -- > query @Post
  -- >     |> limit 10
  -- >     |> offset 10
  -- >     |> fetch
  -- > -- SELECT * FROM posts LIMIT 10 OFFSET 10
  offset :: (HasQueryBuilder queryBuilderProvider joinRegister) => Int -> queryBuilderProvider model -> queryBuilderProvider model
  offset !queryOffset queryBuilderProvider = injectQueryBuilder OffsetQueryBuilder { queryBuilder, queryOffset }
      where
          queryBuilder = getQueryBuilder queryBuilderProvider
  {-# INLINE offset #-}
  
  -- | Merges the results of two query builders.
  --
  -- Take a look at ‘queryOr'  as well, as this might be a bit shorter.
  --
  -- __Example:__ Return all pages owned by the user or owned by the users team.
  --
  -- > let userPages = query @Page |> filterWhere (#ownerId, currentUserId)
  -- > let teamPages = query @Page |> filterWhere (#teamId, currentTeamId)
  -- > pages <- queryUnion userPages teamPages |> fetch
  -- > -- (SELECT * FROM pages WHERE owner_id = '..') UNION (SELECT * FROM pages WHERE team_id = '..')
  queryUnion :: (HasQueryBuilder queryBuilderProvider joinRegister, HasQueryBuilder r joinRegister') => queryBuilderProvider model -> r model -> NoJoinQueryBuilderWrapper model
  queryUnion firstQueryBuilderProvider secondQueryBuilderProvider = NoJoinQueryBuilderWrapper (UnionQueryBuilder { firstQueryBuilder, secondQueryBuilder })
      where
          firstQueryBuilder = getQueryBuilder firstQueryBuilderProvider
          secondQueryBuilder = getQueryBuilder secondQueryBuilderProvider
  
      
  {-# INLINE queryUnion #-}
  
  -- | Like 'queryUnion', but applied on all the elements on the list
  --
  -- >  action ProjectsAction = do
  -- >      let values :: [(ProjectType, Int)] = [(ProjectTypeOngoing, 3), (ProjectTypeNotStarted, 2)]
  -- >
  -- >          valuePairToCondition :: (ProjectType, Int) -> QueryBuilder "projects"
  -- >          valuePairToCondition (projectType, participants) =
  -- >              query @Project
  -- >                  |> filterWhere (#projectType, projectType)
  -- >                  |> filterWhere (#participants, participants)
  -- >
  -- >          theQuery = queryUnionList (map valuePairToCondition values)
  -- >
  -- >      projects <- fetch theQuery
  -- >      render IndexView { .. }
  queryUnionList :: forall table. (Table (GetModelByTableName table), KnownSymbol table, GetTableName (GetModelByTableName table) ~ table) => [QueryBuilder table] -> QueryBuilder table
  queryUnionList [] = FilterByQueryBuilder { queryBuilder = query @(GetModelByTableName table) @table, queryFilter = ("id", NotEqOp, Plain "id"), applyLeft = Nothing, applyRight = Nothing }
  queryUnionList (firstQueryBuilder:secondQueryBuilder:[]) = UnionQueryBuilder { firstQueryBuilder, secondQueryBuilder }
  queryUnionList (firstQueryBuilder:rest) = UnionQueryBuilder { firstQueryBuilder, secondQueryBuilder = queryUnionList @table rest }
  
  
  -- | Adds an @a OR b@ condition
  --
  -- __Example:__ Return all pages owned by the user or public.
  --
  -- > query @Page
  -- >     |> queryOr
  -- >         (filterWhere (#createdBy, currentUserId))
  -- >         (filterWhere (#public, True))
  -- >     |> fetch
  -- > -- SELECT * FROM pages WHERE created_by = '..' OR public = True
  queryOr :: (HasQueryBuilder queryBuilderProvider joinRegister, HasQueryBuilder queryBuilderProvider'' joinRegister'', HasQueryBuilder queryBuilderProvider''' joinRegister''') => (queryBuilderProvider model -> queryBuilderProvider''' model) -> (queryBuilderProvider model -> queryBuilderProvider'' model) -> queryBuilderProvider model -> queryBuilderProvider model
  queryOr firstQuery secondQuery queryBuilder = injectQueryBuilder
      (UnionQueryBuilder { 
          firstQueryBuilder = getQueryBuilder $ firstQuery queryBuilder, 
          secondQueryBuilder = getQueryBuilder $ secondQuery queryBuilder}
      )
  {-# INLINE queryOr #-}
  
  -- | Adds a @DISTINCT@ to your query.
  --
  -- Use 'distinct' to remove all duplicate rows from the result
  --
  -- __Example:__ Fetch distinct books
  --
  -- > query @Book
  -- >     |> distinct
  -- >     |> fetch
  -- > -- SELECT DISTINCT * FROM books
  distinct :: (HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> queryBuilderProvider table
  distinct = injectQueryBuilder . DistinctQueryBuilder . getQueryBuilder
  {-# INLINE distinct #-}
  
  -- | Adds an @DISTINCT ON .. to your query.
  --
  -- Use 'distinctOn' to return a single row for each distinct value provided.
  --
  -- __Example:__ Fetch one book for each categoryId field
  --
  -- > query @Book
  -- >     |> distinctOn #categoryId
  -- >     |> fetch
  -- > -- SELECT DISTINCT ON (category_id) * FROM books
  distinctOn :: forall name model value table queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
  distinctOn !name queryBuilderProvider = injectQueryBuilder DistinctOnQueryBuilder { distinctOnColumn = columnName, queryBuilder = getQueryBuilder queryBuilderProvider}
      where
          columnName = tableNameByteString @model <> "." <> Text.encodeUtf8 (fieldNameToColumnName (symbolToText @name))
  {-# INLINE distinctOn #-}
  
  
  
  -- | Helper to deal with @some_field IS NULL@ and @some_field = 'some value'@
  class EqOrIsOperator value where toEqOrIsOperator :: value -> FilterOperator
  instance {-# OVERLAPS #-} EqOrIsOperator (Maybe something) where toEqOrIsOperator Nothing = IsOp; toEqOrIsOperator (Just _) = EqOp
  instance {-# OVERLAPPABLE #-} EqOrIsOperator otherwise where toEqOrIsOperator _ = EqOp
  