Problem in hierarchical query

[Tom McNeer]

Hi,

I'm running into a problem trying to retrieve entries from an Org table where any organization might have a parent organization in the same table. This is not an infinite, or even deep, hierarchy. Every organization is either a top-level (parentID=0) or second-level organization. When I try to retrieve all the Orgs with their parents, I receive the following error:

Message	Error with class value 'parent' in clause
Detail	Class values must resolve to the Class name or the alias. e.g. 'email.Email.emailName'
Extended Info
Tag Context	C:\webApps\frameworks\transfer\com\tql\walkers\AbstractBaseWalker.cfc (124) C:\webApps\frameworks\transfer\com\tql\walkers\Object.cfc (38) C:\webApps\frameworks\transfer\com\tql\walkers\Join.cfc (261) C:\webApps\frameworks\transfer\com\tql\walkers\Join.cfc (306) C:\webApps\frameworks\transfer\com\tql\walkers\Join.cfc (306) C:\webApps\frameworks\transfer\com\tql\walkers\Join.cfc (50) C:\webApps\frameworks\transfer\com\tql\walkers\From.cfc (133) C:\webApps\frameworks\transfer\com\tql\walkers\From.cfc (57) C:\webApps\frameworks\transfer\com\tql\walkers\From.cfc (91) C:\webApps\frameworks\transfer\com\tql\SelectStatement.cfc (232) C:\webApps\frameworks\transfer\com\tql\SelectStatement.cfc (154) C:\webApps\frameworks\transfer\com\tql\SelectStatement.cfc (101) C:\webApps\frameworks\transfer\com\tql\TQLManager.cfc (28) C:\webApps\frameworks\transfer\com\Transfer.cfc (471) C:\webApps\medlien\model\users\userService.cfc (659) C:\webApps\medlien\controller\userController.cfc (206) C:\webApps\frameworks\ModelGlue\unity\listener\Listener.cfc (26) C:\webApps\frameworks\ModelGlue\unity\eventrequest\MessageBroadcaster.cfc (32) C:\webApps\frameworks\ModelGlue\unity\framework\ModelGlue.cfc (332) C:\webApps\frameworks\ModelGlue\unity\framework\ModelGlue.cfc (290) C:\webApps\frameworks\ModelGlue\unity\framework\ModelGlue.cfc (263) C:\webApps\frameworks\ModelGlue\unity\ModelGlue.cfm (75) C:\webApps\medlien\webroot\index.cfm (33) C:\webApps\medlien\webroot\Application.cfc (52)

Here is the TQL going in:


        <cfsavecontent variable="qList">
            SELECT org.orgName,org.orgID,parentOrg.orgID AS parentID,parentOrg.orgName AS parentName
            FROM users.org AS org LEFT OUTER JOIN users.org AS parentOrg ON parent
            <cfif NOT arguments.includeInactive>WHERE    org.isInactive = :inactive</cfif>
            ORDER BY parentOrg.orgName,parentOrg.orgID, org.orgName
        </cfsavecontent>

And here's the relevant part of transfer.xml:


            <object name="org" table="Orgs">
                <id name="OrgID" type="numeric" />
                <property name="OrgName" type="string" column="OrgName" nullable="true" />
                <property name="OrgAddress1" type="string" column="Address1" nullable="true" />
                <property name="OrgAddress2" type="string" column="Address2" nullable="true" />
                <property name="OrgCity" type="string" column="City" nullable="true" />
                <property name="OrgPhone" type="string" column="Phone" nullable="true" nullvalue="" />
                <property name="OrgFax" type="string" column="Fax" nullable="true" nullvalue="" />
                <property name="OrgZip" type="string" column="Zip"  nullable="true" nullvalue="" />
                <property name="OrgContact" type="string" column="Contact" nullable="true" />
                <property name="OrgContactEmail" type="string" column="ContactEmail" nullable="true" />
                <property name="OrgContactPhone" type="string" column="ContactPhone" nullable="true" nullvalue="" />
                <property name="ChangeDate" type="date" column="ChangeDate" />
                <property name="ChangeUserID" type="numeric" column="ChangeUserID" />
                <property name="IsInactive" type="boolean" column="IsInactive" />
                <property name="ReferralLink" type="string" column="ReferralLink" />
                <property name="taxID" type="string" column="taxID" />
                <manytoone name="parent" lazy="true">
                    <link to="users.org" column="parentID" />
                </manytoone>
                <manytoone name="orgType">
                    <link to="users.orgType" column="OrgTypeID" />
                </manytoone>
                <manytoone name="State">
                    <link to="util.states" column="stateID" />
                </manytoone>
            </object>

