Correct way to do a "join" in persist with 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 the function

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

how would one go about getting all of the Player names for each of the associated sessions?

doing

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

gets the list of players; but it isn't associated with the sessions at all

Answer 1

There is limited join support in persistent at this time, and I believe it is SQL only.

I have a couple of helpers which I use for simple cases. They can be found here. It's not a true JOIN, it selects once per table then builds a list of tuples representing "joined" rows with an element from each.

Given your models and that helper, you should able to 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 cases where a record exists in all three tables will be returned (so it's an INNER JOIN), but you might want to pre-filter for efficiency too.

Answer 2

For future reference, for sql you can use esqueleto or rawSQL to do joins - see this answer Baffled by selectOneMany in Yesod

If you wanted to use a join, in esqueleto your query would look something like:

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

This would return a (Entity GamingSession, PlayerId) tuple