list-eligible-projects.scala

// projects for which the given user has a matching skill but is not a member.


def listEligibleProjects(userId: Int)(implicit session: Session): Seq[ProjectInfo] = {
  
  val q = for {
    
    p <- Projects if (! (p.id in {
      for (pm <- ProjectMembers if pm.userId === userId) yield pm.projectId
    }) && (p.id in {
      for {
        ps <- ProjectSkills
        us <- UserSkills if us.skillId === ps.skillId && us.userId === userId
      } yield ps.projectId
    }))

    o <- p.owner
  
  } yield p.id ~ p.name ~ p.created ~ o.id ~ o.username

  logSQL(q.selectStatement)
  listProjects(q.run.distinct)
}




// generates SQL:
select x2."id", x2."name", x2."created", x3."id", x3."username"
    from "projects" x2, "users" x3
    where ((not (x2."id" in (select x4."project_id" from "project_members" x4 where x4."user_id" = 834)))
          and (x2."id" in (
             select x5.x6 from (
                 select x7."project_id" as x6, x7."skill_id" as x8 from "project_skills" x7) x5
                     inner join (select x9."user_id" as x10, x9."skill_id" as x11 from "skillsets" x9) x12
                     on (x5.x8 = x12.x11) and (x12.x10 = 834))))
          and (x3."id" = x2."owner")