CODEWITHSUNDEEP
haskellyesod
Tero
Tero

Reputation: 185

Selecting rows referenced by another table in Yesod

Suppose I have the following Yesod (Database.Persist) database schema:

File
   path Text

Tag
   name Text

FileTag
   file FileId
   tag TagId
   UniqueFileTag file tag

What is the most convenient way in Yesod to select File records that are referenced by a given Tag record? Do I need to resort to custom SQL? I'm using PostgreSQL as the database backend.

Upvotes: 1

Views: 295

Answers (2)

Michael Snoyman
Michael Snoyman

Reputation: 31315

There is a module for handling one-to-many relationships, either as application-level joins or as a proper SQL join. These modules probably count as the most poorly documented aspect in the entire Yesod project, unfortunately. Longer term, we're hoping to improve the state of more complex SQL queries, but we don't have any actual code to show for this right now.

There are a few threads on the Yesod mailing list covering how to use these, here's one thread that popped up: https://groups.google.com/forum/#!msg/yesodweb/a4EAvPS8wFA/ClPuS94TRFwJ%5B1-25%5D . We really need to either improve the Haddocks or write a wiki page detailing the behavior better.

Upvotes: 0

dflemstr
dflemstr

Reputation: 26167

You can use custom SQL to solve this problem; I don't think Persistent offers a different solution, since it's not an ORM because it has to support non-relational backends like MongoDB.

You can implement a basic join like this:

let tagFileStatement =
  Text.concat
  [ "SELECT ?? "
  , "FROM  file,     file_tag "
  , "WHERE file.id = file_tag.file "
  , "AND   ?       = file_tag.tag"
  ]
files <- runDB $ rawSql tagFileStatement
         [toPersistValue theTagIdThatYouWantToLookupFilesFor]
files :: [Entity File]

Upvotes: 3

Related Questions