Open michalrus opened 6 years ago
There are a few ways this can be done.
The first way is to write the query with the expectation that every time you run this query, you'll always pass in every value. This involves making use of coalesce.
INSERT INTO blog_tags (entry_id, tag) VALUES (coalesce(?, currval(blog_entries_entry_id_seq)), ?)
The next option is to use CTEs and CASE
statements in the WHERE
clause. There are some operators that just don't play nicely with coalesce, such as @@
when performing a fulltext search. Note that this can sometimes create a slow query plan. Again, you will be passing in all values.
WITH
options (keyword, price) AS (VALUES (?, ?))
SELECT
*
FROM
foo
, options
WHERE
CASE WHEN options.price IS NOT NULL THEN foo.price && options.price END
AND CASE WHEN options.keyword IS NOT NULL THEN to_tsvector(foo.name) @@ plainto_tsquery(options.price) END
The last method involves creating a ToField
instance for a custom type. This is what I prefer to use when writing search queries where the search fields are optional.
import Data.List (intercalate)
import Database.PostgreSQL.Simple.ToField (ToField(..), Action(..))
data FooSearch = FooSearch
{ keyword :: Maybe Text
, price :: PGRange Int
}
instance ToField FooSearch where
toField s = buildWhereClause
[ if price s == (PGRange NegInfinity PosInfinity) then Nothing else Just [ Plain "price :: INT <@ ", toField $ price s, Plain " :: INT4RANGE" ]
, (\x -> [ Plain "to_tsvector(name) @@ plainto_tsquery(", toField x, Plain ")" ]) <$> keyword s
]
buildWhereClause :: [Maybe [Action]] -> Action
buildWhereClause = combineActionsWith (Plain " AND ")
combineActionsWith :: Action -> [Maybe [Action]] -> Action
combineActionsWith joiner xs = case intercalate [joiner] (catMaybes xs) of
[] -> Plain "1 = 1"
xs' -> Many xs'
If you run it like this:
query "SELECT * FROM foo WHERE ?" (Only $ FooSearch Nothing $ PGRange NegInfinity PosInfinity)
You get a query that looks like this:
SELECT * FROM foo WHERE 1 = 1
Thank you very much for the inspiration! :revolving_hearts:
If I have a query that once takes
(a,b)
and the other time only(Only a)
, how to do that?For the time being, this seems sensible:
But, I think, I’d much rather like to have a possibility to return an empty
Action
somehow…Is there anything available already?