CODEWITHSUNDEEP
databasehaskellyesod
Aart Stuurman
Aart Stuurman

Reputation: 3598

Join multiple tables in yesod

Say I have the following models:

User
    ident Text
    password Text Maybe
    UniqueUser ident
    deriving Typeable
Payment
    timestamp UTCTime
    from UserId
    to UserId
    receiptId ReceiptId
ReceiptUser                     
    userId UserId
    receiptId ReceiptId
Receipt
    owner UserId
    money Int

I want to perform a query like this:

SELECT ReceiptUser.UserId, Receipt.ReceiptOwner, Receipt.Id, Receipt.Price
FROM ReceiptUser, Receipt, Payment
WHERE ReceiptUser.ReceiptId == Receipt.Id
      AND NOT (Payment.ReceiptId == Receipt.Id AND Payment.From == ReceiptUser.UserId AND Payment.To == Receipt.Owner)

Which is supposed to find all users who have not yet payed another user, plus the corresponding receipt information.

How could I express this in yesod persistent? It seems to me persistent only provides an interface for simple queries on one table.

Upvotes: 0

Views: 376

Answers (2)

Kutagh
Kutagh

Reputation: 21

Since you're doing the same assignment as I'm doing, I'd like to point you to the ManyToMany.hs file in the root of the starting framework. It contains two key functions: joinTables and joinTables3, which will join tables based on given keys. An example on using the latter is given in /Handler/Payment.hs under getPaymentsR. Using that, you should be able to make a join such that the result is [(Entity ReceiptUser, Entity Receipt, Entity Payment)].

Upvotes: 2

J. Abrahamson
J. Abrahamson

Reputation: 74354

Persistent's interface is very simple and often insufficient to implement even slightly complex SQL. Esqueleto is a library building atop Persistent's database machinery which gives you a much more SQL-like Haskell DSL that may allow you to express what you're looking for.

Upvotes: 2

Related Questions