Nice subtle way of telling me to RTFM :). I had been looking at those docs quite a bit actually. Turns out that it was a caching problem. I tried what I did this morning (by removing the category_parent_id property), and things worked. This is the first project I'm using coldbox, coldspring, and transfer with, so I undoubtedly forgot to reinit something earlier :).
I can get around not having the parent_id field in the transfer object by using a normal query (bleh), but it only happens in one spot and I can use Transfer goodness for the rest of the time. So, For the sake of google and other poor souls trying to find this, here is a recap. For a table with a one to Many (onetomany) relationship to itself (self-reference, self-referencing), be sure to NOT include the foreign key in the properties list. If you do, you will get the following SQL error: "<column> appears more than once in the result column list." That should be enough keywords to let more people find this thread :) Thanks again, John On Wed, Oct 15, 2008 at 5:08 PM, Mark Mandel <[EMAIL PROTECTED]> wrote: > > http://docs.transfer-orm.com/wiki/Transfer_Configuration_File.cfm#onetomany > > > http://docs.transfer-orm.com/wiki/Repeated_field_names_in_the_select_list.cfm > > Mark > > On Thu, Oct 16, 2008 at 8:04 AM, John Barlow <[EMAIL PROTECTED]> wrote: > > <object name="categories" table="store_category"> > > <id name="category_id" type="numeric"/> > > <property name="FKStore_id" type="numeric"/> > > <property name="category_parent_id" type="numeric"/> > > <property name="category_name" type="string"/> > > <property name="category_description" type="string"/> > > <property name="category_keywords" type="string"/> > > <property name="category_image" type="string"/> > > <property name="category_hasThumbnails" type="boolean"/> > > <property name="category_position" type="numeric"/> > > <property name="category_sort_by" type="string"/> > > <property name="category_sort_dir" type="string"/> > > <property name="category_password" type="string"/> > > <property name="category_hitcount" type="numeric"/> > > <property name="category_isActive" type="boolean"/> > > <onetomany name="children" lazy="true"> > > <link to="category.categories" > > column="category_parent_id"/> > > <collection type="array"/> > > </onetomany> > > </object> > > > > I'm using the category_parent_id to find all of the top level nodes (0 if > it > > is a root node). From there, I use the children array to keep moving > down > > into the tree as needed. I tried removing the category_parent_id > property > > in hopes that it would remove the duplicate columns in the sql statement, > > but it didn't seem to help. > > > > I've been messing with this so long, I wouldn't dismiss the notion that > it > > could have been a caching problem that made me think it wasn't going to > work > > after I removed the column... > > > > > > On Wed, Oct 15, 2008 at 4:54 PM, Mark Mandel <[EMAIL PROTECTED]> > wrote: > >> > >> John, > >> > >> What did you config look like? > >> > >> I've done this more than once with Transfer, no problem. > >> > >> Mark > >> > >> On Thu, Oct 16, 2008 at 1: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 > >> > > >> > > > >> > > >> > >> > >> > >> -- > >> E: [EMAIL PROTECTED] > >> W: www.compoundtheory.com > >> > >> > > > > > > > > -- > > John Barlow > > PGP Key: 0xE917DD5B > > > > > > > > > > > > > -- > E: [EMAIL PROTECTED] > W: www.compoundtheory.com > > > > -- John Barlow PGP Key: 0xE917DD5B --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
