Play 2.0/Anorm

1.sql

# --- !Ups

CREATE TABLE users(
  email                     VARCHAR(255) NOT NULL PRIMARY KEY,
  name                      VARCHAR(255)
);

CREATE TABLE subjects(
  id                        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title                     LONGTEXT NOT NULL,
  summary                   LONGTEXT NOT NULL,
  imageUrl                  TEXT,
  author                    VARCHAR(255) not null,
  FOREIGN KEY (author) REFERENCES users(email) ON DELETE RESTRICT
);

CREATE TABLE events(
  id                        INTEGER NOT NULL AUTO_INCREMENT primary key,
  title                     LONGTEXT NOT NULL,
  summary                   LONGTEXT NOT NULL,
  start                     BIGINT,
  end                       BIGINT,
  place                     TEXT,
  imageUrl                  TEXT,
  author                    VARCHAR(255) NOT NULL,
  FOREIGN KEY (author) REFERENCES users(email) ON DELETE RESTRICT
);

CREATE TABLE events_subjects(
  subject_id                INTEGER NOT NULL,
  event_id                  INTEGER NOT NULL,
  PRIMARY KEY (subject_id, event_id),
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE subscribers(
  user_email                VARCHAR(255) NOT NULL,
  event_id                  INTEGER NOT NULL,
  PRIMARY KEY (user_email, event_id),
  FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE followers(
  user_email                VARCHAR(255) NOT NULL,
  subject_id                INTEGER NOT NULL,
  PRIMARY KEY (user_email, subject_id),
  FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

CREATE TABLE comments(
  id                        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  content                   LONGTEXT NOT NULL,
  postedAt                  BIGINT,
  event_id                  INTEGER,
  subject_id                INTEGER,
  author                    VARCHAR(255) NOT NULL,
  FOREIGN KEY (author) REFERENCES users(email) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

CREATE TABLE logs(
  id                        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  at                        BIGINT NOT NULL,
  kind                      VARCHAR(255) NOT NULL,
  user_id                   VARCHAR(255) NOT NULL,
  event_id                  INTEGER,
  subject_id                INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(email) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

# --- !Downs

DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS followers;
DROP TABLE IF EXISTS subscribers;
DROP TABLE IF EXISTS events_subjects;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS users;

Models.scala

package models

import play.api._
import play.api.db._
import play.api.Play.current

import anorm._
import anorm.SqlParser._

import java.util.Date

// -- Users

case class User(email: String, name: Option[String]) {
  
  lazy val myEvents: Seq[Event] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from events 
        join users on events.author = users.email 
        join subscribers on subscribers.event_id = events.id
        where subscribers.user_email = {myEmail} 
      """
    ).on(
      'myEmail -> email
    ).as(Event.withAuthor *)
  }
  
  lazy val mySubjects: Seq[Subject] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from subjects 
        join users on subjects.author = users.email 
        join followers on followers.subject_id = subjects.id
        where followers.user_email = {myEmail} 
      """
    ).on(
      'myEmail -> email
    ).as(Subject.withAuthor *)
  }
  
}

object User {

  val simple = {
     get[String]("users.email") ~
     get[Option[String]]("users.name") map {
       case email ~ name => User(email, name)
     }
  }

  def authenticate(email: String, name: Option[String] = None): User = DB.withConnection { implicit connection =>
    SQL(
      """
        INSERT INTO users (email,name) VALUES ({email},{name})
        ON DUPLICATE KEY UPDATE name = {name}
      """
    ).on(
      'email -> email,
      'name -> name
    ).executeUpdate()
    User(email, name)
  }

  def findByEmail(email: String): Option[User] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from users where email = {email}
      """
    ).on(
      'email -> email
    ).as(User.simple.singleOpt)
  }
  
}

// -- Subjects

case class Subject(id: Long, title: String, summary: String, imageUrl: Option[String], author: Option[User] = None) {
   
  lazy val followers: Seq[User] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from users 
        join followers on followers.user_email = users.email
        where followers.subject_id = {id}
      """
    ).on(
      'id -> id
    ).as(User.simple *)
  }
  
  lazy val comments: Seq[Comment] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from comments 
        join subjects on comments.subject_id = subjects.id
        where subjects.id = {id}
      """
    ).on(
      'id -> id
    ).as(Comment.simple *)
  }
  
}

object Subject {

  val simple = {
    get[Long]("subjects.id") ~
    get[String]("subjects.title") ~
    get[String]("subjects.summary") ~
    get[Option[String]]("subjects.imageUrl") map {
      case id~title~summary~image => Subject(id, title, summary, image)
    }
  }

  val withAuthor = {
    simple ~ User.simple map {
      case subject~author => subject.copy(author = Some(author))
    }
  }

  def findAll: Seq[Subject] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from subjects join users on subjects.author =  users.email
      """
    ).as(Subject.withAuthor *)
  }

  def findById(id: Long): Option[Subject] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from subjects join users on subjects.author =  users.email where subjects.id = {id}
      """
    ).on('id -> id).as(Subject.withAuthor.singleOpt)
  }
  
}

// -- Events

case class Event(id: Long, title: String, summary: String, place: String, start: Option[Date], end: Option[Date], imageUrl: Option[String], author: Option[User] = None) {
    
  lazy val subscribers: Seq[User] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from users 
        join subscribers on subscribers.user_email = users.email
        where subscribers.event_id = {id}
      """
    ).on(
      'id -> id
    ).as(User.simple *)
  }
  
  lazy val comments: Seq[Comment] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from comments 
        join events on comments.event_id = events.id
        join users on comments.author = users.email
        where events.id = {id}
      """
    ).on(
      'id -> id
    ).as(Comment.withAuthor *)
  }

}

object Event {

  val simple = {
    get[Long]("events.id") ~
    get[String]("events.title") ~
    get[String]("events.summary") ~
    get[String]("events.place") ~
    get[Option[Long]]("events.start") ~
    get[Option[Long]]("events.end") ~
    get[Option[String]]("events.imageUrl") map {
      case id~title~summary~place~start~end~image => Event(id, title, summary, place, start.map(new Date(_)), end.map(new Date(_)), image)
    }
  }

  val withAuthor = {
    simple ~ User.simple map {
      case event~author => event.copy(author = Some(author))
    }
  }
  
  val withAuthorAndSubjects = {
    withAuthor ~ Subject.simple map {
      case event~subject => (event,subject)
    }
  }

  def next: Option[Event] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from events where events.start > {now} order by start limit 0,1
      """
    ).on('now -> new Date().getTime()).as(Event.simple.singleOpt)
  }

  def findAll: Seq[Event] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from events 
        join users on events.author = users.email
      """
    ).as(Event.withAuthor *)
  }
  
  def findById(id: Long): Option[(Event,Seq[Subject])] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from events 
        join users on events.author = users.email 
        join events_subjects on events.id = events_subjects.event_id
        join subjects on subjects.id = events_subjects.subject_id
        where events.id = {id}
      """
    ).on('id -> id).as(Event.withAuthorAndSubjects *).groupBy(_._1).headOption.map {
      case (event, eventWithSubjects) => (event, eventWithSubjects.map(_._2))
    }
  }
  
}

