Relationships via Linking Tables

Let's pretend for a moment that we really are building some sort of eCommerce site. (Perhaps we sell silly lights that look like they're wearing hats!)

To do this, we would need to have a table of products which would hold the details of the lamps being sold. This is easy.

But, what if we wanted to associate the products a Customer bought back to them somehow? Or, what if we wanted to be able to pull a report on all the invoices for the day? Or, what if we wanted to find all the people who purchased a particularly strange lamp so that we could report them to the authorities?

It sounds to me like we're going to need to have a table of invoices. An Invoice would represent all the items a customer purchased in one transaction. We could have multiple invoices per customer.

To relate products to invoices we'll have to create a table that relates to the two. Reactor thinks of these as "Linking" tables. Linking tables are tables that relate two tables to each other.

Let's do some quick database designing:

Product Table

Name	Type	Nullable	Other
productId	Int	No	PK / Identity
name	Varchar(50)	No
description	Varchar(2000)	No
price	Money	No

Invoice Table

Name	Type	Nullable	Default	Other
invoiceId	Int	No		PK / Identity
customerId	Int	No		FK to Customer.customerId

InvoiceProduct Table

Name	Type	Nullable	Other
productInvoiceId	Int	No	PK / Identity
invoiceId	Int	No	FK to Invoice.invoiceId
productId	Int	No	FK to Customer.customerId

Note: These are example tables. You probably wouldn't do anything this simple in a real application. Also, I used varchar(2000) for the product description so that the "like" operator would work in a future example.

By now you should know that by simply creating the tables in your database you can now generate and instantiate Record objects which represent the tables. So, we're going to skip over that part.

Think about what you know right now and how you'd configure these tables in the database. You would have a hasMany relationship from both Invoice and Product to the InvoiceProduct table. The InvoiceProduct table would have two hasOne relationships back to Invoice and Product.

Given that, what would your code look like if you wanted to get all the products on a particular invoice? Maybe like this:

<cfset Reactor = CreateObject("Component", "reactor.reactorFactory").init(expandPath("reactor.xml")) />


<cfset InvoiceRecord = Reactor.createRecord("Invoice").load(invoiceId=1) />




<cfset InvoiceProducts = InvoiceRecord.getInvoiceProductIterator().getArray() />


<cfloop from="1" to="#ArrayLen(InvoiceProducts)#" index="x">
   <cfset ProductRecord = InvoiceProducts[x].getProduct()/>

   <cfoutput>
        #ProductRecord.getName()#<br>
   </cfoutput>
</cfloop>

This code is a bit of a pain. It’s not evil, but it’s not something I'd want to do all the time. Not to mention, the fact that each one of those getProduct() method calls executes a query. This is not a good solution!

Wouldn't it be cool if we could somehow tell the Invoice and Product tables that they were linked? And maybe then we'd be able to use the same Iterator interface we did for other hasMany relationships? Not to worry, you can!

To support this, you use the "link" tag inside a hasMany tag. For example:

Take a look at the Product and Invoice configurations. The configuration is effectively saying that an Invoice has many Products by virtue of a link through InvoiceProduct (which has one of both). It works the other way around too.

Note: Adding a link through another table implicitly adds a hasOne relationship to the object with the link. So, the link from Product to Invoice says to Reactor that the Product hasOne InvoiceProduct.

Now, let's rewrite the code above and make it a bit nicer:

Wasn't that easier? Reactor intelligently knows how to join the Invoice table to the InvoiceProduct table to the Product table. Furthermore, it returns only the records from the product table.

This leads me to the last few points in this crash course, how to customize Reactor generated objects.