My OperaHow To Create a Super-Intersection View
Friday, May 4, 2007 9:37:49 AM
When you use ADF Business Components, you can create them from database tables. This is an extremely useful feature and helps map foreign key relationships. It also means that for a many-to-many relationship with an intersection table, you can easily extract all children of a specific parent.
One use case that appears quite regularly with large database schemas is not covered by this method however. That is when you have a many-to-many relationship with more than one table inbetween them. A diagram of this situation is shown below. In this case you may want to retrieve a list of all the "grandchildren" of a "grandparent". That is, all the children of all the children of an item. In the example below, you may want to find all the books in all the collections for one library.
As it stands, in ADF you would need to use the LIBRARY_COLLECTIONS intersection to get a list of collection IDs and then apply that as a view criteria on the COLLECTION_BOOKS table (e.g. WHERE COLLECTION_ID IN (12, 23, 65, 32)). You would then have a list of the book IDs.
This is not the best solution, nor a very desireable one. A much better way would be to design a business component that represents an imaginary "super" intersection table, encompassing all three tables between LIBRARIES and BOOKS.
First you need to design an entity object to hold the needed attributes. Create a new entity object.
Call it LibraryBooks, and at first map it to the LIBRARY_COLLECTIONS intersection table. This is simply so that there is some database table that is referenced. Plus it helps in mapping the first attributes needed.
On the next step the wizard automatically adds all the columns from the selected table (in this case LIBRARY_ID and COLLECTION_ID). You want to keep all the foreign keys and discard any other columns. In this case we're keeping both columns as they are foreign keys and will help in creating a primary key later on.
At this point you will also need to add some attributes. Use the New... button rather than the New from Table... one. Add columns for the foreign keys of the COLLECTION_BOOKS table, unless they are already represented. In this case the COLLECTION_BOOKS table has 2 foreign keys: COLLECTION_ID and BOOK_ID, however COLLECTION_ID is already in the list, so we only need to add BOOK_ID.
Make sure that the Primary Key check box is selected. On the next step, make sure all the other foreign keys also have this check box selected. Basically, the IDs from each of the three main tables will be sufficient to define a primary key on this new table. You can also have any fields you need from the two intersection tables (e.g. a status column).
Then click Finish. Right-click on the entity object and select New Default View Object....
Go with the default name of LibraryBooksView. Open up the view object editor for the newly created LibraryBooksView.
Obviously the default SQL query is not going to work, since there is no column named BOOK_ID on the LIBRARY_COLLECTIONS table. To get this right, you will need to select "Expert Mode", which is a posh way of saying "do it yourself"!
The important points to note here are that the FROM clause has the two intersection tables: LIBRARY_COLLECTIONS and COLLECTION_BOOKS, and there is a WHERE clause that equates the COLLECTION_ID column on both tables. This means that a join is performed between the two tables.
I've renamed the alias for LIBRARY_COLLECTIONS from LibraryBooks to LibraryCollections so it is easier to read. We don't need the COLLECTIONS table in this query because we don't have any columns from it. If we did then it should be included. In this example I've taken the COLLECTION_ID column from the LIBRARY_COLLECTIONS table, but it could have easily come from the COLLECTION_BOOKS table instead.
Now that you have the view object representing this "super" intersection, you can use it to create associations and links with the LIBRARIES and BOOKS tables as you would with any other entity and view objects.
Have fun!
One use case that appears quite regularly with large database schemas is not covered by this method however. That is when you have a many-to-many relationship with more than one table inbetween them. A diagram of this situation is shown below. In this case you may want to retrieve a list of all the "grandchildren" of a "grandparent". That is, all the children of all the children of an item. In the example below, you may want to find all the books in all the collections for one library.
As it stands, in ADF you would need to use the LIBRARY_COLLECTIONS intersection to get a list of collection IDs and then apply that as a view criteria on the COLLECTION_BOOKS table (e.g. WHERE COLLECTION_ID IN (12, 23, 65, 32)). You would then have a list of the book IDs.
This is not the best solution, nor a very desireable one. A much better way would be to design a business component that represents an imaginary "super" intersection table, encompassing all three tables between LIBRARIES and BOOKS.
First you need to design an entity object to hold the needed attributes. Create a new entity object.
Call it LibraryBooks, and at first map it to the LIBRARY_COLLECTIONS intersection table. This is simply so that there is some database table that is referenced. Plus it helps in mapping the first attributes needed.
On the next step the wizard automatically adds all the columns from the selected table (in this case LIBRARY_ID and COLLECTION_ID). You want to keep all the foreign keys and discard any other columns. In this case we're keeping both columns as they are foreign keys and will help in creating a primary key later on.
At this point you will also need to add some attributes. Use the New... button rather than the New from Table... one. Add columns for the foreign keys of the COLLECTION_BOOKS table, unless they are already represented. In this case the COLLECTION_BOOKS table has 2 foreign keys: COLLECTION_ID and BOOK_ID, however COLLECTION_ID is already in the list, so we only need to add BOOK_ID.
Make sure that the Primary Key check box is selected. On the next step, make sure all the other foreign keys also have this check box selected. Basically, the IDs from each of the three main tables will be sufficient to define a primary key on this new table. You can also have any fields you need from the two intersection tables (e.g. a status column).
Then click Finish. Right-click on the entity object and select New Default View Object....
Go with the default name of LibraryBooksView. Open up the view object editor for the newly created LibraryBooksView.
Obviously the default SQL query is not going to work, since there is no column named BOOK_ID on the LIBRARY_COLLECTIONS table. To get this right, you will need to select "Expert Mode", which is a posh way of saying "do it yourself"!
The important points to note here are that the FROM clause has the two intersection tables: LIBRARY_COLLECTIONS and COLLECTION_BOOKS, and there is a WHERE clause that equates the COLLECTION_ID column on both tables. This means that a join is performed between the two tables.
I've renamed the alias for LIBRARY_COLLECTIONS from LibraryBooks to LibraryCollections so it is easier to read. We don't need the COLLECTIONS table in this query because we don't have any columns from it. If we did then it should be included. In this example I've taken the COLLECTION_ID column from the LIBRARY_COLLECTIONS table, but it could have easily come from the COLLECTION_BOOKS table instead.
Now that you have the view object representing this "super" intersection, you can use it to create associations and links with the LIBRARIES and BOOKS tables as you would with any other entity and view objects.
Have fun!
