error when inserting many-to-many

[Marc]

Hi,

Having problems here inserting a new user and associating it with existing domain(s).

I have this setup in my Transfer.xml:

        <object name="user">
            <id name="id" generate="true" type="numeric" />
            <property name="email" type="string" nullable="false" />
            <property name="password" type="string" nullable="false" />
            <property name="registrationId" type="string" nullable="false" />
            <property name="lastLogin" type="date" nullable="true" />

            <manytomany name="domain" table="userDomain">
                <link to="user" column="FK_user"/>
                <link to="domain" column="FK_domain"/>
                <collection type="array">
                    <order property="name" order="asc" />
                </collection>
            </manytomany>
        </object>

        <object name="domain">
            <id name="id" column="id" type="numeric" />
            <property name="url" type="string" nullable="false" />
            <property name="name" type="string" nullable="false" />
            <property name="description" type="string" nullable="true" />
        </object>

I fill in a form with data for user (email, lastlogin etc) and a series of checkboxes, each called "domainId", indicating which domain is valid for a user (checked is: associate domain with user, not checked is: don't). If I check 3 checkboxes, after form submit the field "DomainId" has 3 values (e.g."4,5,6").

In my gateway I want to add the new user and associate the selected domains with it:

    <cffunction name="add" access="public" description="Adds or updates a user object">
        <cfargument name="Bean" type="Application.Beans.UserBean" required="true">

[call setters on domain object]
[local.toDomainArray is an array of Transfer "domain" objects ]

        <!--- 1 persist domain object --->
        <cfset local.transfer.save(local.TOUser)>

        <!---  2 associate each domain object with user --->
        <cfloop array="#local.toDomainArray#" index="toDomain">
            <cfset local.TOUser.addDomain(toDomain)>
        </cfloop>

        <!--- 3 persist domain object --->
        <cfset local.transfer.save(local.TOUser)>
    </cffunction>

On step 3 (local.transfer.save(local.TOUser)) I get an error:

Error Executing Database Query.
Field 'id' doesn't have a default value
 
The error occurred in D:\workspace\CMS\Framework\CMS\Transfer\com\sql\QueryExecution.cfc: line 82
Called from D:\workspace\CMS\Framework\CMS\Transfer\com\sql\QueryExecution.cfc: line 59

Tracing back further I see that the error wsa triggered from step 3:

        <cfset local.transfer.save(local.TOUser)>

At first I had only step 1 and 2, I got the same error. I figured it made sense since the new user wan't persisted yet so there was no id created in the db.

This is a snippet of the sql from the error dump:

SQL	INSERT INTO userDomain ( FK_user , FK_domain ) VALUES ( (param 1) , (param 2) )

Question 1: Is Transfer buggy when inserting many-to-many relationships or am I using the wrong approach here?
Question 2: Although the add operations errors, Transfer does insert te user (the linking table user-domain is not updated though). Is transaction off when committing relationships?

Thanks,

Marc

[Marc]

Fixed. The linking table's id column had no auto-increment option on.