If I don't set "lazy=true" on the parent relationship, I get a warning about infinite recursion.

It's not a problem with "parent" being a reserved word, either, because I've changed the naming to no avail.

Once again, I'm pretty sure it's just something I don't yet understand about how to use Transfer.

CF 7.02
Win2003Server
SQL Server 2000

Thanks for any suggestions.

--
Thanks,

Tom

Tom McNeer
MediumCool
http://www.mediumcool.com
1735 Johnson Road NE
Atlanta, GA 30306
404.589.0560

[Brian Kotek]

Your join syntax looks wrong. If you're specifying the relationship it needs to look like

from post.Post as Post
		join user.User 
			ON Post.Author

You may want to consult the documentation as there are several syntaxes for doing joins.

[Tom McNeer]

Hi Brian,

Thanks for the reply.

On Jan 25, 2008 12:05 PM, Brian Kotek <bria...@gmail.com> wrote:

Your join syntax looks wrong. If you're specifying the relationship it needs to look like

from post.Post as Post
		join user.User 
			ON Post.Author

You may want to consult the documentation as there are several syntaxes for doing joins.

Thanks. I have gone through the documentation. I understand what you're saying about the ON specification. But I was following syntax suggested by Sean Corfield a while back when someone had multiple manytoone relationships to the same table.

The OP's XML was:

<object name="game">
       <id name="game_id" type="numeric" />
       <property name="game_date" type="date" nullable="false" />
       <property name="game_home_score" type="numeric" nullable="true" />
       <property name="game_away_score" type="numeric" nullable="true" />
       <manytoone name="home_team">
               <link to="h2o.team" column="game_home_team_id" />
       </manytoone>
       <manytoone name="away_team">
               <link to="h2o.team" column="game_away_team_id" />
       </manytoone>
</object>

He wanted to pull the information out into a CF query structure instead of a set of objects, and was having trouble making his JOINs in the TQL query.

Sean's suggestion was to include this in the TQL:


   JOIN h2o.team AS t ON home_team

or

   JOIN h2o.team AS t ON away_team

Since the OP indicated that this worked for him, I used the same syntax (I think) in making my JOIN, using the manytoone "name" in the ON statement.

If that is not correct, could you help me understand my mistake, please?

[Brian Kotek]

I've actually never even had to use TQL (if I get some complex query I just write the SQL), so we may need to wait for Mark to comment.

[Paul Marcotte]

Hi Tom,

Since you have only one join to the same table and not two as the team example demonstrates, can you try the tql without the "ON parent" portion, just for kicks? 

One other thing, if parentID is set to 0 as  a default, I don't think you need to use a LEFT OUTER JOIN as the field is not NULL.

If you feel like giving it a shot, let me know how that turns out.

Cheers,

Paul

--
Paul Marcotte
Fancy Bread - in the heart or in the head?
http://www.fancybread.com

[Tom McNeer]

Hi Paul,

Thanks for the suggestion.

On Jan 25, 2008 3:22 PM, Paul Marcotte <pmar...@gmail.com> wrote:

Since you have only one join to the same table and not two as the team example demonstrates, can you try the tql without the "ON parent" portion, just for kicks? 

Yup. Precisely the same error, now using this TQL:

<cfsavecontent variable="qList">
            SELECT org.orgName,org.orgID,parent.orgID AS parentID,parent.orgName AS parentName
            FROM users.org AS org LEFT OUTER JOIN users.org AS parent

            <cfif NOT arguments.includeInactive>WHERE    org.isInactive = :inactive</cfif>

            ORDER BY parent.orgName,parent.orgID, org.orgName
 </cfsavecontent>

 

One other thing, if parentID is set to 0 as  a default, I don't think you need to use a LEFT OUTER JOIN as the field is not NULL.

But there is no Org with an ID of 0, so an INNER JOIN loses all  the records with a parentID of 0.

Thanks for the thoughts, though.

[Paul Marcotte]

Ack!  Oh well, sorry I couldn't be of more assistance!

Gotcha on the JOIN syntax.  Shows how much I know about recursive joins in TQL...

Thanks for humouring me. ;)

Paul

[Mark Mandel]

Tom,

What is the error here? The auto join should actually work....? Can
you let me know, that may be a bug.

If you are using a specific join, and are using the ON statement, you
MUST specify which class you are referring to when specifying a
composition name.

I.e.

SELECT org.orgName,org.orgID,parentOrg.orgID AS
parentID,parentOrg.orgName AS parentName

FROM users.org AS org LEFT OUTER JOIN users.org AS

