How to select the all rows in a table referencing a given table, provided that at least one row matches some criteria.

[teymour-aldridge]

Suppose I had a schema like

table! {
  user (id) {
     id -> Int4
  }
}

table! {
  post (id) {
     id -> Int4,
     user_id -> Int4,
     score -> In4
  }
}

I've tried something like

user::table
  .inner_join(post::table)
  .filter(post::score.ge(50))
  .load::<(User, Post)>(connection);

But from a database containing:

user { id: 1 }
post { id : 1, user_id: 1, score: 40 }
post { id : 2, user_id: 1, score: 60 }

That query (as expected) returns only

post { id : 2, user_id: 1, score: 60 }

rather than

post { id : 1, user_id: 1, score: 40 }
post { id : 2, user_id: 1, score: 60 }

I'd like to return all the posts belonging to a user, provided that at least one post has a score of greater than 50.

I'm using Postgres as my database.

[weiznich]

You need a subquery to solve this problem. Conceptually you want to write the following raw SQL query:

SELECT "user"."id", "post"."id", "post"."user_id", "post"."score"
FROM "user" INNER JOIN "post" ON "post"."user_id" = "user"."id"
WHERE "post"."user_id" IN (SELECT "post"."user_id" FROM "post" WHERE "post"."score" >= 50 and )

which can be literally translated into diesel's dsl as following:

user::table.inner_join(post::table)
    .filter(post::user_id.eq_any(post::table.select(post::user_id)filter(post::score.ge(50))))
    .load<(User, Post)>(&conn)?;

(Not sure if that query is actually supported or requires alias support which is not implemented yet.)

[teymour-aldridge]

Many thanks!