database - Yesod 从数据库中获取一对多关系

Question

我有两个表 product 和 category 定义如下:

Product
  category CategoryId
  name Text
  description Text
  price Int
Category
  name Text

我想从数据库中提取类型为 [(Category, [Product])] 的列表。我将如何在 Yesod 中解决这个问题？

我是全新的，我已经找了几乎一整天的时间来尝试找到一些东西来做这件事。

更新

这是我的家乡

$if null rows
  <p>No products
$else
  <div class="list-group menu">
    $forall (category, [products]) <- rows
      <div class="list-group-item">
        <h4 class="list-group-item-heading">#{categoryName category}

      $forall product <- products
       <div class="list-group-item">
        <div class="container-fluid">
          <div class="col-sm-10">
            <p>#{productName product} - #{productPrice product}</p>
          <div class="col-md-2 text-right">
            <div class="btn-group">
              <a class="btn btn-default btn-xs"><div class="glyphicon glyphicon-plus"></div></a>
              <a class="btn btn-default btn-xs"><div class="glyphicon glyphicon-minus"></div></a>

Answer 1

两种基本方法是:

1. 获取所有类别的列表，然后针对每个类别，获取产品列表
2. 使用 esqueleto 进行内部(或左)连接，然后使用 Haskell 中的排序和分组功能

第一种方法可能更简单。这是一个例子:

{-# LANGUAGE EmptyDataDecls             #-}
{-# LANGUAGE FlexibleContexts           #-}
{-# LANGUAGE GADTs                      #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE MultiParamTypeClasses      #-}
{-# LANGUAGE OverloadedStrings          #-}
{-# LANGUAGE QuasiQuotes                #-}
{-# LANGUAGE TemplateHaskell            #-}
{-# LANGUAGE TypeFamilies               #-}
import           Control.Monad.IO.Class  (liftIO)
import           Database.Persist
import           Database.Persist.Sqlite
import           Database.Persist.TH
import Data.Text (Text)
import Control.Monad (forM)

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Product
  category CategoryId
  name Text
  description Text
  price Int
  deriving Show
Category
  name Text
  deriving Show
|]

main :: IO ()
main = runSqlite ":memory:" $ do
    runMigrationSilent migrateAll
    populate
    res <- query
    liftIO $ print res

populate = do
    watches <- insert $ Category "Watches"
    insert_ $ Product watches "Rolex" "Fancy" 100
    insert_ $ Product watches "Limex" "Cheap" 2

    computers <- insert $ Category "Computers"
    insert_ $ Product computers "MacBook Air" "Apple" 1500

query = do
    cats <- selectList [] [Asc CategoryName]
    forM cats $ \(Entity catId cat) -> do
        products <- selectList
            [ProductCategory ==. catId]
            [Asc ProductName]
        return (cat, map entityVal products)