Join with a single case class

[schmitch]

Expected behavior

Actually it would be great to also have a way to join a table with a single case class.

Considering the following:

case class ShortCustomer(id: Long, name: String, street: String, zip: Int, city: String)

Obviously our "big customer" won't have the address data stored in his table:

CREATE TABLE customer(
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
address UUID NOT NULL
);
CREATE TABLE address(
id UUID PRIMARY KEY,
....
);

Workaround

The only workaround at the moment is creating a database view.

[fwbrasil]

@schmitch I don't follow, could you provide more context? Maybe an example of code that's not supported now and you'd like to have it supported.

[schmitch]

@fwbrasil pretty simple considering you have many small tables, as the one above. And now you want all of the values of the specific tables in a single case class. There are actually two ways of doing that and it feels akward.

Either join them with "Query" case classes and the yield into your case class or you can create a database view. It would be great if quill would also have a way generate the following:

SELECT p.var1, p.var2, p.var3 FROM (SELECT t.var1, c.var2, c.var3 FROM tada t INNER JOIN chacha c ON c.var1 = t.var2) p;

So that you can group smaller tables directly into a case class instead of using a view or query case classes

[jilen]

@schmitch You may query p and c separately like this

for {
  p <- db.run(getP)
  c <- db.run(getC(p.id))
} yield PC(p, c)

Then compose them into a big case class

[schmitch]

thats exactly what I written as a workaround in my second explanation.

[deusaquilus]

I just posted a similar question on the gitter channel, please let me know if this is the same thing as @schmitch mentioned:

Is there a way to return a query of an arbitrary case class from a nested block? I'm looking for something like this:

case class Person(...)
case class Address(...)
case class Contact(name:String, secondName:String, streetAddress:String)

val query1 = quote {
    for {
        p <- query[Person]
        a <- address[Address] if p.id == a.personFk
    } yield (Contact(person.name, person.lastName, address.street))
}

case class Company(...)
val query2 = quote {
    for {
        c <- query[Company]
    } yield (Contact(c.name, c.subtitle, c.streetAddress))
}

Slick has this notion of a CaseClassShape that allows you to yield/map to a arbitrary case class from any query which is a great source of abstraction. It let's you define and interface like so:

val filterContact = quote {
    (q:Query[Contact]) => q.filter(_.address like "51st Street%")
}

This is great for abstractions and composition! Anything you can map to a Contact be it a Person, Company or anything else, can use this filter and any subsequent functions...

val filtered1 = filterContact(query1)
val filtered2 = filterContact(query2)

For example a union:

val filteredUnion = filterContact(query1) ++ filterContact(query2)

Am I on point here? Any ideas how Quill could do this?

[mxl]

@deusaquilus That's close to what @schmitch was asking for.

Currently there is not no way in Quill to map multiple tables to single case class and I think it will add unnecessary complexity. Database view is a better approach for such cases. @getquill/maintainers WDYT?

Another workaround is to map tuple returned by ctx.run() to case class:

ctx.run(...).map((Contact.apply _) tupled)

[mosyp]

case class Person(id: Int, name: String)
case class Address(id: Int, personId: Int, street: String)
case class Contact(name:String, streetAddress:String)

val query1 = quote {
    for {
        p <- query[Person]
        a <- query[Address] if p.id == a.personId
    } yield (p.name, a.street)
}

case class Company(name:String, streetAddress:String)

val query2 = quote {
    for {
        c <- query[Company]
    } yield (c.name, c.streetAddress)
}

val m = ctx.run(query1 ++ query2)

println(m.string)

produces:

SELECT x._1, x._2 FROM ((SELECT p.name _1, a.street _2 FROM person p, address a WHERE p.id = a.person_id) UNION ALL (SELECT c.name _1, c.street_address _2 FROM company c)) x

Which is actually what are you looking for?

@getquill/maintainers In above example, if we would use as @deusaquilus suggested:

        a <- query[Address] if p.id == a.personId
    } yield Contact(p.name, a.street)

we would come up with the following error:

Found the following free variables: Contact.
Quotations can't reference values outside their scope directly. 

Hence, if Quill can handle tuples it should handle case classes as well, I consider this as free variables bug which is actually something that we can take a look and fix, wdyt?

[deusaquilus]

I definitely agree with @mentegy. If tuples work with this kind of functionality, shouldn't case classes also work?

[deusaquilus]

Also, @mxl I really feel like I need to follow up here. I absolutely hate database views since they can't take parameters, have no local scoping, and don't compose very well. The whole reason I took to learning Slick and Quill in the first place is because I hate database views so much! I have dealt with some databases with literally hundreds of them and it's a complete nightmare! The whole idea of being able to create multiple queries with different filtration/joining, assign these queries to typed-variables, and then use these variables downstream to compose bigger queries is what makes this system so attractive. There's no RBDMS system in existence that has the ability pass views as parameters into a UDF so any kind of abstraction mechanism is completely off the table. If I could reduce the amount of database views I rely on to zero, replacing them all with Quill/Slick Query objects, it would actually be the best possible situation.

[mxl]

@deusaquilus @mentegy I just thought a little bit and it seems that you are right. Such feature makes code more self-documented. So you have my 👍 .

[deusaquilus]

Awesome! I'd be happy to test out this feature.

[fwbrasil]

case class support within quotations doesn't seem trivial to implement. We'd need to create new AST types for case class instantiation and patter matching, but I think it'd be a good addition despite the complexity.

[deusaquilus]

