Haskell Persist query

Question

I'm using Persistent.Sqlite for my DB IO,...and I'm struggling with following:

Lets say I have a table User with columns: User_id and User_age, and I want to run following query:

select * from User where (User_age * val_1) > val_2;

I have tried using *=. but that query combinator is allowed only for Update type. Most probably I might need to use rawSql?,..but then I'm not sure how to pass value to the query.

Answer 1

rawSql gives you raw power! With that you can literally do anything. In your case, this should work:

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
User
    age Int
    deriving Show
|]

getUser :: MonadIO m => ReaderT SqlBackend m [Entity User]
getUser = rawSql "select ?? from user where age * ? > ?" [PersistInt64 2, PersistInt64 40]

main :: IO ()
main = runSqlite "/home/sibi/test.db" $ do
         runMigration migrateAll

         johnId <- insert $ User 40
         janeId <- insert $ User 41

         users <- getUser
         liftIO $ print (users :: [Entity User])

For knowing more about the special placeholders ? and ??, read the documentation here. I have added quite a big example there to make things clear. Let me know if you are still having trouble.

The above code will produce an output of this:

$ ./sqlitePersist
[Entity {entityKey = UserKey {unUserKey = SqlBackendKey {unSqlBackendKey = 1}}, entityVal = User {userAge = 40}},Entity {entityKey = UserKey {unUserKey = SqlBackendKey {unSqlBackendKey = 2}}, entityVal = User {userAge = 41}}]