parentOrg ON parent

won't work as the when TQL reads this: ON parent, it assumes that
'parent' is a class name, and tries to resolve it as such.

Hence the error:

Class values must resolve to the Class name or the alias. e.g.
'email.Email.emailName'

(tho' that should read 'their')

You ON statement should read:
FROM users.org AS org LEFT OUTER JOIN users.org AS parentOrg ON org.parent

without specifying which class the composition refers to, TQL has no
way to knowing how to resolve it.

You can see in the documentation, specific joins read:
from class [as classAlias] join class [as classAlias] ON ( class |
classAlias ).composite [ ( and | or ) ( class | classAlias ).composite
]*

I hope that makes more sense.

Regards,

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Tom McNeer]

Hi Mark,

I figured I'd hear from you soon. I'm always amazed at how willing you are to help.

 SELECT org.orgName,org.orgID,parentOrg.orgID AS
parentID,parentOrg.orgName AS parentName

           FROM users.org AS org LEFT OUTER JOIN users.org AS

parentOrg ON parent

won't work as the when TQL reads this: ON parent, it assumes that
'parent' is a class name, and tries to resolve it as such.

Gotcha. As I mentioned earlier, I was trying to follow Sean's syntax example in a thread a couple of months back. Maybe I got it wrong.

Anyway --

You ON statement should read:
FROM users.org AS org LEFT OUTER JOIN users.org AS parentOrg ON org.parent

I understand what you're saying, and why. But help walk me through this, please: currently, "parent" is not defined as a class or alias in the XML. It's simply the name of a manytoone relationship that joins back to the users.org table on the parentID field.

            <object name="org" table="Orgs">
                <id name="OrgID" type="numeric" />

                    (... fields omitted)

                <manytoone name="parent" lazy="true">
                    <link to="users.org" column="parentID" />
                </manytoone>

            </object>

So -- is it possible to make a join with this manytoone relationship only? Or do I need to have a separate Object definition in transfer.xml for users.parentOrg, pointing to the same table as the user.Org Object.?

Or am I misunderstanding altogether?

[Mark Mandel]

Tom,

Quick one to go back on:

You said you tried:

<cfsavecontent variable="qList">
SELECT org.orgName,org.orgID,parent.orgID AS
parentID,parent.orgName AS parentName
FROM users.org AS org LEFT OUTER JOIN users.org AS parent

<cfif NOT arguments.includeInactive>WHERE
org.isInactive = :inactive</cfif>
ORDER BY parent.orgName,parent.orgID, org.orgName
</cfsavecontent>

And got an error? what was the error? This should have worked, so I
want to know if I have a bug.

On Jan 26, 2008 10:25 AM, Tom McNeer <tmc...@gmail.com> wrote:
> Hi Mark,
>
> I figured I'd hear from you soon. I'm always amazed at how willing you are
> to help.

You're very welcome ;)

> > You ON statement should read:
> > FROM users.org AS org LEFT OUTER JOIN users.org AS parentOrg ON org.parent
>
>
> I understand what you're saying, and why. But help walk me through this,
> please: currently, "parent" is not defined as a class or alias in the XML.
> It's simply the name of a manytoone relationship that joins back to the
> users.org table on the parentID field.

Yes, that is totally correct.

>
>
> <object name="org" table="Orgs">
> <id name="OrgID" type="numeric" />
> (... fields omitted)
>
> <manytoone name="parent" lazy="true">
> <link to="users.org" column="parentID" />
> </manytoone>
> </object>
>
> So -- is it possible to make a join with this manytoone relationship only?

Yes, these are called 'specific joins' as outlined in the documentation.

Did you try it? did it not work ;) ?

Mark

[Tom McNeer]

Hey Mark,

On Jan 26, 2008 3:42 AM, Mark Mandel <mark....@gmail.com> wrote:

<cfsavecontent variable="qList">
           SELECT org.orgName,org.orgID,parent.orgID AS
parentID,parent.orgName AS parentName
           FROM users.org AS org LEFT OUTER JOIN users.org AS parent

           <cfif NOT arguments.includeInactive>WHERE
org.isInactive = :inactive</cfif>
           ORDER BY parent.orgName,parent.orgID, org.orgName
 </cfsavecontent>

And got an error? what was the error? This should have worked, so I
want to know if I have a bug.

Actually, no, I had not tried it. In my last message, I was trying to make sure I understood you clearly. I now understand better how the parser tries to resolve aliases. But ...

I have now tried it, and there's no error. But it doesn't result in the SQL I expected, and therefore doesn't pull the parents.

What I'm going for is:

