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'?
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 User
s 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