Discriminator with WHERE clause

[Jason Seminara]

All,

[CF 9,0,0,251028 , hotfix 3]

I've been learning ORM this week, and hit a snag. I'm having a problem
with the discriminator attribute in one of my child beans. I expect it
to insert a WHERE clause, but it seems to be ignored.

I'm trying to rationalize a vendor's DB schema. The tables contain our
customer data and cannot be modified.

There are 4 entities described in the DB tables:
1 PERSON_PROFILES (person entities)
2 SITES (physical location entities)
3 ORGANIZATION_PROFILES (metadata about the locations)
4 RELATIONSHIPS (holds ALL bi-directional info connecting the
entities)

So far, I've rationalized this to
User <>----[has many]---- Employment(s) ( PERSON_PROFILES <>----
[has many]---- SITES)
______________________________________________________________________________

***Per User, I only want 'Business' SITES where I'm an
'EMPLOYEE_OF'***
______________________________________________________________________________

/**
**User.cfc
**/
component persistent='true' table='PERSON_PROFILES'
entityName="DummyUser" {

property name='entityID' fieldtype='id' column='O_PARTY_ID';
property name='partyID' fieldtype='column' column='PARTY_ID';

property name='lastName' fieldType='column'
column='PERSON_LAST_NAME' ;
property name='middleName' fieldtype='column'
column='PERSON_MIDDLE_NAME' ;
property name='firstName' fieldtype='column'
column='PERSON_FIRST_NAME';

property name='employment'
hint='array of employers'
singularname='employment'
fieldtype='one-to-many'
cfc='Employment'
linktable='RELATIONSHIPS'
inversejoincolumn='O_OBJECT_ID'
fkcolumn='O_SUBJECT_ID'

where='RELATIONSHIP_CODE="EMPLOYEE_OF"'
;
}
___________________________________________________________
/**
**Institution.cfc
**/
component persistent='true' table='SITES' entityname='Institution'
discriminatorcolumn='SITE_USE_TYPE' {
property name='instID' fieldType='id' column='O_PARTY_ID';
property name='locationID' fieldType='column'
column='O_LOCATION_ID' ;
property string address1;
property string address2;
property string address3;
property string city;
property string state;
property name='zip' column='POSTAL_CODE' ;
}

___________________________________________________________
/**
**Employment.cfc
**/
component persistent='true' table='SITES' entityname='Employment'
extends='Institution' discriminatorvalue='BUSINESS' {
property name='orgName'
table='ORGANIZATION_PROFILES'
joinColumn='O_PARTY_ID'
column='ORGANIZATION_NAME'
readOnly='true'
setter='false';
}
___________________________________________________________

When I run this
o = EntityLoad( 'DummyUser' , {entityID="e5153402"},true);
writeDump(o);


The generated SQL looks like this:

Hibernate:
select
dummyuser0_.O_PARTY_ID as O1_80_0_,
dummyuser0_.PARTY_ID as PARTY2_80_0_,
dummyuser0_.PERSON_LAST_NAME as PERSON3_80_0_,
dummyuser0_.PERSON_MIDDLE_NAME as PERSON4_80_0_,
dummyuser0_.PERSON_FIRST_NAME as PERSON5_80_0_
from
PERSON_PROFILES dummyuser0_
where
dummyuser0_.O_PARTY_ID='e5153402'
=-=-=-=-=

Hibernate:
select
employment0_.O_SUBJECT_ID as O5_1_,
employment0_.O_OBJECT_ID as O14_1_,
employment1_.O_PARTY_ID as O1_79_0_,
employment1_.O_LOCATION_ID as O3_79_0_,
employment1_.ADDRESS1 as ADDRESS4_79_0_,
employment1_.ADDRESS2 as ADDRESS5_79_0_,
employment1_.ADDRESS3 as ADDRESS6_79_0_,
employment1_.CITY as CITY79_0_,
employment1_.`STATE` as STATE8_79_0_,
employment1_.POSTAL_CODE as POSTAL9_79_0_,
employment1_1_.ORGANIZATION_NAME as ORGANIZA3_77_0_
from
RELATIONSHIPS employment0_
left outer join
SITES employment1_
on employment0_.O_OBJECT_ID=employment1_.O_PARTY_ID
left outer join
ORGANIZATION_PROFILES employment1_1_
on employment1_.O_PARTY_ID=employment1_1_.O_PARTY_ID
where
(
employment0_.RELATIONSHIP_CODE="EMPLOYEE_OF"
)
and employment0_.O_SUBJECT_ID='e5153402'



