Codegen: AssociateXById

I have a “skill” table and a “worker” table. Each worker can have multiple skills, so I created an association table “worker_skill_assn”.

Everything works very well, except there's an opportunity to improve performance.

There's a generated Worker::AssociateSkill() method; this method accepts an object of the Skill class. The problem is, the parameter that this method takes is a whole-blown Skill object.

To edit worker profile, we created a simple QForm that has a QListBox of Skills with multiple selection enabled. The problem is, the code to update skills associated with the current worker looks like this:

foreach ($objSelectedListItems as $objListItem) {    
   $this->objWorker->AssociateSkill(Skill::Load($objListItem->Value));
}

           
Which means that if there are 20 changes to the list of workers, there'll be 40 queries (one to load the skill, one to perform an INSERT into the association table).

I'd like to propose an introduction of a new AssociateXById() method, which will take in the ID of the skill (in my case). I think this will be beneficial for all users of association tables.

Additionally, it would be “just ideal” if there was a method that accepted an array of ID's of Skills to associate, creating a single bulk query that'd perform all the necessary inserts in one go. I'm not sure if there's a cross-database way of doing that.

Add the following to your associated_object_manytomany.tpl file:

        /**
         * Associates a <%= $objManyToManyReference->ObjectDescription %>
         * @param ind $<%= $objManyToManyReference->VariableName %><%= $objManyToManyReferenceTable->PrimaryKeyColumnArray[0]->PropertyName %>
         * @return void
        */ 
        public function Associate<%= $objManyToManyReference->ObjectDescription %>By<%= $objManyToManyReferenceTable->PrimaryKeyColumnArray[0]->PropertyName %>($<%= $objManyToManyReference->VariableName %><%= $objManyToManyReferenceTable->PrimaryKeyColumnArray[0]->PropertyName %>) {
            if (<%= $objCodeGen->ImplodeObjectArray(' || ', '(is_null($this->', '))', 'VariableName', $objTable->PrimaryKeyColumnArray) %>)
                throw new QUndefinedPrimaryKeyException('Unable to call Associate<%= $objManyToManyReference->ObjectDescription %> on this unsaved <%= $objTable->ClassName %>.');

            // Get the Database Object for this Class
            $objDatabase = <%= $objTable->ClassName %>::GetDatabase();

            // Perform the SQL Query
            $objDatabase->NonQuery('
                INSERT INTO <%= $strEscapeIdentifierBegin %><%= $objManyToManyReference->Table %><%= $strEscapeIdentifierEnd %> (
                    <%= $strEscapeIdentifierBegin %><%= $objManyToManyReference->Column %><%= $strEscapeIdentifierEnd %>,
                    <%= $strEscapeIdentifierBegin %><%= $objManyToManyReference->OppositeColumn %><%= $strEscapeIdentifierEnd %>
                ) VALUES (
                    ' . $objDatabase->SqlVariable($this-><%= $objTable->PrimaryKeyColumnArray[0]->VariableName %>) . ',
                    ' . $objDatabase->SqlVariable($<%= $objManyToManyReference->VariableName %><%= $objManyToManyReferenceTable->PrimaryKeyColumnArray[0]->PropertyName %>) . '
                )
            ');
        }

Note: This is untested, and may be wrong.