Hi John,
I would suggest using a manytoone for nesting categories. The object
definition for your category would include
<manytoone name="Parent" lazy="true">
<link to="Category" column="category_parent_id"/>
</manytoone>
Note that if you use a table column for an object relationship, you cannot
have a property referencing the same column. I'm guessing that is why your
sql has two references to category_parent_id.
Cheers,
Paul
On Wed, Oct 15, 2008 at 7:32 AM, John Barlow <[EMAIL PROTECTED]> wrote:
>
> Greetings all,
>
> I've been racking my brain for about two days to try to figure this
> out. I have a category table, consisting of, among other things, a
> category_id and a category_parent_id. The idea is that categories can
> be sub categories of others (hence the category_parent_id) and have a
> null parent if they are the root node.
>
> I set up a onetomany relationship linking the category_id to the
> parent_id with lazy loading and everything was working beautifully for
> reading. The problem cropped up when I tried to save a record.
>
> The error that came back was a SQL error, stating "category_parent_id
> appears more than once in the result column list." From what I can
> find, this error occurs if you have two tables and have the
> relationship defined on both tables. The solution there is to remove
> a relationship definition from one of the tables and all is well.
>
> Since this is referencing itself, it is inherently getting both sides
> of the definition defined, which I think is what is causing this to
> break. I checked the SQL, and indeed it is taking on an extra
> category_parent_id to the end of the insert statement.
>
> I'm not sure if this is a Transfer bug or if I'm trying to use it in a
> way it wasn't meant to, but having a self-referencing table like this
> seems like a reasonable thing one would want to be able to model. For
> the record, I did get around it by using a link table that has two
> columns that point to the category_id field in the category table and
> a Many to Many relationship. Ideally, I'd like to not use an extra
> table to do this.
>
> I just wanted to bring this to the masses because I couldn't find
> anything on this and felt it may have been overlooked. What I WAS
> able to find was in reference to hibernate.
>
> System specs:
> -Transfer 1.0
> -CF7
> -MSSQL 2005
> -XP
>
> >
>
--
Paul Marcotte
Fancy Bread - in the heart or in the head?
http://www.fancybread.com
--~--~---------~--~----~------------~-------~--~----~
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
You received this message because you are subscribed to the Google Groups
"transfer-dev" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/transfer-dev?hl=en
-~----------~----~----~----~------~----~------~--~---
