CODEWITHSUNDEEP
phpsqlcakephpcakephp-3.0
artSir
artSir

Reputation: 550

CakePHP 3 find() with order foreach each row

Man, I don’t know why this isn’t working, i’m following the Manuel verbatim.

I have a ProductStyles Table that has an order column. Suppose i have 5 rows. And I delete number 3. I want to find all rows matching the product id and iterate through each one of them replacing the order # incrementially. 

Productstyle Table
id - product_id - order

My first step is to just fetch all the rows matching product ID in order so i can do a foreach but I keep get SQL errors. Honestly I don’t know SQL that well other than the basic select.

$query = $this->ProductStyles->find()
                ->where(['product_id'=> 1 ])
                ->order(['order' => 'ASC'])
                ;


//debug($query);
//debug( $query->all() ); //this throws an error

Error Message:

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 ASC' at line 1

SQL:

//SELECT ProductStyles.id AS `ProductStyles__id`, //ProductStyles.product_id AS 
//`ProductStyles__product_id`, ProductStyles.style_id AS //`ProductStyles__style_id`, 
//ProductStyles.order AS `ProductStyles__order` FROM product_styles ProductStyles 
//WHERE product_id = :c0 ORDER BY order ASC
//die;


     $i = 1;
    foreach ($query as $row) {
        //debug($row); die;
        $ps= $this->ProductStyles->get($row->id);
        //debug($ps);die;
        $ps->order = $i;
        $this->ProductStyles->patchEntity($ps,['order'=> $i]);
        $this->ProductStyles->save($ps);
        $i++;
    }

Upvotes: 1

Views: 1063

Answers (2)

Alvaro Gonzalez
Alvaro Gonzalez

Reputation: 181

order is a reserved keyword in SQL, so, if you want to use as a column name, you need to use quotes, like this:

SELECT * FROM table ORDER BY `order` ASC

As you can see in the error, is not being quoted.

Maybe you have to use $this->Model->query() and write the query manually.

Another solution is change the "order" column to another name.

Hope this helps.

Upvotes: 2

ndm
ndm

Reputation: 60463

order is a reservered word, and is not allowed to be used in an unquoted (in this case non-backticked) fashion, it would need to be used in the form of `order`

See

I would suggest to change the column name, that's the easiest, and non-performance affecting fix. Another option would be to enable automatic identifier quoting, this will however affect the performance, and cannot be applied everywhere, see

Cookbook > Database Access & ORM > Database Basics > Identifier Quoting

Upvotes: 2

Related Questions