<property> or <manytoone> but not both

If you skim the Transfer docs, like I do, you might miss the fact that specifying a relationship (onetomany, manytoone, manytomany) automatically creates support for the column you reference. This can cause maddening errors like the following:

Error Executing Database Query.
ERROR: column "dtype" specified more than once
transfer\com\sql\QueryExecution.cfc (108)
transfer\com\sql\TransferInserter.cfc (371)
transfer\com\sql\TransferInserter.cfc (132)
transfer\com\sql\TransferInserter.cfc (49)
transfer\com\sql\SQLManager.cfc (61)
transfer\com\Transfer.cfc (197)
transfer\com\Transfer.cfc (177)

If you can see the SQL debugging, you would certainly see:

INSERT INTO tbl_question(questionid, answerlist, answerpattern, question, dtype, dtype)

where “dtype” is inserted twice. That’s clearly no good. Here was the object definition:

<object name="clubquestion" table="tbl_question" decorator="model.club.question">
<id name="questionid" type="UUID" generate="true" />
<property name="dtype" type="numeric" />
<property name="answerlist" type="string" />
<property name="answerpattern" type="string" />
<property name="question" type="string" />
<manytoone name="datatype" lazy="true">
<link column="dtype" to="event.questiondatatype" />
</manytoone>
</object>

As a first pass, we created a basic definition for all tables including all fields as properties. Then we came back later and added a manytoone to define a relationship. Transfer doesn’t complain until you try to save a new one and then you receive the “column specified more than once” error (note that this is from PostgreSQL, but you should see something similar or identical on other databases).

However, when creating the manytoone relationship, the question object now has new methods like setDatatype() that manage the value of dtype for you. Which is why you must remove the field from the <property> list. If much time passes between adding the relationship and trying to save, you could easily go crazy wondering what the problem could be.

This definition works as expected:
<object name="clubquestion" table="tbl_question" decorator="model.club.question">
<id name="questionid" type="UUID" generate="true" />
<property name="answerlist" type="string" />
<property name="answerpattern" type="string" />
<property name="question" type="string" />
<manytoone name="datatype" lazy="true">
<link column="dtype" to="event.questiondatatype" />
</manytoone>
</object>

Now you use the generated Transfer object method question.setDatatype(datatype) and when you transfer.save(question), the value for dtype will be correctly inserted.

Remember: use a <property> OR a relationship but NOT BOTH.