How to convert this update QUERY to ActiveRecord / Forums / Community / EllisLab

Forum Logo

How to convert this update QUERY to ActiveRecord

Posted: 03 July 2007 08:44 AM

[ Ignore ]

Joined: 2006-12-28
24 posts

I have this query

$this->db->query('UPDATE '.$this->dbprefix.'menu SET ord_id = ord_id-1 WHERE ord_id > ?', $row['ord_id']); 

but I want to make this with ActiveRecord

some think like this

$this->db->set('ord_id = ord_id-1');
$this->db->where('ord_id >', $row['ord_id']);
$this->db->update('menu'); 

can some one help with this problem?

weremax

Posted: 04 July 2007 02:24 AM

[ Ignore ] [ # 1 ] [ Rating: 0 ]

Joined: 2007-06-13
70 posts

First you have to of course obtain the $row[‘ord_id’] and then this is what I believe you are looking for:

$data = array(
   'ord_id' => ($row['ord_id'] - 1),
   );
$this->db->where('ord_id >', $row['ord_id']);
$this->db->update('menu', $data); 

Basically this will update the field subtracting 1 from it each time it is accessed.

MpaK69

Posted: 04 July 2007 02:26 AM

[ Ignore ] [ # 2 ] [ Rating: 0 ]

Joined: 2006-12-28
24 posts

nop :(
if I have now more than 2000 records! If I will do it in cycle that will be tragedy

weremax

Posted: 04 July 2007 02:32 AM

[ Ignore ] [ # 3 ] [ Rating: 0 ]

Joined: 2007-06-13
70 posts

Yeah with that many records that would not work very well. Oh i think i understand what you are looking for now you want it to actively update that fields ord_id subtracting 1 from it for anything greater than the supplied row ord_id.

For example
here would be 3 ord_id fields in the db.
127
145
198

and the supplied row ord_id was 129.

127 would not be touched while
145 would be updated to 144
and 198 would be updateed to 197

MpaK69

Posted: 04 July 2007 03:19 AM

[ Ignore ] [ # 4 ] [ Rating: 0 ]

Joined: 2006-12-28
24 posts

yeah, ok, I understand I cant do this with AC pattern

I did it with query

if($neword < $oldord){
            // UP
            $this->db->query('UPDATE '.$this->dbprefix.'menu SET ord_id=ord_id+1 WHERE ord_id >= ? AND ord_id <= ? AND pid = ?', array($neword, $oldord, $pid) );
            $this->db->set('ord_id', $neword);
            $this->db->where('uid', $lastid);
            $this->db->update('menu');
        }elseif($neword > $oldord){
            // DOWN
            $this->db->query('UPDATE '.$this->dbprefix.'menu SET ord_id=ord_id-1 WHERE ord_id <= ? AND ord_id >= ? AND pid = ?', array($neword, $oldord, $pid) );
            $this->db->set('ord_id', $neword);
            $this->db->where('uid', $lastid);
            $this->db->update('menu');
        } 