Mapping big table

[Ryan Bair]

I have a use case with a very wide table, about 40 columns. This is
causing mapping issues as its well over the 22 item max tuple size.

To alleviate the issue, we would like to map the table to nested case
classes. This will also help manageability of the object as a bonus.
We're developing against 0.10-SNAPSHOT which has the nested tuples
code.

Is this possible? If not, is there another sensible approach? I'd
prefer to avoid splitting my tables if possible.

Thanks

[Noel Weichbrodt]

Ryan, I had the same problem (a 43 column wide table)[1]. My eventual solution was to divide the table into two case classes & objects, giving the second table a foreign key pointing to the first table's id (which was the same column in both). HTH, and hopefully this case will be handled in the replacement for scalaquery.

--Noel Weichbrodt

[1] https://groups.google.com/d/topic/scalaquery/94P4ISzJYOk/discussion

[Ryan Bair]

Thanks. I ended up restructuring my tables which I wasn't too terribly
happy with.

Did you happen to take a look at the resulting SQL generated from your
solution? Specifically, was SQ smart enough to eliminate the join? The
Postgres (9.1) planner doesn't seem to be smart enough to eliminate
the join if it's passed through.

[Chung Onn Cheong]

I just started to use Slick today and I find the inability to declare more than 22 columns is a serious limitation. Also I wonder why Slick didn't attempt to derive the columns types from the columns defined within the table. With the same token, I wonder why  the projection "*" method  is necessary ? As the method could be derived from all the defined columns. 

I find having to declare the Column type in the Table's tuple and declare again with the Table methods is against the DRY principle. The problem really is for table with many columns, one have to ensure the position of each type matches and this really a pain especially I am starting out to define a new table and need to adjust the columns constantly. And each time I do this, I need to modify 3 places - the Table's tuple, the columns and the projection methods.

I am wondering if anyone is facing the same problem like mine?

- chungonn

[virtualeyes]

"I am wondering if anyone is facing the same problem like mine?"

heh, heh, well yes, I'd think we all are in the same boat to some
degree, type safety is not free...of boilerplate.

I would love to consolidate my mappings, maybe type providers will
help in this regard (or as @nafg suggests, improved migrations)

[Denis]

We ended up simply leaving tables intact and mapping first 22 columns on each table that has 22+ fileds. We also overrode * method and in our implementation of * we return all columns defined on a given table. So, then each table definition will extend from the custom table:

import your.package.db.{ ExtendedMySQLTable => Table }

object A_Tbl extends Table[(ATblRow)]("a_tbl") {

}

This allows calling ddl methods, eg. Table.ddl.create. In queries, if you need to return columns that are outside of 22 variable range, you simply yield them explicitly and then do case matching when mapping result-sets. I actually find it working pretty well.

Regards,

Denis.

[Cristi Boariu]

I read in a few places that they solved this 22 limit issue in August by using Nested Tuples... Does anyone has any example with this?

[Denis]

You needed nested tuples when yielding more then 22 columns in a query. So, yield clause would be yield ((a), b) now you can just do yield (a,b)

[Cristi Boariu]

Thanks Denis.

About your solution, do you have a small sample about this? "In queries, if you need to return columns that are outside of 22 variable range, you simply yield them explicitly and then do case matching when mapping result-sets."

Basically I am interested how can I yield columns if I not defined them with def column23 = ....