I’m having a problem creating one table that will allow me to use all the columns from 3 separate MySQL tables that are linked.
Basically, the problem is similar to the Person-City containers in the AddressBook tutorial, however instead of just replacing the city_id column with city_name using AddGeneratedColumn, I would like to be able to actually add both the name AND another column(i.e. town_id) in the city container to my main table, so I could keep expanding the table when components are linked.
Something like this:
1st Table: COUNTRY
ID | COUNTRY_NAME | CITY_ID (linked to CITY_ID)
2nd Table: CITY
ID | CITY_NAME | TOWN_ID (LINKED TO TOWN ID)
3rd Table: TOWN
ID |TOWN_NAME
Full Table:
ID | COUNTRY_NAME | CITY_ID (getting caption from CITY_NAME) | TOWN_ID (getting caption from TOWN_NAME)
=======================
I have the tables as 3 separate SQL Containers. Is there some straight forward way in which the 3 Containers could be combined into one SQL container, and then I could just pick apart the columns using AddGeneratedColumn to convert IDs to actual names.
How about creating a view on the database side that combines the 3 tables the way you need them
and then access the view in vaadin and link it to one single SQLContainer !?
Actually that is what I had before but it seems to be very inefficient this way.
For example, let’s say I want to add a table that will display a specific product and a specific manufacturer, where one product can have many manufacturers, and one manufacturer can have many products assigned to them.
PRODUCT | MANUF
TV | SONY
TV | SHARP
RADIO | SONY
RADIO | SHARP
1st problem: this will cause a lot of duplicate entries in both columns of the table, and the MANUF_IDs will go {1,2,3,4} instead of {1,2,1,2} as they should since we have duplicates.
2nd problem: Now let’s say I only want to add a new manufacturer. I do not know anything about the products they make and will add that later. If I have a completely separate table for manufacturers in my DB I can just add it there and then add products to the product table and link the two tables using the manufacturer ID.
I can do this with 2 tables no problem, it is just when I try to expand to more than 2 tables I don’t know how to access the reference key in the second table that points to the 3rd table. Basically addGeneratedColumn seems to only allow me to overwrite a prexisting column in the initial SQLcontainer that I set using setDataSource.
Unfortunately SQLContainers cannot be combined as such. Your options are to, as previous commenter already said, create a view in your database, use FreeformQuery (together with your implementation of FreeformStatementDelegate for write/lazyloading/etc support) with a query where all tables are JOINed in the way you need.
The third option is, as you probably noticed, use three different SQLContainers and manually handle the relationships. Some help for manually handling the relationships can be had from the addReference(), removeReference(), setReferencedItem(), getReferencedItemId() and getReferencedItem() methods in SQLContainer. In this case, maybe it would be possible to rethink the UI?
One final option might be for you to create a proxy-container that just calls into the three different SQLContainers for retrieving the items and properties. This would allow you to “combine” any number of SQLContainers into any row configuration you want. A read only container would probably be pretty straight forward, but write support might get trickier. But you sound like a smart guy, I bet you can get it working if you want to
GeneratedColumns need the propertyId to actually be present in the container, which means that you can only overwrite columns that are present. It’s a bit of a bummer with SQLContainer, since you cannot easily add properties to this container. If you go for the proxy-container you can easily add support for adding any number of generated columns.
