Haskell持久与Esqueleto联手 [英] Haskell Persistent Joins with Esqueleto

问题描述

我一直在研究Persistent库来与sql数据库进行交互。假设我有一个包含食谱的数据库，包含Recipe，Ingredient和RecIng表。

我对持久性的理解让我相信我应该像这：

  share [mkPersist sqlSettings，mkMigratemigrateAll] [persistLowerCase | 
食谱
标题字符串
成分
名称字符串
记录
记录食谱Id 
 ingId IngredientId 
数量Int 
 | ] 
  

通过这种方式，可以使用Esqueleto获得这些表之间的内连接： p>

 从$ \中选择$ 
（i`InnerJoin`ri`InnerJoin` r） - > （r ^。RecipeId ==。ri ^。RecIngIngId）
 on（i ^。IngredientId ==。ri ^。RegIngRecId）
 return（r，ri，i）
  

返回（Recipe，RecIng，Ingredient）的元组。

我真正想要的是一种查询食谱的方法，结果如下：

  data Recipe = Recipe {title :: String 
，ingredients :: [Ingredient] 
} 
 
 data成分=成分{name :: String 
，quantity :: Integer 
} 
  

除了定义一组额外的数据类型，并转换元组，是否有做这种事情的最佳做法？

解决方案

<1>对亚当的评论+1，这是正确答案IMO。

您可以采用的另一种方法是使用嵌入式实体，这实质上意味着对成分列表进行JSON编码到每个配方。但这将是糟糕的SQL设计，会导致更新表锁定问题，并且不能很好地扩展大量的成分。换句话说，有一个不匹配您希望使用的Haskell表示与将数据存储在数据库中的正确方式之间的关系。这并不意味着数据库格式或Haskell数据类型存在问题：这是逻辑上的区别。对这种差距的正确回应是拥有两种数据类型和一种智能的方式来在它们之间进行转换。

I've been looking into the Persistent library to interface with sql databases. Suppose I have a database containing recipes, with Recipe, Ingredient, and RecIng tables.

My (admittedly limited) understanding of persistent leads me to believe I should define the tables like this:

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Recipe 
    title String
Ingredient 
    name String
RecIng
    recId RecipeId
    ingId IngredientId
    quantity Int
|]

With this, it is possible to use Esqueleto to get the inner join between these tables:

select $
from $ \(i `InnerJoin ` ri `InnerJoin` r) -> do
    on (r ^. RecipeId ==. ri ^. RecIngIngId)
    on (i ^. IngredientId ==. ri ^. RegIngRecId)
    return (r, ri, i)

This returns tuple of (Recipe, RecIng, Ingredient).

What I really want is a way to query recipes that results in the following:

data Recipe = Recipe { title :: String
                     , ingredients :: [Ingredient]
                     }

data Ingredient = Ingredient { name :: String
                             , quantity :: Integer
                             }

Other than defining an additional set of data types, and converting the tuples, is there a best practice for doing this kind of thing?

解决方案

+1 to Adam's comment, it's the right answer IMO.

A separate approach you could take would be to use embedded entities, which would essentially mean JSON-encoding the list of ingredients into each recipe. But this would be bad SQL design, would cause table locking issues for updates, and wouldn't scale well for large numbers of ingredients.

In other words, there's a mismatch between the Haskell representation you want to work with and the right way to store the data in the database. This doesn't mean there's a problem with either your database format or your Haskell datatypes: it's a logical difference. The right response to this gap is to have two datatypes and an intelligent way to convert between them.

这篇关于Haskell持久与Esqueleto联手的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！