Slick join two tables and get result of both

Question

I have a Many to Many relationship setup like this:

Person <-> PersonField <-> Field

Now I want to query not only all the fields of a Person (I can do that), but a joined version of PersonField with Field of a Person. (I want to query/retrieve the Information in the Pivot/Intermediate Table "PersonField" as well!)

Person:

case class Person(id: Long, name: String)
{
   def fields =
   {
       person <- Persons.all.filter(_.id === this.id)
       field <- person.fields
   } yield field
}

class Persons(tag: Tag) extends Table[Person](tag, "persons")
{
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")

    def * = (id, name) <> (Person.tupled, Person.unapply)
    def fields = PersonFields.all.filter(_.personID === id).flatMap(_.fieldFK)
}

object Persons
{
    lazy val all = TableQuery[Persons]
}

Field:

case class Field(id: Long, name: String, description: Option[String])

class Fields(tag: Tag) extends Table[Field](tag, "fields")
{
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def description = column[Option[String]]("description")

    def * = (id, name, description) <> (Field.tupled, Field.unapply)
}

object Fields
{
    lazy val all = TableQuery[Fields]
}

PersonField:

case class PersonField(id: Long, personID: Long, fieldID: Long, value: String)

// TODO add constraint to make (personID, fieldID) unique
class PersonFields(tag: Tag) extends Table[PersonField](tag, "person_field")
{
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def personID = column[Long]("person_id")
  def fieldID = column[Long]("field_id")
  def value = column[String]("value")

  def * = (id, personID, fieldID, value) <> (PersonField.tupled, PersonField.unapply)

  def personFK = foreignKey("person_fk", personID, Persons.all)(_.id)
  def fieldFK = foreignKey("field_fk", fieldID, Fields.all)(_.id)
}

object PersonFields
{
  lazy val all = TableQuery[PersonFields]
}

Now to query all the fields of a Person I have a little helper-class:

def getFields(p: Person): Future[Seq[Field]] =
{
    val query = p.fields
    db.run(query.result)
}

So I can do

val personX ...

personX.onSuccess
{
    case p: Person =>
    {
        val fields = helper.getFields(p)
        fields.onSuccess
        {
            case f: Seq[Field] => f foreach println 
        }  
    }
}

Now each field of personX gets printed to the console. Works like a charm.

The thing is, I want to get the PersonField as well (with the Field)!

So I tried the following changes (among others that didn't work, which I can't remember)

In Person:

def fields =
{
    for
    {
        person <- Persons.all.filter(_.id === this.id)
        field <- person.fields join Fields.all on (_.fieldID === _.id)
    } yield field
}

In PersonS

def fields = PersonFields.all.filter(_.personID === id) // No flatMap here!

then getFields(p: Person) looks like this:

def getFields(p: Person): Future[Seq[(PersonField, Field)]]

but

personX.onSuccess
{
    case p: Person =>
    {
        val fields = helper.getFields(p)
        fields.onSuccess
        {
            case f: Seq[(PersonField, Field)] => f map(f => println(f._1)} 
        }  
    }
}

gives me nothing, so I guess my join must be wrong. But what exactly am I doing wrong?

Ebeneser

Did you try with a unit test to inject a person, a field, and a personfield with the right ID and see that the query returns no result? – Cassiopeia

I tried it manually and verified that the tables are sane (as expected), so: partially yes ;) – Ebeneser

Could you give a fully working gist? – Cassiopeia

I provided all the necessary/used code above, there is nothing special going on in the background. – Ebeneser

your code doesn't even compile, the fields method in the Person class is missing a "for". Happy to help if we can get the code to compile :) – Cassiopeia

Answer 1

You can join all three, then yield the result

for {
      ((personField, person), field) <- PersonFields.all join Persons.all on (_.personId === _.id) join Fields.all on (_._1.fieldId === _.id)
      if person.id === this.id
    } yield (personField, person, field)

(I am not sure I got exactly what you were trying to get out of the query, so you can just edit the yield part )

Halfback

I've wasted days because the searches (at least the ones that I found to date) for how to join two tables with Slick all said it could not be done and to use a for comprehension instead. None of those take into account sorting and limits etc which have to be a single SQL statement. Really glad I accidentally discovered this while trying to work through rconv for multiple case classes. – Digitate