SELECT org.orgID, org.orgName, parent.orgID AS parentID, parent.orgName AS parentName
FROM Orgs org LEFT OUTER JOIN Orgs parent ON org.ParentID = parent.OrgID
WHERE org.IsInactive = 0
ORDER BY parent.OrgName , parent.OrgID , org.OrgName

The above TQL statement produces:

SELECT org.OrgName , org.OrgID , parent.OrgID AS parentID , parent.OrgName AS parentName
FROM Orgs org LEFT outer join Orgs parent ON org.parentID = parent.OrgID
AND org.OrgID = parent.parentID
WHERE org.IsInactive = 0
ORDER BY parent.OrgName , parent.OrgID , org.OrgName

Note the extra condition added in line 4, which can't be fulfilled. Thus no parents are found.

Now, if I create a second copy of the XML for the Orgs table in transfer.xml, call it "parentOrg," and specify it in the manytoone relationship of the org object, everything comes out as expected.

I think I'm asking Transfer to do something it's not built for, in the way I'm configuring it, at least. Certainly, it's simple enough to write the SQL to get what I need; and actually, there's another relationship -- "orgType" that I need to include in the query, which I omitted in this post (and from my test TQL) in order to simplify the situation.

Maybe trying to solve a recursive relationship this way is not the best thing. I was doing it, as much as anything, to try to learn how to utilize Transfer in as many ways as possible.

Or maybe I'm just doing it wrong again, and you'll straighten me out.

[Mark Mandel]

Ah yes, the auto join SHOULD do that, because it looks to join every
relationship it can find - in this case, it is both ways.

Now what happens when you do the Specific Join that we talked about
earlier in this post? Once the fix for your TQL is in place, does that
work? (It should)

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Tom McNeer]

Now what happens when you do the Specific Join that we talked about
earlier in this post? Once the fix for your TQL is in place, does that
work? (It should)

Sure. But it still requires a duplicate object in transfer.xml, placed there just to represent the parents. (Unless I'm missing what you're suggesting.) Without that, I don't have a class to join against. And since all I'm really looking for at this point is a query object with Orgs and their Parents, it's much easier (and cleaner, I think) to simply do straight SQL.

When I retrieve an individual Org, I'll still have all the data about its parent from the original manytoone relationship. Going to the trouble of having the duplicate object just to get a simple list seems to be going about things the hard way.

It's been an interesting exercise, though. And it has done what I was really trying to do, anyway: teach me more about how Transfer works, and when (or not) to use it. ;-)

Thanks again for all your help.

[Mark Mandel]

>
> Sure. But it still requires a duplicate object in transfer.xml, placed there
> just to represent the parents. (Unless I'm missing what you're suggesting.)

No, you shouldn't need it. Like I said, have you actually TRIED what
I suggested?

> Without that, I don't have a class to join against. And since all I'm really
> looking for at this point is a query object with Orgs and their Parents,
> it's much easier (and cleaner, I think) to simply do straight SQL.

You can join it against another instance of itself, just aliased
differently like you had originally.

>
> When I retrieve an individual Org, I'll still have all the data about its
> parent from the original manytoone relationship. Going to the trouble of
> having the duplicate object just to get a simple list seems to be going
> about things the hard way.

Agreed, hence what I am saying above.

Mark

[Tom McNeer]

Hi Mark,

On Jan 27, 2008 4:42 PM, Mark Mandel <mark....@gmail.com> wrote:

No, you shouldn't need it.  Like I said, have you actually TRIED what
I suggested?

I thought I had, but I misunderstood what you were asking me to do.

Sorry.

See, when you said to change my TQL to this ...

FROM users.org AS org LEFT OUTER JOIN users.org AS parentOrg ON org.parent

... I thought that you had misread my XML and thought that I had defined a package called "org." (Guess I should have know better ;-0)

I now understand that you were trying to explain to me that once "org" was defined as an alias for "users.org," I could then refer to the "parent" object as "org.parent." I didn't realize that you could refer to the child relationships that way. Believe me, I've read the documentation; but I still didn't understand how you could refer to a child relationship that was not defined with its own <object> definition in the XML.

Now that I have made my TQL conform to yours, it works, of course. And I understand more about how Transfer works. Actually kinda scary how much it can do.

Thanks for bearing with me. Hopefully, this discussion will provide some education for other folks, too.

[Mark Mandel]

No problem ;o)

Glad we got there in the end, and you got the result that you wanted :oD

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Tom McNeer]

Mark,

Thanks again. It's kind of you to spend so much time explaining things like this, after you've already put so much into building Transfer.