How do I add "ORDER BY" to an "UPDATE" query in an Editor event handler with the PHP library?

rdueck

I've followed the example for row reordering at https://editor.datatables.net/examples/extensions/rowReorder.html but in my case I'm using a primary key.

The following code doesn't work properly when 'readingOrder' is a primary key. The query needs an "ORDER BY readingOrder DESC" clause to avoid conflicts. I've tried adding ->order('readingOrder DESC') but it didn't solve the problem.

    ->on( 'preCreate', function ( $editor, $values ) {
        // On create update all the other records to make room for our new one
        $editor->db()
            ->query( 'update', 'audiobooks' )
            ->set( 'readingOrder', 'readingOrder+1', false )
            ->where( 'readingOrder', $values['readingOrder'], '>=' )
            ->exec();
    } )

allan

You want to change the value of the primary key for each row? How do you maintain referential integrity for any rows that link to your table in that case?

Modifying the primary key based on the reordering isn't something I had considered I'm afraid (for exactly the above reason).

There isn't an option to set an order by clause for the Query class when used to update values at the moment - but that sounds like a sensible extension of what it currently does. I'll take a look at the options for that in the next release.

Allan

rdueck

Thanks Allan. In my case it's actually a unique key for sorting rows, rather than a primary key for persistent reference from other tables.

I've added support for order() by adding .$this->_build_order() after .$this->_build_where() in _update() in Editor-PHP-1.5.5/php/Database/Query.php. The result is:

 934     protected function _update()
 935     {
 936         $this->_prepare(
 937             'UPDATE '
 938             .$this->_build_table()
 939             .'SET '.$this->_build_set()
 940             .$this->_build_where()
 941             .$this->_build_order()
 942         );
 943 
 944         return $this->_exec();
 945     }

I can imagine it being useful to support _build_join() and _build_limit() as well, though I don't have a need for those right now.

allan

Agreed - thanks for the suggestion. And also for posting your solution.

It does make sense to have a unique flag on that column in retrospect - thanks for pointing that out!

Regards,
Allan