Slick query => duplicated result

Question

I got those models (simplified) :

User(id: Int, name: String)
Restaurant(id: Int, ownerId: Int, name: String)
Employee(userId: Int, restaurantId: Int)

when I use this query :

for {
    r <- Restaurants
    e <- Employees
    if r.ownerId === userId || (e.userId === userId && e.restaurantId === r.id)
  } yield r

which is converted to :

select x2."id", x2."owner_id", x2."name" from "restaurants" x2, "employees" x3 where (x2."owner_id" = 2) or ((x3."user_id" = 2) and (x3."restaurant_id" = x2."id"))

So far no problems. But when I insert those data :

User(1, "Foo")
User(2, "Fuu")
Restaurant(1, 2, "Fuu")
Restaurant(2, 1, "Foo")
Restaurant(3, 1, "Bar")
Employee(2, 2)
Employee(2, 3)

then try to query, I get this result :

List(Restaurant(1, 2, "Fuu"), Restaurant(1, 2, "Fuu"), Restaurant(2, 1, "Foo"), Restaurant(3, 1, "Bar))

I do not understand why Restaurant(1, 2, "Fuu") is present 2 times.

(I am using org.h2.Driver with url jdbc:h2:mem:play)

Am I missing something ?

Answer 1

Why you are getting 4 rows back

Cross joins are hard; what you are asking for with your SQL query is:

-- A Cartesian product of all of the rows in restaurants and employees
Employee.user_id | Employee.restaurant_id | Restaurant.name | Restaurant.owner_id
               2 |                      2 | Fuu             |                   2 
               2 |                      3 | Fuu             |                   2
               2 |                      2 | Foo             |                   1
               2 |                      3 | Foo             |                   1
               2 |                      2 | Bar             |                   1
               2 |                      3 | Bar             |                   1

-- Filtering out those where the owner != 2
Employee.user_id | Employee.restaurant_id | Restaurant.name | Restaurant.owner_id
               2 |                      2 | Fuu             |                   2 
               2 |                      3 | Fuu             |                   2

-- And combining that set with the set of those where the employee's user_id = 2
-- and the restaurant's ID is equal to the employee's restaurant ID
Employee.user_id | Employee.restaurant_id | Restaurant.name | Restaurant.owner_id
               2 |                      2 | Foo             |                   1
               2 |                      2 | Bar             |                   1

How to fix it

Make it an explicit left-join instead:

for {
    (r, e) <- Restaurants leftJoin Employees on (_.id = _.restaurantId)
    if r.ownerId === userId || e.userId === userId
} yield r

Alternately, use exists to make it even clearer:

for {
  r <- Restaurants
  if r.ownerId === userId ||
     Employees.filter(e => e.userId === userId && e.restaurantId === r.id).exists
} yield r