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?

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 :

• All users having purchased this items (join User and Items)
• All User having made a such comment (join User and Comments)
• All Items purchased by a user having made a such comment (Join User And Items And Comments)

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 itself
• or having an EDSL.

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.

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.

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"

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?

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.

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!

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.

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.

haskell/hackage-server#56 (comment)

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:

1) IN works for subList_select

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

2) sum function does not necessarily return the same type it receives

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))

jcristovao@161621c

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?

1. Focus the tests only on SQLite
2. Create a separate test file (plus eventually an associated script) for each DB

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.

3) random_ return type

A similar problem. No type signature change is required in the src code, but while SQLite returns an Integer, Postgres returns a double.

4) ASC and DESC with null fields

This affects tests:

1. select/orderBy works with two ASC field
2. select/orderBy works with one ASC and one DESC field
3. update works on a simple example
4. lists of values EXISTS works for subList_select

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?

5) IN works for insertSelect FAILED

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.

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-Entitys that get returned. It is a little cumbersome getting Value Int instead of Int after calling select. Thanks for any insights people can provide.

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 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.

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.

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?

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.

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.

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

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.

Have you considered adding support for SELECT DISTINCT ON? See here for an example.

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.

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

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'

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.

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)).

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.

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)

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.

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
        )

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

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?

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?

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

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:

1. Using sqlite bindings
2. Using select instead of selectDistinct
3. Only sorting on fields on 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.

I had to pull esqueleto recently and manually bump the text version, things seems to work for me so far.

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.

Please see http://stackoverflow.com/questions/16595892/composing-database-esqueleto-queries-conditional-joins-and-counting

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.

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.

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:

• select/where_ works with random_
• select/orderBy works with asc random_

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.

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 JOINs between one Entry and some EntryPropertys (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

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.

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.

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)

esqueleto 1.3.4.2 depends on hspec >=1.3 && <1.8, but 1.8.1.1 is now out.

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 ?!?

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