ColdFusion: ORM Collection with Multiple Foreign Keys

Question

My database structure mainly consists of multiple primary keys per table, therefore multiple columns are required per join. I'm trying to use ColdFusion (11 to be specific) ORM collection property. It seems that a comma separated list of columns in the fkColumn attribute doesn't work, like it does for relationship properties. I have filed a bug with Adobe, but I'm wondering if anyone else has run into this and found workarounds. Or maybe I'm just doing it wrong..

Table Setup

Years        Staff         StaffSites    Sites
===========  ============  ============  ===========
YearID (PK)  StaffID (PK)  YearID (PK)   SiteID (PK)
YearName     StaffName     StaffID (PK)  SiteName
                           SiteID (PK)

Staff ORM CFC

component persistent=true table='Staff' {
    property name='id'    column='StaffID'       fieldType='id';
    property name='year'  column='YearID'        fieldType='id';
    property name='sites' elementColumn='SiteID' fieldType='collection' table='StaffSites' fkColumn='StaffID,YearID';
}

The Problem

There is an error when running the generated query: [Macromedia][SQLServer JDBC Driver][SQLServer]An expression of non-boolean type specified in a context where a condition is expected, near ','.

Taking a look at the generated query, it appears that the list of columns is not properly parsed for the where clause, but it somewhat understands that there are multiple columns in the select expression.

select
    sites0_.StaffID,
    YearID as StaffID1_2_0_,
    sites0_.SiteID as SiteID4_0_ 
from
    StaffSites sites0_ 
where
    sites0_.StaffID,YearID=?

The Goal

For the ORM collection property to correctly support a multi-key "join". Why not use a relationship? I'd like to use ORM objects to then serialize as JSON for use in the REST services. The serialized JSON needs to contain the ID for the relationships, not the actual relationship data. For example, the JSON payload should be:

{
    "id": 1234,
    "year": 2015,
    "sites": [1,2,3]
}

Instead of something like:

{
    "id": 1234,
    "year": 2015,
    "sites": [
        {"id": 1, "name": "Foo"},
        {"id": 2, "name": "Bar"},
        {"id": 3, "name": "Baz"},
    ]
}

Post a sample of expected JSON for use in the REST services. — Henry, Apr 21 '15 at 19:29
@Henry I've added the examples that you requested. Thanks for any input. I've been thinking about using ORM hooks to manually fetch the data "post-load". I'll try that once I get back to this project. — Panman, Apr 22 '15 at 14:39
looks like you want this JSON from the Staff entity. Use custom serializer (new in CF11) for `Staff` and set up the rest as one-to-many or many-to-many with linktable. — Henry, Apr 22 '15 at 21:33

Henry · Answer 1 · 2015-04-22T16:34:22.983

0

For your DB structure, the simplest way to translate into ORM would be to use "StaffSites" as linktable for many-to-many relationships.

You should try CF11's Custom serializer http://blogs.coldfusion.com/post.cfm/language-enhancements-in-coldfusion-splendor-improved-json-serialization-2

edited Apr 22 '15 at 16:34

answered Apr 21 '15 at 01:24

Henry

32,689
19
120
221

The problem with relationships is that the actual relationship data is included in the serialized JSON. I just need to get the related ID when serializing, which is the reasoning for collection instead. – Panman Apr 21 '15 at 13:48
@Panman then why use ORM at all? :) Just use good-old cfquery if you prefer to deal with ID's. – Henry Apr 21 '15 at 16:19
I'd still like to use other ORM features; searching, caching, etc. And it's a good way to organize other business logic. – Panman Apr 21 '15 at 19:14
Alright, I'll try the relationship with custom serializer route. I'm having trouble figuring out how to configure the property. Do you know what my `sites` property would need to be? – Panman Apr 28 '15 at 19:30

ColdFusion: ORM Collection with Multiple Foreign Keys

1 Answers1