I am, more or less, a procedural coder. Sure, I use ColdFusion components (CFCs), but rarely in a way that the righteous OOP people will say even qualify as "Object Oriented Programming." I understand the idea behind domain models and things like model-view-controller, but I just can't quite seem to connect all the dots that would allow me to implement this type of programming in my day-to-day work.
One of the huge mental hurdles that I have is the JOIN table. I understand objects that correspond to a single table, or a single entity spread across multiple tables; that sort of information is easy for me to update through a data access object. But what about these JOIN tables? I can't seem to fit them in anywhere that makes me comfortable as a man. Take for example a common problem that I come across: the parent-child relationship. In a join table the defines this hierarchy, we might have a parent_id, a child_id, and then additional columns that further describe the relationship, such as the sort order of the children:
The above tables are tables that I generally have to use when describing the relationship between two or more areas of practice in a given legal industry. What makes this even more interesting is that this join table doesn't join two different tables - it joins one table back to itself (cool stuff!).
If I wanted to get all the children records for a given practice ares, I might use a SQL query like this:
SELECT p.id p.name FROM practice p INNER JOIN pratice_practice_jn ppjn ON ( p.id = ppjn.child_id AND ppjn.parent_id = <cfqueryparam .... /> ) ORDER BY ppjn.sort ASC, p.name ASC
Ok, so that's how it could get used, let's talk about where these values get updated. Currently, I would have something like a sortable table that submits a list of IDs in a given order. Taking that list, I would then do something like this:
<!--- Delete existing joins to this parent. ---> DELETE FROM practice_practice_jn WHERE parent_id = <cfqueryparam .... /> ; <!--- Create new joins. ---> <cfloop index="intSort" from="1" to="#ListLen( FORM.lst_id )#" step="1"> INSERT INTO practice_practice_jn ( sort, parent_id, child_id ) VALUES ( <cfqueryparam value="#intSort#" />, <cfqueryparam .... />, <cfqueryparam value="#ListGetAt( FORM.lst_id, intSort )#" /> ); </cfloop>
If I were to switch over to an object oriented approach, where the heck would this kind of update go? I don't imagine it would go in the Practice business object, because it's not really related to a single practice (unless you are thinking it is related to the parent practice). But even so, what then? Would you have something like:
<cfset Practice.SetChildSort( FORM.lst_id ) />
That's doesn't feel right. And what if the JOIN table had more than just sort information? For example, joining attorneys to practice areas (attorney_practice_jn), we would have practice_id, an attorney_id, a sort column, and maybe a flag for "department chair." Well, I guess in that case, our update SQL statement would have to change because we can't just delete joins for the sort update (otherwise we would lose the chair information).
Is this where something like a "Practice Service" object would come into play?
Clearly, I don't want to be instantiating a Practice object for EACH ID value in my returned list - this would seem very silly since reaching directly into the database is so much more efficient. Plus, object creation in ColdFusion is expensive.
Clearly I am very confused and frustrated with the creating of and the maintaining of join information using ColdFusion and object oriented programming. I really hope someone looks at this and says, "Oh, you are just missing one little bit of info here" and then I have this Eureka moment and all is good and happy.
Please help me help myself - I will make it worth your while.
Want to use code from this post? Check out the license.