prowdsponsor / esqueleto Goto Github PK
View Code? Open in Web Editor NEWBare bones, type-safe EDSL for SQL queries on persistent backends.
Home Page: http://hackage.haskell.org/package/esqueleto
License: BSD 3-Clause "New" or "Revised" License
Bare bones, type-safe EDSL for SQL queries on persistent backends.
Home Page: http://hackage.haskell.org/package/esqueleto
License: BSD 3-Clause "New" or "Revised" License
esqueleto
1.3.4.2 depends on hspec >=1.3 && <1.8
, but 1.8.1.1
is now out.
Is it possible have delete and update return the number of rows changes? I believe some of the sqls return this information as text string. I don't know if this is standard for all sqls, but would like support for those that do.
https://gist.github.com/mstksg/6182519
Many-to-many join, with sorting on a field on the associated table.
The goal is to find all tags, but sort them by most recent entry.
No error happens when:
Tag
(and no fields on Entry
).Errors only happen when attempting to sort by a field on Entry
.
The error is:
main.hs: user error (Postgresql.withStmt': bad result status FatalError (("PGRES_FATAL_ERROR","ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list\nLINE 3: ORDER BY \"entry\".\"day_posted\" DESC, \"tag\".\"label\" ASC\n ^\n")))
Is this an issue with esquelito or one with persistent?
Also I'm not even sure if I'm properly doing what I'm trying to do (sort tags by most recent entry) -- if I'm doing it wrong and there's a proper way to do this, please let me know. This is my first time playing around at this low of a level to actual sql. That being said, it does do what it's supposed to do when using sqlite3 connections.
fromValue :: Value a -> a
fromValue2 :: (Value a, Value b) -> (a, b)
:
I might be doing this incorrectly, but I was wondering if there was a way to rename columns in Esqueleto so you can select from them twice in a single query.
As an example, I have some code that selects the two-component phrase ["foo", "bar"] from my db:
select c1.id,c2.id
from component as c1, component as c2, word_component as w1,
word_component as w2
where c1.text='foo'
and c2.text='bar'
and w1.component_id=c1.id
and w2.component_id=c2.id
and w1.word_id=w2.word_id
and w1.index=1 and w2.index=2;
Have I missed something, or is this just missing functionality?
Consider following question on StackOverflow: http://stackoverflow.com/questions/4230780/mysql-query-with-multiple-limits
I have similar task, but I can't find union function in Esqueleto. Can I introduce similar single query using Esqueleto or should I make several queries?
Thank you.
At line 244 of esqueleto/src/Database/Esqueleto.hs
you have
-- Since @age@ is an optional @Person@ field, we use 'just' lift
-- @val 18 :: SqlExpr (Value Int)@ into @just (val 18) ::
-- SqlExpr (Value (Just Int))@.
The final type signature should have Maybe
instead of Just
, i.e. SqlExpr (Value (Maybe Int))
.
On Fri, Sep 7, 2012 at 7:42 AM, Arthur Clemens [email protected] wrote:
This looks promising.
Trying out, I notice that I need to hide ==. from Yesod and
Database.Persist.Sqlite, so my imports become:import Yesod hiding ((==.))
import Database.Persist.Sqlite (withSqliteConn, withSqlitePool)
import Database.Persist.TH
Yesod exports Yesod.Persist, which in turn exports Database.Persist, which in turns exports Database.Persist.Query, which conflicts with a lot of symbols from esqueleto.
Hmm... I guess I'll have to say on the docs that either you manually import Yesod modules, or you import esqueleto qualified.
I often find myself matching a foreign key from a table with a Int64
and keep writing something like:
u ^.UserId ==. val (Key (PersistInt64 i))
Instead, it would nice to have something like this which works like the present function val
:
valkey :: Esqueleto query expr backend =>
Int64 -> expr (Value (Key backend entity))
valkey i = val (Key (PersistInt64 i))
so the above could be written as:
u ^.UserId ==. valkey i
http://www.network-theory.co.uk/docs/postgresql/vol1/COALESCE.html
Would esqueleto welcome the addition of something like...
coalesce :: PersistField a => [expr (Value (Maybe a))] -> expr (Value (Maybe a))
coalesceNonNull :: PersistField a => [expr (Value (Maybe a))] -> expr (Value a) -> expr (Value a)
added to the Esqueleto class? Or, is there any way to mimic this function currently? Thanks.
I would like to use custom operators with Esqueleto. For example, PostgreSQL has ILIKE operator that is case-insensitive variant of case-sensitive LIKE-operator. Also, regular expressions can be useful, too.
Currently (^.) strips away field type, so when you pick couple of different fields from your datatype you'll get something meaningful like (Text, Int, Text)
But it might make sense to keep EntityField val typ so ToJSON instance can be derived from that (using generics) - it will simplify working with JSON APIs.
In case you did not see this SO question, here's my problem this time: http://stackoverflow.com/questions/21754451/choosing-database-esqueleto-sql-operators-dynamically
For a quicker understanding for getting values out of a query, it would help to see the type signatures in action. For example:
getBlogPersons :: SqlPersist IO [(Entity BlogPost, Entity Person)]
getBlogPersons = select $
from $ \(b, p) -> do
where_ (b ^. BlogPostAuthorId ==. p ^. PersonId)
orderBy [asc (b ^. BlogPostTitle)]
return (b, p)
blog_persons <- getBlogPersons
let bp = map (\(b, p) -> (entityVal b, entityVal p)) blog_persons
bp :: [(BlogPost, Person)]
bpp = map (\(b, p) -> (blogPostTitle b ++ " " ++ personName p)) bp
bpp :: [String]
liftIO $ mapM_ putStrLn bpp
Often when selecting a Maybe
field from the database I'll also have a where-clause that restricts that value to a non-null result. Unfortunately it is not really possible to infer this restriction in the type system, so currently ^.
and ?.
will return Value (Maybe a)
For example, I am trying to write something similar to this:
findSubstitutesForBomLines :: ProjectRevisionId -> SqlPersistT Handler [(Value ProjectBomId, Value Int)]
findSubstitutesForBomLines rid = do
items <-
select $
from $ \(bom `LeftOuterJoin` sub) -> do
on (sub ?. ProjectBomSubstituteProjectBomID ==. just (bom ^. ProjectBomId))
where_ $ ((isNothing $ sub ?. ProjectBomSubstituteId)
&&. (not_ $ isNothing $ bom ^. ProjectBomPartID) -- Constrain ProjectBomPartID to not null
&&. (bom ^. ProjectBomProjectRevisionID ==. val rid))
return $ (bom ^. ProjectBomId, bom ^. ProjectBomPartID)
return $ mapSnd (fmap fromJust) items -- Error: This does not actually work since Value is not a Functor...
where
mapSnd f xys = [(x, f y) | (x,y) <- xys]
However, the best I can do is this:
findSubstitutesForBomLines :: ProjectRevisionId -> SqlPersistT Handler [(Value ProjectBomId, Value (Maybe Int))]
--findSubstitutesForBomLines :: ProjectRevisionId -> SqlPersistT Handler [(Value ProjectBomId, Value Int)]
findSubstitutesForBomLines rid = do
items <-
select $
from $ \(bom `LeftOuterJoin` sub) -> do
on (sub ?. ProjectBomSubstituteProjectBomID ==. just (bom ^. ProjectBomId))
where_ $ ((isNothing $ sub ?. ProjectBomSubstituteId)
&&. (not_ $ isNothing $ bom ^. ProjectBomPartID) -- Constrain ProjectBomPartID to not null
&&. (bom ^. ProjectBomProjectRevisionID ==. val rid))
return $ (bom ^. ProjectBomId, bom ^. ProjectBomPartID)
return items
-- return $ mapSnd (fmap fromJust) items
-- where
-- mapSnd f xys = [(x, f y) | (x,y) <- xys]
It would be very convenient (though admittedly unsafe) if I could just use a coercive operator, something like say (^!)
and (?!)
to check this constraint at run-time, allowing:
findSubstitutesForBomLines :: ProjectRevisionId -> SqlPersistT Handler [(Value ProjectBomId, Value Int)]
findSubstitutesForBomLines rid =
select $
from $ \(bom `LeftOuterJoin` sub) -> do
on (sub ?. ProjectBomSubstituteProjectBomID ==. just (bom ^. ProjectBomId))
where_ $ ((isNothing $ sub ?. ProjectBomSubstituteId)
&&. (not_ $ isNothing $ bom ^. ProjectBomPartID) -- Constrain ProjectBomPartID to not null
&&. (bom ^. ProjectBomProjectRevisionID ==. val rid))
return $ (bom ^. ProjectBomId, bom ^! ProjectBomPartID) -- (^!) coerces Maybe a -> a
EDIT: Admittedly I'm not sure if there could be any way to make this work sensibly in where
/ on
clauses etc. At least, issueing a run-time error would seem impossible.
I'm not sure since I am not the most familiar with the alternative databases, but here's an example query that I'm not sure I'll be able to express in esqueleto.
SELECT
CASE
WHEN
EXISTS(
SELECT
NULL
FROM
karma_received_count
WHERE
name = "nishbot")
THEN
(SELECT
COUNT(karma_received_count.name) + 1
FROM
karma_received_count
WHERE
(karma_received_count.up - karma_received_count.down) > (
SELECT
(karma_received_count2.up - karma_received_count2.down)
FROM
karma_received_count AS karma_received_count2
WHERE
karma_received_count2.name = "nishbot"))
ELSE
-1
END
I'm using the CASE WHEN
feature of SQLite, and from my understanding the other databases have it similiarly as IF THEN ELSE
and the doc is SQLite expressions.
The best i've been able to do to express this is to split it up as two separate query and then run the second query if the first one succeeds in its check.
Example Code:
rankingDenormalizedT karmaName karmaTotal whom = do
e <- select $ from (\v -> do
where_ (karmaGivenName v ==. val whom)
return (karmaGivenName v)
)
if null e
then return Nothing
else (do
r <- select $ from (\v -> do
let sub = from $ (\c -> do
where_ (karmaName c ==. val whom)
return $ karmaTotal c
)
where_ (karmaTotal v >. sub_select sub)
return $ count (karmaName v) +. val 1 :: SqlQuery (SqlExpr (Value Int))
)
return $ Just $ unValue $ head r -- TODO: unsafe head
)
MySQL/MariaDB implement random
as rand
:
https://mariadb.com/kb/en/rand/
And, while featuring a different name, it returns the same type as PostgreSQL, a double between zero and one (SQLite returns an Integer).
So, shall we define a flag and CPP for Sql.hs ?
#ifdef WITH_MYSQL
random_ = unsafeSqlValue "RAND()"
#else
random_ = unsafeSqlValue "RANDOM()"
#endif
Without it, the tests:
Fail with
uncaught exception: MySQLError (ConnectionError {errFunction = "query", errNumber = 1548, errMessage = "Cannot load from mysql.proc. The table is probably corrupted"})
With it, and the same type annotation adopted in the PostgreSQL test, both tests pass.
Recently I used GHC 7.6.3, everything worked well. But after then I've installed GHC
v7.8.20140130 to use GHCJS. Now I can't install Yesod.
$ cabal install yesod-platform yesod-bin --max-backjumps=-1 --reorder-goals
Resolving dependencies...
cabal: Could not resolve dependencies:
trying: yesod-platform-1.2.7.1 (user goal)
trying: base-4.7.0.0/installed-e4f... (dependency of yesod-platform-1.2.7.1)
next goal: esqueleto (dependency of yesod-platform-1.2.7.1)
rejecting: esqueleto-1.3.4.5 (conflict: base==4.7.0.0/installed-e4f...,
esqueleto => base>=4.5 && <4.7)
rejecting: esqueleto-1.3.4.4, 1.3.4.3, 1.3.4.2, 1.3.4, 1.3.3, 1.3.2, 1.3.1,
1.3, 1.2.4, 1.2.3, 1.2.2.1, 1.2.2, 1.2.1, 1.2, 1.0.8, 1.0.7, 1.0.6, 1.0.5,
1.0.3, 1.0.2, 1.0.1, 1.0, 0.2.9, 0.2.8, 0.2.7, 0.2.6, 0.2.5, 0.2.4, 0.2.3,
0.2.2, 0.2.1, 0.2, 0.1 (conflict: yesod-platform => esqueleto==1.3.4.5)
Dependency tree exhaustively searched.
$ ghc --version
The Glorious Glasgow Haskell Compilation System, version 7.8.20140130
$ ghcjs --version
The Glorious Glasgow Haskell Compilation System for JavaScript, version 0.1.0 (GHC 7.8.20140130)
I've seen test failures occurring in my past few Stackage builds:
1) rand works returns result in random order
expected: False
but got: True
Randomized with seed 1361376607
This is not a issue, but a question about an eventual feature.
I've seen a system a couple of years ago which was doing "auto-joins" in a really elegant way. I've been thinking of implementing it for a while and I wonder if Esqueleto is the right tool to do so.
The main idea is, except for a few exception, joins between tables are always the same. So for a given "context", you can deduce the joins which are needed only from the fields which are involved in the request.
The concept of "context" is the key thing here. Instead of relying on foreign keys to do auto-join, a "context" defines a DAG of tables and for each query corresponds to a subset of this graph.
Example, I have some Users, Comments (made by a User) and Items (purchased by a User).
Classical queries would be :
The 2 first joins are a subset of the last one.
In theory, I could solve my problem by writing a giant view like :
SELECT *
FROM user
JOIN item ON (user.id = item.user_id)
JOIN comment ON (user.id = comment_id)
And then write all of my queries using this giant view like :
SELECT user
FROM giant_view
WHERE comment = 'hello'
SELECT user
FROM giant_view
WHERE item = 'map'
SELECT item
FROM giant_view
WHERE comment = 'hello'
This almost works but there is a lot of problems, the main one is results get spoiled by table which haven't been used. If a user has bought 3 items, then he will appears 3 times in the first query. That can be solved by using DISTINCT
but is probably inefficient. Then if the user hasn't bought any thing. It won't appear at all even if he made an 'hello' comment. That also can be solved by doing a outer join etc ....
It seems that one static view doesn't work, but a dynamically tweaked on could work.
Also, we need different "contexts" (or different giant views) because we need an acyclic graph and the database schema can have cycle. A "context" is then maximum acyclic graph of the overall one.
Anyway, generating the sub view covering a set of field seems relatively easy, but to get this list of field, there are two options 👍
Parsing the raw SQL can be messy and I'm not sure it can be done easily in a type-safe way.
If I'm using an EDSL, I'll probably better use an existing one : there is HaskellDB and Esqueleto.
HaskellDB seems dead (am I wrong ?) and not flexible enough (you have to specify the table)
Esqueleto is clever enough to figure out tables itself from the field and even cross join, so it seems to be the perfect candidate for this project.
So my question is, does anyone think this can be done relatively easily as an Esqueleto extension, or I am mistaken and the current structure of esqueleto
won't allow me to do this.
I find myself using both Data.Function.on and on from Esqueleto, and so am forced to disambiguate. Since we already have where_ in Esqueleto, modifying "on" the same way seems the clearest. I don't have any strong need for this to be moved up-stream but I figured I'd raise the issue in case others are encountering the same.
Hi Felipe,
I was further studying Esqueleto (after some successful previous experiences), and I thought the best way to start would be to analyze the tests. Great way to have examples, by the way!
Anyhow, I noticed all the tests were being run in SQLite, so I set out to try them in PostgreSQL, since that will be the database I'll be using. Simple enough, I added the dependency to the Cabal and changed the database initializing string:
withPostgresqlConn "host=localhost port=5432 user=xpto dbname=esqueleto" .
However, I was a bit surprised at first that some tests were not passing. Well, I then remember that you clearly stated that:
We do not try to hide the differences between DBMSs from you, and esqueleto code that works for one database may not work on another.
Nevertheless, not all failing tests are falling under this category, and in at least one case (the first) a test can be simply modified to also run on Postgres.
I was in doubt if I should open a issue for each one of them, but I opted to group it all on one issue. If you prefer, I can split it out.
So, one by one:
This test assumes a given order, which is however not made explicit. By pure chance, I guess, PostgreSQL returns the two rows in the inverse order. By making the ordering explicit, this test now passes, without influencing SQLite results.
jcristovao@84f2f08
While in SQLite and MySQL the sum function returns the same type it receives, this is not the case in Postgres
http://www.postgresql.org/docs/9.2/static/functions-aggregate.html
In fact, when summing to 'BigInts', the result is 'Numeric', which translates to Rational in the underlying Postrgres haskell libs.
To support this, a change is necessary in the code, specifying this:
sum_ :: (PersistField a, PersistField b) => expr (Value a) -> expr (Value (Maybe b))
However, while this change has no impact on the sum_ test under SQLite, since its expected result type is already properly annotated, when run under Postgres this type needs to be changed to Rational.
So, what would be your preferred approach?
While I agree that 1) is faster, it would certainly help Esqueleto adoption if a user is certain of which tests / features work on its DB of choice. I would, of course, be willing to volunteer the modifications made to run under PostgreSQL.
A similar problem. No type signature change is required in the src code, but while SQLite returns an Integer, Postgres returns a double.
This affects tests:
The crux of the problem is that Postrgres considers NULLs bigger than everything else, while SQLite and MySQL consider the exact opposite. Postgres can be made to support a similar behaviour by specifying:
orderByType ASC = " ASC NULLS FIRST"
orderByType DESC = " DESC NULLS LAST"
But, as it would be expected, this syntax is not supported by the other DBs. So, either we add a dependency for CPP like #ifdefs, or actively maintain a different branch or file (if we want consistent behaviour for all backends). Otherwise, the tests can be modified / branched to fit PostgreSQL behaviour. I don't know... what's your take on this?
Well, this one I believe its a bug in the code, as I described here, but one that unfortunately I don't know enough to solve.
Sorry for the long post, will be glad to hear your input,
Abraço
EDIT: You can find here my fork with only test 5) failing.
It looks like like
doesn't work with a Maybe
field in the database because it expects the arguments to satisfy IsString s
.
E.g. This does not work if field
of table A
is a Maybe
type
results <- select $ from $ \a -> do
where_ $ (a ^. AField) `like` concat_ [(%), val "some text", (%)]
return a
max_, min_ and sum_ currently are (PersistField a, PersistField b) => expr (Value a) -> expr (Value b)
. Here's some code where this has bitten me:
select $ from $ \(e `InnerJoin` v) -> do
on (e ^. EntryId ==. v ^. EntryVersionEntry)
groupBy (e ^. EntryId, e ^. EntryName)
orderBy [desc $ max_ $ v ^. EntryVersionDate]
return (e ^. EntryId, e ^. EntryName)
Since we don't return the max date its type is ambiguous and the code doesn't compile. I can't think of an example where MAX(), MIN() and SUM() would return a different type from its argument, so I propose to change their types to (PersistField a) => expr (Value a) -> expr (Value a)
. If you're okay with that, I'll make a pull request.
Also arguably the type of round_ could be (PersistField a, RealFrac a, PersistField b, Integral b) => expr (Value a) -> expr (Value b)
and similarly for ceiling_ and floor_, but this is probably too much.
It seems that Postgresql is much more sensitive to the ordering of the on
clauses for the InnerJoin
or a from
clause than Sqlite.
If one writes a working query against Sqlite and then runs it against Postgresql it can give a rather ugly run-time failure. Unless one reads (again) the documentation for the on
clause one is like to start digging into the Esqueleto code to figure out why.
I propose that we add code to Esqueleto that enforces the on
clause restrictions and print better error messages. I'm even willing to do the work :-)
Is that a good idea?
Often, I write a query with the following return type:
type Exp a = SqlExpr (Entity a)
myQuery :: SqlQuery (Exp Promotion, Exp Play, Exp Message)
And then when I run it with select
, I get a triple. However, I would rather have my query look like this:
data PlayInfo = PlayInfo (Entity Promotion) (Entity Play) (Entity Message)
myQuery :: SqlQuery (SqlExpr PlayInfo)
This would avoid using triples in all the type signatures when the results get propagated to other places. I cannot figure out how to do this. The query ends with the line:
return (promo, play, message)
And I can't figure out how to change this to wrap them in a non-tuple data structure. Additionally, I was wondering if it's possible to compose something with select
that would strip off all the the Value
wrappers on any non-Entity
s that get returned. It is a little cumbersome getting Value Int
instead of Int
after calling select. Thanks for any insights people can provide.
I had to pull esqueleto recently and manually bump the text version, things seems to work for me so far.
I have some tables with a column name of user, which must always go double-quoted to disambiguate from the keyword. This works just fine with most of persistent and esqueleto (everything I've touched up to now), but breaks with insertSelect. I don't immediately follow the code well enough to build a pull request, but can dive in and do so if needed.
I have a Maybe EntityId-field for which I'd like to use IN-operator as follows:
e ^. SomeField in_
(just (subList_select $ from $ \e2 -> return e2 ^. SomeField))
Can I promote ValueList a to ValueList (Maybe a) somehow?
There's nothing in the type system to stop me writing a query which does a "double aggregation". The generated SQL is invalid.
sumBar :: SqlQuery (SqlExpr (Value (Maybe Int)))
sumBar = from $ \bar -> do
return (avg_ (sum_ (bar ^. BarBar)))
SELECT AVG(SUM("bar"."bar"))
FROM "bar"
MySQL/MariaDB do not support the || infix string concatenation operator, only the Concat function, leading test:
text functions like, (%) and (++.) work on a simple example
To fail.
Can't use an existing Value in a delete where_
I found the test case below very confusing. As far as I could tell, the select query returned a Value Key, which should have been the type needed in the delete where_.
However this caused a error:
Couldn't match type `KeyBackend
SqlBackend (CachedKeyGeneric SqlBackend)'
with `Value (KeyBackend backend0 (CachedKeyGeneric backend0))'
The fix seems to be to unpack the Value, and re-pack it with val inside the where_ clause. (Specifically, replace the first k2 with Value k2, and the second k2 with val k2.)
Am I missing something in the documentation that explains how to do this, or is the inability to unify these types a bug?
{-# LANGUAGE QuasiQuotes, TypeFamilies, GeneralizedNewtypeDeriving, TemplateHaskell,
OverloadedStrings, GADTs, FlexibleContexts #-}
import Database.Persist.TH
import Database.Persist.Sqlite (runSqlite)
import Control.Monad.IO.Class (liftIO)
import Control.Monad
import Database.Esqueleto hiding (Key)
share [mkPersist sqlSettings, mkSave "entityDefs", mkMigrate "migrateAll"] [persistLowerCase|
CachedKey
key String
UniqueKey key
deriving Show
AssociatedFiles
key CachedKeyId Eq
file FilePath
UniqueKeyFile key file
deriving Show
|]
main :: IO ()
main = runSqlite "foo.db" $ do
runMigration migrateAll
forM_ [1..30000] $ \i -> do
k <- insert $ CachedKey (show i)
liftIO $ print k
insert $ AssociatedFiles k (show i)
[(k2)] <- select $ from $ \k -> do
where_ (k ^. CachedKeyKey ==. val (show i))
return (k ^. CachedKeyId)
liftIO $ print (2, k2)
delete $ from $ \f -> do
where_ (f ^. AssociatedFilesKey ==. k2)
There is a known issue with Hackage that it does not build documentation for projects very well anymore. There is also a workaround. I think making the documentation available is very helpful for new people trying to use the library, and I encourage you to use the workaround to upload documentation.
I'd like to concat_ two strings that may be NULL, but I can't figure out how.
See code snippet at http://lpaste.net/102363
At least in PostgreSQL it is allowed to have NULL values as input for concat-function. NULL-values will be ignored.
There are SqlSelect instances for up to 16 fields. I'd like to have more, but Database.Esqueleto.Internal.Sql does not export the functions (e.g. sqlSelectCols) of the class SqlSelect that seem to be needed to derive an instance.
It is no longer "extremely hard to do a type-safe JOIN" in persistent - it uses esqueleto by design!
So please remove the second paragraph of the package description, and replace it with just this sentence:
esqueleto provides type-safe JOIN queries for persistent.
Have you considered adding support for SELECT DISTINCT ON? See here for an example.
I've found a common pattern in my programming with Esqueleto, and I'm curious if it would be possible to fold generic solution into Esqueleto. I would be willing to do this, with some guidance (I'm not sure if it's even possible, hence this rfc)
Consider a multi-to-multi Persistent model defined by:
Person
name Text
Committee
name Text
PersonCommittee
personId PersonId
committeeId CommitteeId
I might also have a "view" layer, like so:
data PersonView = PersonView { person :: Person, committees :: [Committees] }
One common task in my day-to-day work is to populate such views. A query might look something like:
\pid -> (select $ from $ \(person `InnerJoin` pxc `InnerJoin` committee) -> do
on (committee ^. CommitteId ==. pxc ^. PersonCommitteeCommitteeId)
on (pxc ^. PersonCommitteePersonId ==. person ^. PersonId)
where_ (person .^ PersonId ==. val pid))
return (person, committee)
In order to populate a PersonView, I have to "manually" perform a group by on the Haskell side -- pull the head out of the result set and fmap to aggregate the committee field into a list. It occurs to me that I will be doing this a lot, and that this could generalize to other monoidal functors.
Comments? Am I just approaching this the wrong way?
It can be used to coerce a subquery's result set into an array
in Postgres:
subArray_select :: (PersistField a, Esqueleto query expr backend) => query (expr (Value a)) -> expr (Value [a])
subArray_select query = unsafeSqlFunction "array" . veryUnsafeCoerceSqlExprValueList $ subList_select query
http://blog.felipe.lessa.nom.br/?p=68#comment-125
It's going to be hard to avoid clashing with ==
, but we could at least choose something different for ^.
and ?.
. Perhaps !
and ?!
?
See http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
I'd like to extract subfields from date time values. It does not seem to be possible from unsafeSqlFunction . I'll see if I can come up with a pull request.
I'm not sure if this is a good or bad idea, we'd have to try it out. See http://blog.felipe.lessa.nom.br/?p=68#comment-125.
I have two tables defined thus in config/models:
User
ident Text
password Text Maybe
customer CustomerId
privilege Int
UniqueUser ident
Address
local Text
suffix Text Maybe
domain LocalId
type Int default=0
user UserId Maybe
extra Text Maybe
I can easily write an inner join in esqueleto:
select $
from $ \(a `InnerJoin` u) -> do
on (a ^. AddressUser ==. just (u ^. UserId))
where_ (...)
return (a, u)
But I also want to see the rows of the address table where the user field is NULL. In SQL, it's a snap (yeah, user
may have been a poor choice of name):
select * from address a left outer join "user" u on a.user = u.id where ...;
But I cannot for the life of me write that outer join in Esqueleto. This compiles:
from $ \(a `LeftOuterJoin` u) -> do
on (a ^. AddressUser ==. just (u ^. UserId))
but gives a run-time error: PersistMarshalError "field ident: Unexpected null"
I feel that this ought to work:
from $ \(a `LeftOuterJoin` u) -> do
on (a ^. AddressUser ==. u ?. UserId)
with the LHS acquiring a Maybe
from the type of the field, and the RHS from the ?.
operator, but:
Couldn't match type `Entity User' with `User'
...
Couldn't match type `Maybe' with `Entity'
...
And I've tried every combination of just
, ?.
, joinV
, etc that I can think of, all to no avail.
At this point I really can't tell if there's actually a bug here, or just a gap in my understanding. Can Esqueleto express that SQL query?
I'm using esqueleto-1.4, postgresql-9.2.6.
Hello!
Can I express a SELECT query with "FOR UPDATE"/"LOCK IN SHARED MODE" options to be able to lock the rows in persistent / esqueleto until the end of transaction? This could be useful.
Thank you.
I have a table EntryProperty
which implements many-to-many relation between Entry
and Property
.
Entry
…
Property
…
EntryProperty
entry EntryId
property PropertyId
value Double
UniqueEntryProperty entry property
I need to do queries with INNER JOIN
s between one Entry
and some EntryProperty
s (zero to five), like this:
SELECT entry FROM entry AS e
INNER JOIN entry_property AS p1 ON p1.property = p_1 AND p1.entry = e.id AND p1.value > v_1
INNER JOIN entry_property AS p2 ON p2.property = p_2 AND p2.entry = e.id AND p2.value = v_2
… WHERE …
My first solution looks like so (yesod get request handler):
getEntriesWithPropertiesR from cnt props =
respondSourceDB "application/json" $
(E.selectSource $ fromProps props)
=$ awaitForever getEntry $= streamJSONArray
where
fromProps [] = E.from $ queryEntry
fromProps (p_1:[]) =
E.from $ \(e `E.InnerJoin` p1) ->
queryProp p_1 e p1 >>
queryEntry e
fromProps (p_1:p_2:[]) =
E.from $ \(e
`E.InnerJoin` p1
`E.InnerJoin` p2) ->
queryProp p_1 e p1 >>
queryProp p_2 e p2 >>
queryEntry e
fromProps (p_1:p_2:p_3:[]) =
E.from $ \(e
`E.InnerJoin` p1
`E.InnerJoin` p2
`E.InnerJoin` p3) ->
queryProp p_1 e p1 >>
queryProp p_2 e p2 >>
queryProp p_3 e p3 >>
queryEntry e
fromProps (p_1:p_2:p_3:p_4:[]) =
E.from $ \(e
`E.InnerJoin` p1
`E.InnerJoin` p2
`E.InnerJoin` p3
`E.InnerJoin` p4) ->
queryProp p_1 e p1 >>
queryProp p_2 e p2 >>
queryProp p_3 e p3 >>
queryProp p_4 e p4 >>
queryEntry e
fromProps (p_1:p_2:p_3:p_4:p_5:_) =
E.from $ \(e
`E.InnerJoin` p1
`E.InnerJoin` p2
`E.InnerJoin` p3
`E.InnerJoin` p4
`E.InnerJoin` p5) ->
queryProp p_1 e p1 >>
queryProp p_2 e p2 >>
queryProp p_3 e p3 >>
queryProp p_4 e p4 >>
queryProp p_5 e p5 >>
queryEntry e
queryProp (PropCond prop_id prop_cond) e p = do
E.on $ p E.^. EntryPropertyProperty E.==. E.val prop_id
E.&&. p E.^. EntryPropertyEntry E.==. e E.^. EntryId
E.&&. p E.^. EntryPropertyValue `propCond` prop_cond
queryEntry e = do
E.orderBy [E.desc $ e E.^. EntryCreated]
E.limit $ fromIntegral cnt
E.offset $ fromIntegral $ from * cnt
return e
getEntry = …
propCond = …
This works perfect but only with zero and one entry_property.
For two and more it throws an sql error:
Unknown column 'entry_property2.property' in 'on clause'
Esqueleto version is 2.1.1
Suppose I have some queries which aggregate
sumFoo :: SqlQuery (SqlExpr (Value (Maybe Int)))
sumFoo = from $ \foo -> do
return (sum_ (foo ^. FooFoo))
sumBar :: SqlQuery (SqlExpr (Value (Maybe Int)))
sumBar = from $ \bar -> do
return (sum_ (bar ^. BarBar))
[EDIT: corrected a typo in my own code above]
and then I join them, supposedly after the aggregation has been performed.
join = do
f <- sumFoo
b <- sumBar
return (f, b)
I would expect the generated SQL to be equivalent to this
SELECT "foo", "bar"
FROM (SELECT SUM("foo"."foo") FROM "foo"),
(SELECT SUM("bar"."bar") FROM "bar")
However instead I get
SELECT SUM("foo"."foo"), SUM("bar"."bar")
FROM "foo", "bar"
This seems very odd to me, and an impediment to composability. Is this really the intended semantics of those queries?
Joins that require testing for a NULL id don't appear to be possible. For example, from http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS NULL
written as
select $
from $ \(ta `LeftOuterJoin` tb) -> do
on (ta ^. TableAName ==. tb ^. TableBName)
where_ (tb ^. TableBId ==. val Nothing)
return ta
would give the error
Couldn't match expected type `KeyBackend
SqlBackend TableA'
with actual type `Maybe a0'
In the first argument of `val', namely `Nothing'
In the second argument of `(==.)', namely `val Nothing'
My high-level problem is that I need to query the same table twice, but can't figure out (even in raw SQL, not because of any limitation in Esqueleto) how to do it using a top-level self-JOIN
. Instead I'm using subqueries.
I haven't given any thought to whether there's a simpler example that would illustrate the problem, but here goes. I have the following query:
SELECT COUNT(*)
FROM link_vote as lvOuter
WHERE lvOuter.link_id = 69
AND lvOuter.id = ( SELECT id
FROM link_vote as lvInner
WHERE lvInner.link_id = 69
AND lvInner.voter = lvOuter.voter
ORDER BY lvInner.cast_time
DESC
LIMIT 1
)
AND type = 'Upvote'
;
It results in the number of users whose most-recently-cast vote had type 'Upvote'.
I'm trying thusly to generate a comparable query using Esqueleto:
uvCtResult <- runDB $ select $ from $ \lvOuter -> do
where_ (lvOuter ^. LinkVoteLinkId Esql.==. val linkId)
where_ (lvOuter ^. LinkVoteType Esql.==. val Upvote)
where_ (lvOuter ^. LinkVoteId Esql.==. (sub_select $ from $ \lvInner -> do
where_ (lvInner ^. LinkVoteLinkId Esql.==. val linkId)
where_ (lvInner ^. LinkVoteVoter Esql.==. lvOuter ^. LinkVoteVoter)
orderBy [ desc $ lvInner ^. LinkVoteCastTime ]
limit 1
return $ lvInner ^. LinkVoteId))
return $ Esql.count (lvOuter ^. LinkVoteId)
Unfortunately, this generates the following SQL:
SELECT COUNT(link_vote.id)
FROM link_vote
WHERE (link_vote.link_id = 69)
AND ( ( link_vote.type = 'Upvote' )
AND ( link_vote.id = ( SELECT link_vote.id
FROM link_vote
WHERE (link_vote.link_id = 69)
AND (link_vote.voter = link_vote.voter)
ORDER BY link_vote.cast_time
DESC
LIMIT 1
)
)
)
;
Note that the tables aren't aliased. This changes the semantics and, of course, the result.
If there were a way to assign an unique alias to each table identifier in my Esqueleto code (e.g. I'd want to use an alias for lvOuter
and another for lvInner
), I think that would fix me up.
Thanks for any help!
Suppose you have following tables a - b - c, where b contains reference to a in a_id, and c contains reference to b in b_id and we want to extract all rows from a and those rows from c which are referenced to a via b if any. It's possible with sql code like
SELECT a., bc. FROM a LEFT OUTER JOIN (b JOIN c ON b.c_id = c.id) bc ON a.id = bc.a_id;
Field names in sqlite will be a bit messed up, but it works. Important part here are parenthesis around inner join.
Following code in esqueleto typechecks fine
from $ ( a LeftOuterJoin
(b InnerJoin
c))
but generated sql code contains no parenthesis - since parenthesis provided in the haskell source signify only order of nesting, but not sql nested joins and because of that resulting sql query will contain only those of a which have non-null refecences to c.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.