Cannot save a Doctrine_Collection

Question

I am using Docrine 1.2 with Zend Framework and trying to save a Doctrine Collection.

I am retrieving my collection from my table class with the following code.

public function getAll()
{
   return $this->createQuery('e')
    ->orderBy('e.order ASC, e.eventType ASC')
    ->execute();
}

I also have the following class to reorder the above event records.

class Admin_Model_Event_Sort extends Model_Abstract
{
    /**
     * Events collection
     * @var Doctrine_Collection
     */
    protected $_collection = null;

    public function __construct()
    {
        $this->_collection = Model_Doctrine_EventTypesTable::getInstance()->getAll();
    }

    public function save($eventIds)
    {
        if ($this->_collection instanceof Doctrine_Collection) {
            foreach ($this->_collection as $record)
            {
                $key = array_search($record->eventTypeId, $eventIds);
                if ($key !== false) {
                    $record->order = (string)$key;
                }
            }
            return $this->_saveCollection($this->_collection);
        } else {
            return false;
        }
    }

}

The _saveCollection method above is as follows

/**
 * Attempts to save a Doctrine Collection
 * Sets the error message property on error
 * @param Doctrine_Collection $collection
 * @return boolean
 */
protected function _saveCollection(Doctrine_Collection $collection)
{
    try {
        $collection->save();
        return true;
    } catch (Exception $e) {
        $this->_errorMessage = $e->getMessage();
        OpenMeetings_Logger_ErrorLogger::write('Unable to save Doctrine Collection');
        OpenMeetings_Logger_ErrorLogger::vardump($this->_errorMessage);
        return false;
    }
}

The event id's in the above save method is simply an enumerated array of event id's, I am using the keys of the array to set the sort order of the events using the order field. If I do a var_dump of the collection to an array ($this->_collection->toArray()) I get the correct data. However when I attempt to save the collection I get the following error.

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order = '0' WHERE eventtypeid = '3'' at line 1"

Is there anyway I can get Doctrine to expand on this error, the full SQL statement would be a start, also if anyone knows as to why this error is occuring then that would be very helpful.

Many thanks in advance

Garry

EDIT

I have modified my above code to try to work one record at a time but I still get the same problem.

public function save($eventIds)
    {
        foreach ($eventIds as $key => $eventId) {
            $event = Model_Doctrine_EventTypesTable::getInstance()->getOne($eventId);
            $event->order = (string)$key;
            $event->save();
        }

    }

Answer 1

Ok I have found the problem. I was using the MYSQL reserved word order as a field name thus the error, changed it to sortOrder and the problem went away.

Hope this helps someone with a similar issue.

Garry