// -- Comments

case class Comment(id: Long, content: String, date: Date, author: Option[User] = None)

object Comment {
  
  val simple = {
    get[Long]("comments.id") ~
    get[String]("comments.content") ~
    get[Long]("comments.postedAt") map {
      case id~content~postedAt => Comment(id, content, new Date(postedAt))
    }
  }

  val withAuthor = {
    simple ~ User.simple map {
      case comment ~ author => comment.copy(author = Some(author))
    }
  }

}

// -- Wall

case class Log(date: Date, kind: String, user: User, event: Option[Event], subject: Option[Subject])

object Log {
  
  val simple = {
    get[Long]("logs.at") ~
    get[String]("logs.kind") ~
    User.simple ~
    (Event.simple?) ~
    (Subject.simple?) map {
      case at~kind~user~event~subject => Log(new Date(at), kind, user, event, subject)
    }
  }
  
  def wall: Seq[Log] = DB.withConnection { implicit connection =>
    SQL(
      """
        select * from logs 
        join users on logs.user_id = users.email
        left join events on events.id = logs.event_id
        left join subjects on subjects.id = logs.subject_id
        order by logs.at desc
        limit 0, 20 
      """
    ).as(Log.simple *)
  }
  
}

Thank you, this is very informative. It even partially makes up for the poor Anorm documentation :-)