HowTo setup composite keys

From Lift

To replicate a schema set up like this:

CREATE TABLE FooBar 
( 
  id serial NOT NULL, 
  fooId long NOT NULL, 
  barId long NOT NULL, 
  FOREIGN KEY ( fooId ) REFERENCES Foo ( id ), 
  FOREIGN KEY ( barId ) REFERENCES Bar ( id ), 
  PRIMARY KEY ( id ) 
); 
CREATE UNIQUE INDEX index_name ON FooBar (fooId, barId); 

Create a model object like this:

class FooBar extends LongKeyedMapper[FooBar] { 
  def getSingleton = FooBar 
  def primaryKeyField = id 
  object id extends MappedLongIndex(this) 
  object foo extends MappedLongForeignKey(this, Foo) 
  object bar extends MappedLongForeignKey(this, Bar) 
} 

object FooBar extends FooBar with KeyedMetaMapper[Long, FooBar] { 
  override def dbIndexes = Index(foo, bar) :: super.dbIndexes 
...
}

Many-to-many relations

Michal Příhoda posted on March 15, 2008 to the liftweb group:

Let's say you have User model class, and want to express a m:n relationship with Entry model class, tj. each user can have multiple entries, as well as each entry can belong to multiple users. So you create third class that represents this relationship, for example UserEntry:

class UserEntry extends KeyedMapper[Long, UserEntry] {
...
        object user extends MappedLongForeignKey(this, User)
        object entry extends MappedLongForeignKey(this, Entry)
...

}

And then you can add the entries/users to the model classes:

class User extends MegaProtoUser[User] {
...
        object entries extends HasManyThrough(this, Entry, UserEntry, UserEntry.user, UserEntry.entry)
...
}

class Entry extends KeyedMapper[Long, Entry] {
...
        object users extends HasManyThrough(this, User, UserEntry, UserEntry.entry, UserEntry.user)
...
}

And as soon as you call apply or get on these properties, you get the List[Entry] or List[User] respectively, tj.

val userEntries : List[Entry] = user.entries()

But the only reasonable way to manage the relationship right now is by creating/deleting the UserEntry object directly

val userEntry = (new UserEntry).user(user).entry(entry).saveMe
UserEntry.find(userEntry.id).foreach(UserEntry.delete_!)

you can't call user.entries.append(entry) or the like. And because the corresponding list is defined as FatLazy, the database query runs exactly once on the first usage. So test like this will not work:

val user = new (User)....
val entry = new (Entry)...
val userEntry = (new UserEntry).user(user).entry(entry).saveMe
user.entries() must contain(entry) // this is the first call, it will pass
UserEntry.find(userEntry.id).foreach(UserEntry.delete_!)
user.entries() must notContain(entry) // this will fail, as the list is not reloaded
User.find(user.id).get.entries() must notContain(entry) // this will work as the user object is reloaded

By adding the reset method, there would be a way to define at least methods like this:

class User ... {
...
        def addEntry(e: Entry) = {
                (new UserEntry).user(this).entry(e).save
                entries.reset
        }

        def removeEntry(e:Entry) = {
                UserEntry.find(By(UserEntry.user, this.id), By(UserEntry.entry,  
e.id)).foreach(UserEntry.delete_!)
                entries.reset
        }
...

}

Which would make the usage pattern easier:

val user = new (User)...
val entry = new (Entry)...
user.addEntry(entry)
user.entries() must contain(entry) // The sql query is performed first time
user.removeEntry(entry)
user.entries() must notContain(entry) // The list has been reset, so the query is perfomed again