selectOneMany Yesod Persistent

3
votes

Im trying to get selectOneMany to work with limited success.

I have the following database models

User
 email Text
 verkey Text Maybe
 verified Bool
 password Text Maybe
 UniqueUser email
 date UTCTime
 deriving Show

Competence
 parent CompetenceId Maybe
 title Text
 UniqueCompetence title
 deriving Show Read

UserCompetence
 competence CompetenceId
 user UserId Eq
 UniqueUserCompetence user competence
 deriving Show Read

code from my handler

mmember <- runMaybeT $ do
  id <- MaybeT $ maybeAuth
  user <- MaybeT . runDB . get . entityKey $ id
  Entity memberId member <- MaybeT . runDB . getBy . UniqueMember . userEmail $ user
  competences <- lift . runDB . runJoin $ (selectOneMany (UserCompetenceUser <-.) userCompetenceUser)
  return (member,competences)

first of; I cant event get this code to run without adding a big type-signature, is this as it should be?

competences <- lift . runDB . runJoin $ (selectOneMany (UserCompetenceUser <-.) userCompetenceUser :: SelectOneMany SqlPersist (UserGeneric SqlPersist) (UserCompetenceGeneric SqlPersist))

secondly; what is the type of competences. Ideally i want to end up with [Entity competenceId competence].

Lastly; How would one add a filter to the above join so as to only acquire competences for 'user'?

2
haskellyesod

2 Answers

2
votes

I have already told you that it's not possible to avoid the extra type signature due to the fact that SelectOneMany uses type aliases that might not be inductive; i.e. your code tries to be more polymorphic than it should be, and the type signature is necessary to restrict that polymorphism.

You can avoid using the huge signature by constraining the types "from a different angle", e.g.:

return (member, competences :: [(Entity User, [Entity UserCompetence])])

Since the type aliases User and UserCompetence select a specific database backend, the types should be resolved appropriately.

Also, I just spoiled the type of competences for you. Hah! I hope that that's enough for you. If you want a many-to-many three-table join directly so that you can get all competences "owned" by an user, you should use prepared statements anyways because of the potential AST overhead, so check out the generic raw SQL interface which lets you do the traditional "SELECT * FROM foo WHERE bar = ?" [filteredBarValue] which you might be more used to working with; it doesn't offer the same type safety as the rest of persistent but I think that it's the easiest way to implement three-table joins in your case.

You can restrict the Users that are selected by modifying the result of oneFilterMany which has type OneFilterMany. Like so (haven't tested it, but should work):

let join = (selectOneMany (UserCompetenceUser <-.) userCompetenceUser)
           { somFilterOne = [... filters for User ...] }
competences <- lift . runDB . runJoin $ join
2
votes

Thanks to (lots of) help from dflemstr I ended up with

mmember <- runMaybeT $ do
  id <- MaybeT $ maybeAuth
  let user = entityVal id
  Entity memberId member <- MaybeT . runDB . getBy . UniqueMember . userEmail $ user
  let competenceStatement =
        Text.concat
        [ "SELECT ?? "
        , "FROM   competence,     user_competence "
        , "WHERE  competence.id = user_competence.competence_id "
        , "AND    ?             = user_competence.user_id"
        ]
  competences <- lift . runDB $ rawSql competenceStatement
                 [toPersistValue . entityKey $ id]
  return (member, competences :: [Entity Competence])