So I started having a look around at the quotation mechanism. Inside the Parsing.scala... would it be possible to add a construct to convert case classes in a Map body to Quill-AST Tuples? That way AST changes aren't really necessary. If this is a horrible idea, please let me know. Any advice in general on what directions I should explore in order to implement this?

Edit: As an extension of this idea, why not think of the Tuple in the Quill AST as more of a generic Product-type-thing analogous to Slick's Product Node Shape? On this train of thought, you could also eventually support using things like Shapless HLists as query output. Does that make sense?

[fwbrasil]

@deusaquilus Thanks for looking into this! :)

I was reading the thread again. I don't understand how "mapping multiple tables to one class" is solved by supporting case classes within quotation. Maybe I'm missing something, or we just need to update the issue description.

Slick has this notion of a CaseClassShape that allows you to yield/map to a arbitrary case class from any query which is a great source of abstraction. It let's you define and interface like so:

val filterContact = quote { (q:Query[Contact]) => q.filter(_.address like "51st Street%") }

This is also possible in Quill through traits: https://scalafiddle.io/sf/lsmX57J/201

So I started having a look around at the quotation mechanism. Inside the Parsing.scala... would it be possible to add a construct to convert case classes in a Map body to Quill-AST Tuples? That way AST changes aren't really necessary. If this is a horrible idea, please let me know. Any advice in general on what directions I should explore in order to implement this?

That's a nice trick and it'd work for some scenarios because the query execution doesn't really need to know about the case classes. I think the problem would be the case where a case class field is accessed within the quotation:

val q = quote {
  val p = Person("John", 22)
  query[Person].filter(_.name == p.name)
}

p could come from other constructs like nested queries. We could workaround by using the field position in the case class, but I think the best path here is to create proper ASTs for case class usage. I imagine users will ask for other features with case classes within quotations and we'll end up having to implement the AST anyway. Maybe we could start only with case class creation (no pattern matching)?

[deusaquilus]

Hi @fwbrasil

I definitely agree that this issue should be renamed, probably more along the lines of "Allow Arbitrary Case Class Query Record in Addition to Arbitrary Tuple." or "Allow More Flexible Output Record Types from Query." From what I understood regarding @schmitch's original post you have two tables:

case class Customer(id:Int, name:String, address:Int, ...other, stuff, we, dont, care, about)
case class Address(id:Int, street:String, zip:Int, city:String, ...other, stuff, we, dont, care, about)

What he needs is a join that results in a specific domain object that represents a 'denormalized' customer with an address he calls 'short' because the fields "...other, stuff, we, dont, care, about" are not present.

case class ShortCustomer(id: Long, name: String, street: String, zip: Int, city: String)

Although, I would just call it ShortDenormalizedCustomer or AddressableCustomer which I'll do from now on:

case class AddressableCustomer(id: Long, name: String, street: String, zip: Int, city: String)

So the query would look something like this:

val addressableCustomers = query {
    for {
        c <- query[Customer]
        a <- query[Address] if c.address = a.id
    } yield AddressableCustomer(c.id, c.name, a.street, a.zip, a.city)
}

Instead of using tuples which is the only way it can be done now (according to my understanding)

val addressableCustomers = query {
    for {
        c <- query[Customer]
        a <- query[Address] if c.address = a.id
    } yield (c.id, c.name, a.street, a.zip, a.city)
}

The gist of the idea, is: let the client have more freedom to use the output-record-types they desire, irrespective of what the original schemas were. Typically in DBA land, we would have to create SQL views for this kind of mix-and-matching... and SQL views are the bane of my existence :)

Furthermore, this kind of flexible-record-output is really nice when you start dealing composing queries. For example, say I write a special kind of filter that does something with the AddressableCustomer table:

val addressableCustomersFilter = quote {
    (ac:Query[AddressableCustomer]) => (... complex business logic here...)
}

I can pretty easily compose it with my initial query:

val addressableCustomers = query {
    for {
        c <- query[Customer]
        a <- query[Address] if c.address = a.id
    } yield (c.id, c.name, a.street, a.zip, a.city)
}
val filteredCustomers = addressableCustomersFilter(addressableCustomers)

Otherwise my addressableCustomersFilter would need to use tuples in the type signature which is confusing and unclear:

val addressableCustomersFilter = quote {
    (ac:Query[(Int, String, String, Int, String)]) => (... complex business logic here...)
}

Bottom line, I'm pretty sure this is purely about output record types and not about embedding case classes into quoted blocks in the general sense. We should rename it appropriately.

That said, do you still think there should be a new AST type? If so, should only case-classes be supported or should there be hooks for other kinds of categorical products like HLists. I can conceivably imagine Shapeless fans wanting to do something like this:

val hlistRecord = query {
    for {
        a <- query[SomeTable]
        b <- query[SomeOtherTable] if a.foo == b.bar
    } yield (a.something :: a.somethingElse :: b.something :: b.somethingElse :: .... lots and lots of other records)
}

Of course, this more-general idea is much harder to do and I literally have no clue how to do it with quasi quotes during compile-time so any advice would be highly appreciated. What are your thoughts?

[deusaquilus]

Come to think of it, this thread has caused so much confusion that maybe I should open an entirely new issue. What do you guys think?

[deusaquilus]

Okay, I think this should do it. I implemented a new AST element like @fwbrasil suggested. @fwbrasil, @mxl, please have a look at #957 if you can. I couldn't figure out how to do this without calling the 'new' keyword (i.e. via the companion's apply method) so any advice would also be appreciated.