The correct way to make a "connection" while maintaining yesod

Question

Consider the models:

Player name Text nick Text email Text Maybe phone Text Maybe note Textarea Maybe minutes Int Maybe deriving Table name Text game Text pointsHour Int seats Int Maybe description Text Maybe deriving GamingSession start UTCTime end UTCTime Maybe player PlayerId table TableId seat Int Maybe deriving 

and function

 getGamingSessionsR :: Handler RepHtml getGamingSessionsR = do sessions <- runDB $ selectList [GamingSessionEnd ==. Nothing] [Desc GamingSessionTable] defaultLayout $(widgetFile ("opensessions")) 

how would I get all the player names for each of the related sessions?

do

 players <- runDB $ selectList [FilterOr . map (\(Entity _ s) -> PlayerId ==. (GamingSessionPlayer s)) $ sessions] [] 

Gets a list of players but it’s not associated with sessions at all

Answer 1

There is currently limited connection support in persistent, and I believe this is only SQL.

I have several helpers that I use for simple cases. They can be found here . This is not true JOIN, it selects once for the table, and then builds a list of tuples representing the "connected" rows with the element from each.

Given your models and this helper, you should do something like:

 records <- runDB $ do sessions <- selectList [] [] players <- selectList [] [] tables <- selectList [] [] return $ joinTables3 gamingSessionPlayer gamingSessionTable sessions players tables forM records $ \(session, player, table) -> do -- -- ... -- 

Only those cases where a record exists in all three tables will be returned (this is an INNER JOIN), but you can also pre-filter the performance.

Answer 2

In the future for sql, you can use esqueleto or rawSQL to combine - see this answer Baffled by selectOneMany in Yesod

If you want to use a connection, in esqueleto your request will look something like this:

 select $ from $ \(gamingSession `InnerJoin` player) -> do on (gamingSession ^. GamingSessionPlayer ==. player ^. PlayerId) where_ $ isNothing $ gamingSession ^. GamingSessionEnd orderBy [asc (gamingSession ^. GamingSessionTable)] return (gamingSession, player ^. PlayerId) 

This will return a tuple (Entity GamingSession, PlayerId)