Composing queries

[Bill Atkins]

I'm having some trouble composing different query components into a
single Query. I'd like to create a set of traits (SoftDeletable,
HasName, SortedByName) that I can simply mix-in to Table objects to
add that behavior.

The ideal looks like:

abstract class BaseModel[Tuple <: Product,CaseClass](tableName:
String) extends Table[Tuple](tableName) {
def id = column[Int]("id", O.AutoInc, O.PrimaryKey)

def mapped: MappedProjection[CaseClass, TupleClass]

def allQuery = this.map(_.mapped)
final def all = database.withSession { implicit session: Session =>
allQuery.list() }

...
}

trait SoftDeletable[Tuple <: Product, CaseClass] extends
BaseModel[Tuple,CaseClass] {
def isActive = column[String]("is_active")

def * = super.* ~ isActive
def allQuery = /* here, I'd like to compose super.allQuery with a
filter that returns rows where isActive is true */
}

trait HasName[Tuple <: Product] extends Table[Tuple] {
def name = column[String]("name")

def * = super.* ~ name
}

trait SortedByName[Tuple <: Product] extends HasName[Tuple {
override def allQuery = super.allQuery /* compose somehow with (_ <-
Query orderBy name */
}

Can I do these kinds of things with ScalaQuery? The main sticking
points are:

1. How do I cleanly compose the filter in SoftDeletable.allQuery and
the sort in SortedByName.allQuery?

2. I need to repeat the long tuple declaration in every trait, which
becomes very unwieldy if a table has five or six columns. Is there
something I can do with type members to avoid having to do things
like:

case class Foo

class Foos[(Int,Int,Boolean,String), Foo] extends
Table[(Int,Int,Boolean,String)] with
SoftDeletable[(Int,Int,Boolean,String), Foo] with
SortedByName[(Int,Int,Boolean,String), Foo] with
HasName[(Int,Int,Boolean,String), Foo] {
}

Can I avoid all those repetitions?

[Harshad RJ]

On Monday, 4 June 2012 05:02:06 UTC+5:30, Bill Atkins wrote:

  case class Foo

  class Foos[(Int,Int,Boolean,String), Foo] extends
Table[(Int,Int,Boolean,String)] with
SoftDeletable[(Int,Int,Boolean,String), Foo] with
SortedByName[(Int,Int,Boolean,String), Foo] with
HasName[(Int,Int,Boolean,String), Foo] {
  }

 Can I avoid all those repetitions?

I am only beginning with ScalaQuery, but I think I can answer that from a Scala perspective.

You can use type aliases:

type Columns = (Int,Int,Boolean,String)

class Foos[Columns, Foo] extends Table[Columns]
    with SoftDeletable[Columns, Foo]
    with SortedByName[Columns, Foo]
    with HasName[Columns, Foo]

Btw, is there a way to take this further and define an alias for a list of types. Something like:

type CF = [Columns, Foo]

?

[virtualeyes]

This is an important question, one that does not have an easy answer,
AFAIK.

You can compose any query prior to invoking list, foreach, etc.

trait SortedByName[Tuple <: Product] extends HasName[Tuple {
override def allQuery =

for {
res <- super.allQuery
_ < Query orderBy name desc
} yield res
}

isActive would look like (provided isActive is Boolean):
for { res <- super.allQuery if res.isActive } yield res

@Harshad's type alias suggestion seems a good one. I have been
muddling by with case class + companion object:
case class User(
id: Int,
firstName: String,
lastName: String,
email: String,
password: String
)
object Users extends _Mapper[User]("user") {
...
}

where _Mapper just defines the id PK

I'm not sure what you gain through your attempt, however. You can do
things like:
val byBar = FooObject.createFinderBy(_.bar)
val fiveBars = byBar(someBar) take 5 // or in a for {...} compose
query as you like

In other words, there may be no need to go beyond basic DAO findAll,
findOne, create, update, etc. since we can compose any base query into
something arbitrarily complex & wonderful via for {} comprehensions --
I cannot believe the transformation in 1 project, had serious doubts
about SQ being able to handle some of the string-based queries, but to
my surprise, verbatim replication, and type safe to boot, incredible!