为什么这两个 Slick 查询不等价? [英] Why are these two Slick queries not equivalent?

问题描述

由于 试图使 Slick 查询更具可读性，我有这个查询构造函数，它有效>

As a result of trying to make a Slick query more readable, I have this query constructor, which works

val q = Users.filter(_.id === userId) 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)
}

我认为这个是等效的，但不起作用:

And this one I thought would be equivalent, but doesn't work:

val q = Users.filter(_.id === userId) 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 second one appears to be returning a list of profiles that includes more than the target one.

为什么它们不一样?

等效"SQL(即此构造的目标)是:

The "equivalent" SQL (IE the goal of this construction) 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)

推荐答案

这里的问题是，slick 使用 LiteralNode(true) 作为默认连接条件. 所以第二个查询将产生如下所示的结果:

The problem here is, slick uses a LiteralNode(true) as the default join condition. So the second query will results in something looks like the following:

   select p.*, a1.*, b.*, a2.*
     from Users u 
     join People p on 1 = 1
left join Addresses a1 on 1 = 1
left join Businesses b on 1 = 1 
left join Addresses a2 on u.personId = p.id
                      and p.addressId = a1.id
                      and p.businessId = b.id
                      and b.addressId = a2.id

如您所见，每个连接表的连接条件所期望的所有条件实际上都是最后一个连接的连接条件的一部分.

As you can see, all the conditions that are expected to be a join condition for each of the joined tables, are actually a part of the join condition of the last join.

为了理解这将如何影响最终结果，让我们将问题简化如下:

To understand how this will affect the final result, let's simplify the problem as follows:

create temporary table A (id int primary key);

insert into A values (1), (2);

   select a1.id, a2.id, a3.id
     from A a1
     join A a2 on 1 = 1
left join A a3 on a1.id = a2.id
              and a2.id = a3.id;

在第一次连接时，a1 和 a2 被一个始终为真的条件连接，导致临时结果:

On the first join, a1 and a2 are joined by a condition that's always true, resulting in a temporary result of:

(1, 1)
(1, 2)
(2, 1)
(2, 2)

现在让我们考虑第二个连接.我们将 a1.id = a2.id 作为连接条件的一部分，但请记住连接条件用于决定如何从表 a3 中检索行，而不是过滤第一次连接的中间结果.而且我们这里做的是左连接，所以即使不满足连接条件，也会产生额外的a3行NULL.最终结果将是:

Now let's consider the second join. We have a1.id = a2.id as part of join condition, but remember the join condition is used to decide how to retrieve rows from table a3, not to filter the intermediate result of the first join. And we are doing a left join here, so an extra a3 row of NULL is generated, even if the join condition is not satisfied. The final result will be:

(1, 1, 1)
(1, 2, NULL)
(2, 1, NULL)
(2, 2, 2)

因此，您应该会看到更多意想不到的结果，最后一个左连接表的列为 NULL.

So, you're expected to see a lot more unexpected results with columns of the last left joined table being NULL.

这篇关于为什么这两个 Slick 查询不等价?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！