Skip to content

Instantly share code, notes, and snippets.

@dustingetz

dustingetz/list-eligible-projects.scala

Forked from anonymous/list-eligible-projects.scala
Last active December 15, 2015 16:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dustingetz/5288813 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dustingetz/5288813 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. dustingetz revised this gist Apr 2, 2013. 1 changed file with 4 additions and 3 deletions.
    7 changes: 4 additions & 3 deletions list-eligible-projects.scala
    View file
    Original file line number Diff line number Diff line change
    @@ -17,9 +17,10 @@ def listEligibleProjects(userId: Int)(implicit session: Session): Seq[ProjectInf
    o <- p.owner

    } yield p.id ~ p.name ~ p.created ~ o.id ~ o.username
    logSQL(q.selectStatement)
    listProjects(q.run.distinct)
    }

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



  2. dustingetz revised this gist Apr 2, 2013. 1 changed file with 33 additions and 25 deletions.
    58 changes: 33 additions & 25 deletions list-eligible-projects.scala
    View file
    Original file line number Diff line number Diff line change
    @@ -1,28 +1,36 @@
    // projects for which the given user has a matching skill but is not a member.
    // 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")
    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, us) <- ProjectSkills innerJoin UserSkills on
    // ((ps, us) => ps.skillId === us.skillId && us.userId === userId)) yield ps.projectId
    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
    // 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")
  3. @invalid-email-address Anonymous created this gist Apr 1, 2013.
    28 changes: 28 additions & 0 deletions list-eligible-projects.scala
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,28 @@
    // projects for which the given user has a matching skill but is not a member.
    // 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")
    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, us) <- ProjectSkills innerJoin UserSkills on
    // ((ps, us) => ps.skillId === us.skillId && us.userId === userId)) yield ps.projectId
    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)
    }