___________________________________________________________
As you can see, I would expect the first left outer join to read:
left outer join
SITES employment1_
on employment0_.O_OBJECT_ID=employment1_.O_PARTY_ID
AND SITE_USE_TYPE = 'BUSINESS'


Am I doing something wrong?

[Brian Kotek]

Not sure I follow exactly, but the discriminator column is purely used for inheritance, to allow Hibernate to differentiate subtypes. 

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To post to this group, send email to cf-or...@googlegroups.com.
To unsubscribe from this group, send email to cf-orm-dev+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cf-orm-dev?hl=en.

[Jason Seminara]

Sorry, now that I'm re-reading this, it is a bit dense. Let me sum it
up:

It seems that using a WHERE clause in a o2m relationship causes the
discriminator to be ignored on the 'many' side of the join.

The resultant SQL says, "Get me Site Info where this User is an
Employee".
What it SHOULD say is, "Get me the BUSINESS Site Info where this User
is an Employee"


EVIDENCE:
The Users join on the Organizations through the Relationships cross-
reference table. I need to filter twice: once in my type of
Relationship, and then again on the type of Organization.
Since Relationships is not an entity (it exists only as a linkTable on
User.cfc), I'm forced to select the type of relationship I want with a
WHERE clause (RELATIONSHIP_CODE="EMPLOYEE_OF" [see User.cfc above,
'employment' property]).

I then should be able to filter the type of Organization type with a
discriminator (SITE_USE_TYPE = 'BUSINESS' ), but the discriminator is
ignored.


Sorry for the brain-dump above. ;)

[Brian Kotek]

Sorry but I still don't follow. You've got RELATIONSHIP_TYPE="EMPLOYEE_OF", but there is nothing named RELATIONSHIP_TYPE or any values of "EMPLOYEE_OF" anywhere in the rest of the code you showed. So I'm not seeing why you would think that would translate into SITE_USE_TYPE="BUSINESS" in the SQL.

[Brian Kotek]

Re-reading and it makes a bit more sense, but not much. I'm not really sure if you can do this. I've certainly never tried it. Anyone else have any idea?

[Jason Seminara]

I think I'm missing an object in between the User and the Employer.
Here's what I'm going to try to do tomorrow:

A Person has a Job (or many).
A Job can be offered by only one Employer.
The Employer is a type of Institution, which holds an address, phone,
etc.

Unfortunately, this crazy table structure is forcing me to jump
through hoops to rationalize it.

I'll let you know if I'm still experiencing Hibernate ignoring the
Discriminator value, or if I simply stumped the parser with this mess.

Thanks for the help

[Brian Kotek]

I'm actually just wondering if you're making this more complex than it needs to be, because it sounds like you just want:

Person <one-to-many> Job

Job <one-to-one> Employer

[Brett]

I'll just throw this out there, using discriminators are big a nope
with me. The SQL I have seen generated with any select to any of the
objects creates a OUTER JOIN to each inheriting table. Use
composition! over inheritance.

[Brian Kotek]

Hmm not sure if others are doing something different than I am, but when I use inheritance all I end up with is a WHERE discriminatorColumn = 'SomeType' to limit the results to one particular subtype.

[Jason Seminara]

Brian,
Yes, that's exactly what I want to do!
After many hours of simplifying, I got it down to a digestible state,
yet the original issue still exists: The Job discriminator is ignored
when the Job Entity is joined onto the Person Entity using o2m.

