问题描述
此代码正在创建一个查询,用于在 Web 后端检索用户的个人资料.它创建了一个查询,将必要的信息组合成一个 DTO(它只是一个案例类),随后作为 JSON 发回.
This code is creating a query for use in retrieving a user's profile on a web back end. It creates a query that assembles the necessary information into a DTO (which is just a case class) that is subsequently sent back as JSON.
def getProfile(userId: Long)={
val q = for{
((((u,p),a),b), ba) <- filterById(userId) join
People on (_.personId === _.id) joinLeft
Addresses on (_._2.addressId === _.id) joinLeft
Businesses on (_._1._2.businessId === _.id) joinLeft
Addresses on (_._2.flatMap(_.addressId) === _.id)
}yield(p, a, b, ba)
db.run(q.result.headOption).map{ _.map{case(p,a,b,ba) =>
val business = b match {
case Some(b) => Some(dtos.Business(b.name, b.abn, b.adminFee, ba, b.id))
case _ => None
}
dtos.ProfileInfo(p, a, business)
}}
}
我已经包含了仅用于上下文的结果处理 (db.run(...)
).
I've included the result processing (db.run(...)
) for context only.
我正在寻找一种更具可读性的方式来表达查询构造.
I'm looking for a more readable way to express the query construction.
我阅读这篇文章的经验是等等,什么??... on (_._1._2.flatMap(_.addressId)
.... 那在做什么??为什么是 flatmap那里而不是这里:on (_._1._2.businessId
.这些实际上是直截了当的事情,但不要直截了当地阅读.
My experience reading this is "Wait, what?? ... on (_._1._2.flatMap(_.addressId)
.... what is that doing?? Why flatmap there and not here: on (_._1._2.businessId
. These are actually straight forwards things, but don't read at all straight fowards.
我正在寻找一种表达方式,不需要阅读此版本所需的扣除量.我必须推导出" _._1._2 是什么,以及为什么需要将其展平,这与等效的 SQL 无关.
I'm looking for a way of expressing this that doesn't require the amount of deduction needed to read this version. I have to "deduce" what _._1._2 is, and why it needs to be flattened, which I don't have to do with the equivalent SQL.
注意事项:
- 此代码来自我正在扩展的现有应用程序(不是我编写的).
- 用户、人员、地址、企业(显然?)是表格.
- 个人和企业都有地址.
- 用户有个人(*),人有事业
filterByUserId(userId)
基本上等价于Users.filter(_.id === userId
)等效的 SQL 是:
- This code comes from an existing application (not written by me) which I am extending.
- Users, People, Addresses, Businesses are (obviously?) the tables.
- People and Businesses have Addresses.
- Users have a Person(*), People have a Business
filterByUserId(userId)
is basically equivalent toUsers.filter(_.id === userId
)The equivalent SQL is:
select p.*, a1.*, b.*, a2.* from Users u
innerJoin People p on (u.personId == p.id)
leftJoin Addresses a1 on (p.addressId == a1.id)
leftJoin Businesses b on (p.businessId == b.id)
leftJoin Addresses a2 on ( b.addressId == a2.id)
推荐答案
你应该尝试这样的事情:
You should experiment with something like this:
val q = Users join People joinLeft Addresses joinLeft Businesses joinLeft Addresses on {
case ((((u, p), a), b), ba) => u.personId === p.id && p.addressId === a.flatMap(_.id) && p.businessId === b.flatMap(_.id) && b.flatMap(_.addressId) === ba.id
} map {
case ((((u, p), a), b), ba) => (p, a, b, ba)
}
另一种解决方案是在不使用理解的情况下进行连接,因此您不必使用下划线从元组中提取值:
The other solution would be to make joins without using for comprehension, so you wouldn't have to use underscores to extract values from tuples:
val q = Users join People on {
case (u, p) => u.personId === p.id
} joinLeft Addresses on {
case ((u, p), a) => p.addressId === a.id
} joinLeft Businesses on {
case (((u, p), a), b) => p.businessId === b.id
} joinLeft Addresses on {
case ((((u, p), a), b), ba) => b.flatMap(_.addressId) === ba.id
} map {
case ((((u, p), a), b), ba) => (p, a, b, ba)
}
您尚未提供数据的完整定义,因此我无法完全测试这些解决方案,但这应该能让您深入了解在 Slick 中定义连接的不同方式.如果这有帮助,请告诉我.
You haven't provided full definitions of your data so I wasn't able to fully test those solutions, but this should give you some insight into a different way of defining joins in Slick. Let me know if this was helpful at all.
这篇关于如何使用 Slick 3.0 编写可读的嵌套连接查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!