Using models for multiple one-to-many table relationships

Byron,

You can create a separate "modifying" model for each table and do

inserts/deletes/updates in a transaction across the tables with the

three models. That's really the easy part.

The somewhat more tedious part is the "join" models, but not hard to do.

You just have to decide what you need from the different tables and

quite possibly create multiple join models for different select scenarios.

So let's just run through a simple two table join model.

Create a SQL model. I usually name them based on the table joins. Let's

use a somewhat more real example:

table = customer

columns = custid, firstname, lastname, email

table = address

columns = addrid (seqid), custid (fk), type

('w'ork,'h'ome,'b'illing,'s'hipping,'o'ther), address1, address2, city,

state, zip

Suppose we want to select a customer and show a list of all his/her

addresses.

I would create a SQL model class (extending QueryModelBase of course)

called CustomerAddressJoinModel (this naming convention gets tough with

three or more tables, so you have to be creative).

In that model, I would create the fields just like any single table

model. You need only include the columns you need. Maybe you create one

join model that only has custid, lastname, firstname, type, city, zip.

OK, so we only cut out 2 columns, but you get the point. If you had a

table with lots of columns with data you rarely need to display and/or

update, then you can exclude them. Probably a slick way to exclude

dynamically, but this is straight forward.

Another join model could have all the columns of both tables.

So the trick is to set the static where criteria (using the

setStaticWhereCriteria method) in the model's constructor. I think the

sql model template has this in it.

setStaticWhereCriteria(customer.custid=address.custid);

Now you just set your criteria like any other model, and execute it.

For a 3 table join, you just set the static criteria like this:

tableA.aid=tableB.aid AND tableB.bid=tableC.bid

does that make sense?

You can also set the "modifying table" (setModifyingTable(table) - i

think that's right) in the model and inserts/updates/deletes will only

operate on that table within the model. Problem is maybe in the the

field binding in your views. You may want to select from one field, and

modify using another field. Can be done though, just not directly supported.

In the next release of JATO, you will be able to set a display fields

binding for read and write using different fields and even different models.

craig

Byron Servies wrote:

>Hi there!

>

>I have a database with tables that have one-to-many relationships. They

>are laid out with a standard join tables:

>

>AA_BBB_CC

>------

>a_ida_idb_idb_idc_id

>b_idc_id

>

>

>and has 1 A for n of B. In some cases, the B table also joins with

>another table for yet more information.

>

>My question: what technique should I use to organize a database of this

>type into JATO Models?

>

>I could create an AModel, for example, that would have a set of query

>field descriptors for the information relevant from tables A and B. But

>then how do I query table C when that information is necessary? Or,

>more to the point, create information in C when a new entry in B is created.

>

>Do the models use each other, or is that left up to the view bean (seems

>incorrect for data isolation).

>

>Any pointers appreciated,

>

>Byron

>

>

>

>

>To download the latest version of JATO, please visit:

><a href="http://www.sun.com/software/download/developer/5102.html">http://www.s un.com/software/download/developer/5102.html</a>

>

>For more information about JATO, please visit:

><a href="http://developer.iplanet.com/tech/appserver/framework/index.jsp">http: //developer.iplanet.com/tech/appserver/framework/index.jsp</a>

>

>

>

>

>

>

Guest at 2007-7-1 16:32:29 >