This DOESNT happen:
Person <o2m> Job (Using
discriminator( RELATIONSHIP_CODE='EMPLOYEE_OF')


When I EntityLoad('Person'...), I get back a Person with 46 Jobs, obvs
representing his other affiliations with the Employer, not his actual
jobs.
When I EntityLoad('Job'...) directly (without the Person), Hibernate
behaves as expected, and inserts "where
this_.RELATIONSHIP_CODE='EMPLOYEE_OF'" into the SQL, thus returning
only ONE entity.

Brett, aren't I doing both? the Job Entity is a "typeOf"(Inheritence)
Relationship Entity. The Job is then Composed into the Person. Is
there a better way to do this?

[Jason Seminara]

This seems to be the same problem.
http://stackoverflow.com/questions/7680025/discriminator-not-used-in-sql-for-one-to-many-mapping-in-coldfusion-orm

[Brian Kotek]

On Fri, Jan 6, 2012 at 12:48 PM, Jason Seminara <jsem...@gmail.com> wrote:

This DOESNT happen:
Person <o2m> Job (Using
discriminator( RELATIONSHIP_CODE='EMPLOYEE_OF')


When I EntityLoad('Person'...), I get back a Person with 46 Jobs, obvs
representing his other affiliations with the Employer, not his actual
jobs.
When I EntityLoad('Job'...) directly (without the Person), Hibernate
behaves as expected, and inserts "where
this_.RELATIONSHIP_CODE='EMPLOYEE_OF'" into the SQL, thus returning
only ONE entity.

Sure, Hibernate knows how to use the discriminator column to differentiate the different types of Institutions. That's what it's for. I've actually never seen or heard of anyone trying to use this Hibernate-specific column in their own ORM calls. 

I'm honesetly still not totally sure what you're trying to do. It LOOKS like you're trying to create a property in Person that "skips over" the Job association and goes directly to the Employer. If I were doing this, I'd just associate Person to Job. If I want an employers collection exposed from Person, I'd write a method that iterates over the Jobs and returns an array of the Employers.

 

Brett, aren't I doing both? the Job Entity is a "typeOf"(Inheritence)
Relationship Entity.  The Job is then Composed into the Person. Is
there a better way to do this?

[Jason Seminara]

Sorry for all the confusion. That's exactly what I'm trying to do:

Person <one-to-many> Job

The problem is that the RELATIONSHIPS table holds more than just the
person's Job.

So, the join returns ALL his relationships. How do I select only the
Jobs?

[Brian Kotek]

Now the problem makes a bit more sense. Honestly I'm not sure since I've never used a link table that contained relationships to multiple different tables. Is there some reason you're doing it that way? Ideally, I'd say the best solution would be to correct the database schema to use a proper link table for each relationship. 

Could you set up a jobs relationship in Person that uses WHERE RELATIONSHIP_CODE = 'JOB' so that the jobs collection just has jobs in it? From there, you could set up the employers property to loop over the jobs and get the Employers, as we discussed earlier. 

[Jason Seminara]

Brain,

Unfortunately, I cannot change the DB structure at all; it's read-only
and provided by multiple other systems.

I figured out a work-around: use the WHERE attribute of the property
element. It's ugly, but it works:

USER.cfc:
property name='jobs'
singularname='job'
cfc='Employment'
fieldtype='one-to-many'
fkcolumn='O_SUBJECT_ID'
inversejoincolumn='O_OBJECT_ID'
where='RELATIONSHIP_CODE="EMPLOYEE_OF"'
setter='false'
update='false'
readonly='true'
;

Unless someone can prove otherwise, this is definitely a bug.
Thanks for all the help!

[Brian Kotek]

Well, I'd hesitate to call it a bug. This is a very "non-standard" usage of Hibernate so it MAY just be our inability to figure out how to set it up correctly. Like I said, I've definitely never done anything like this myself.

[Brian Kotek]

I'm also not really sure why the solution you showed above is much different than the original one? Weren't you using WHERE in your initial message?

[Jason Seminara]

No, I wasn't using WHERE: according to the docs, simply by defining
the discriminator, Hibernate SHOULD insert the WHERE clause in the
proper place.

The documented discriminator behavior works properly if you
instantiate the target object alone. For example, If I were to
instantiate the Job object directly (without the User object) it would
behave correctly and use the discriminator's value for the WHERE
clause in the generated SQL.

[Brian Kotek]

On Mon, Jan 9, 2012 at 3:23 PM, Jason Seminara <jsem...@gmail.com> wrote:

No, I wasn't using WHERE: according to the docs, simply by defining
the discriminator, Hibernate SHOULD insert the WHERE clause in the
proper place.

Well I just meant your first post said you were doing this (from the first post in this thread):

property name='employment'
       hint='array of employers'
       singularname='employment'
       fieldtype='one-to-many'
       cfc='Employment'
       linktable='RELATIONSHIPS'
       inversejoincolumn='O_OBJECT_ID'
       fkcolumn='O_SUBJECT_ID'
       where='RELATIONSHIP_CODE="EMPLOYEE_OF"';

The documented discriminator behavior works properly if you
instantiate the target object alone. For example, If I were to
instantiate the Job object directly (without the User object) it would
behave correctly and use the discriminator's value for the WHERE
clause in the generated SQL.

Sorry to keep sounding dim, but I'm afraid I'm still not sure exactly what you mean because there are so many names floating around at point (Person, User, Relationship, Relationship Type, Job, Institution, Employer, etc.). So I'm not sure what "Job without User object" means or what discriminator value is used in the WHERE clause for what association in your statement above. 

Anyway, if it works it may just be enough to leave it for now, unless you want to keep digging. Like I said, I'm not really sure if this is a bug, if it is something that just isn't being configured correctly, or if it is even something Hibernate allows but which CF isn't exposing/handling in its Hibernate wrapper interface because it is such an unusual situation.

[Jason Seminara]

I think the confusion is my fault. When I first reported this issue, I
had only been working with ORM for a few days. The snippet of the
WHERE clause in that block of code, was my final attempt to get the
code to work as I expect. It shouldn't have been there in the example.

After all I've learned this week, I now understand the problem to
simply be exactly what Daniel T. encountered here:
http://stackoverflow.com/questions/7680025/discriminator-not-used-in-sql-for-one-to-many-mapping-in-coldfusion-orm

(his example uses a Store with various Products)

Basically, when you have various types of 'things' (Fruits and
Vegetables in his case) in your cross-reference table, you should be
able to use a discriminator (as the documentation states) to choose
the type of 'thing' you want from the xref.

This works well when you are instantiating the Fruit by itself--
Hibernate uses the discriminator value to choose the appropriate item.
You receive a Fruit, which is an extension of Product.cfc.

CF ORM does not discriminate on Fruits when you instantiate the Store
[o2m <- Fruit].
The expected behavior here is that the you should receive a Store
object composed with an array of Fruits. What you get is a Store
composed with an array of Fruit objects, BUT the Fruit objects
represent ALL Products, not just Fruits -- Hibernate does not apply
the discriminator to choose the appropriate item. Inspecting the SQL
proves this: there is no WHERE clause in the SQL to discriminate
amongst the Products.

Daniel T got around this by creating a Fruit <m2o> Store on the Fruit
object (filtering by Store on the Fruit, see his examples). I'm unable
to use this solution as it doesn't fit the business model.

Ideally, I would rewrite the database tables to make more sense. But
since that's not possible, I'm getting around this issue by explicitly
adding a WHERE clause to the User's 'employment' property.

[Brian Kotek]

Looking at that post, I wonder if the two bidirectional relationships are what is messing it up. I try to leave out the inverse="true" on store.fruits and store.vegetables and removed the Many-To-One from Product to Store. Basically just have it go from Store to Fruit and Store to Vegetable. If he ever did actually need to find the stores for a given product, he could just use an HQL query for that.

But basically I'm just not sure if this is a limitation on CF's Hibernate wrapper, or is actually something that Hibernate itself won't allow. It may actually be a bug in CF after all!