Increment value using CakePHP model functions

Discussion:

Dave

2009-11-20 12:46:16 UTC

Hi everybody,

I have a quick question that bugs me each time I run into it in a project,
and then I promptly forget about it. It is an easy fix, but it is such
common functionality that I feel there must be some way to do it within
built in functions rather then a custom query.

The issue is incrementing a table field without grabbing the previous value,
using MySQL's default function `tablefield` = `tablefield` + 1 .

example:

$this->Item->query('UPDATE `items` SET `flags` = `flags`+1 WHERE
`items`.`id` = ' . $id);

I have tried pretty much every way I can think of with model->saveField to
accomplish this through cake, but I can't get it to work.

First I set the id with $this->Item->id = $id
After that I have tried

$this->Item->saveField('flags','+1');
$this->Item->saveField('flags',' +1');
$this->Item->saveField('flags', 'flags+1');
$this->Item->saveField('flags', '`flags`+1');

So... is there a way to do this with 1 query within cake? I like to have as
few custom queries as possible within my application.

Thanks for any tips,

who knows, if it isn't built in maybe I will be able to make my first
contribution to the Core!

Dave

--

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to cake-php+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit this group at http://groups.google.com/group/cake-php?hl=.

jburns

2009-11-20 13:50:32 UTC

Permalink

I think this might point you in the right direction:
http://book.cakephp.org/view/75/Saving-Your-Data

,,,particularly this piece:

$this->Post->read(null, 1);
$this->Post->set(array(
'title' => 'New title',
'published' => false
));
$this->Post->save();

Post by Dave
Hi everybody,
I have a quick question that bugs me each time I run into it in a project,
and then I promptly forget about it. It is an easy fix, but it is such
common functionality that I feel there must be some way to do it within
built in functions rather then a custom query.
The issue is incrementing a table field without grabbing the previous value,
using MySQL's default function `tablefield` = `tablefield` + 1 .
$this->Item->query('UPDATE `items` SET `flags` = `flags`+1 WHERE
`items`.`id` = ' . $id);
I have tried pretty much every way I can think of with model->saveField to
accomplish this through cake, but I can't get it to work.
First I set the id with $this->Item->id = $id
After that I have tried
$this->Item->saveField('flags','+1');
$this->Item->saveField('flags',' +1');
$this->Item->saveField('flags', 'flags+1');
$this->Item->saveField('flags', '`flags`+1');
So... is there a way to do this with 1 query within cake? I like to have as
few custom queries as possible within my application.
Thanks for any tips,
who knows, if it isn't built in maybe I will be able to make my first
contribution to the Core!
Dave

--

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to cake-php+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cake-php?hl=.

grigri

2009-11-20 14:45:25 UTC

Permalink

It's possible in two ways.

First, you've got the `updateAll` function which doesn't escape or
mess with your arguments:

$this->Item->updateAll(
array('Item.flags' => 'Item.flags + 1'),
array('Item.id' => 666)
);

One possible problem with this is that it will not call `afterSave`,
trigger behaviour methods, etc... because it's not really a save.
If you need this to work with the rest, the solution is a bit more
long-winded:

$db =& ConnectionManager::getDataSource($this->Item->useDbConfig);
$this->Item->set('flags', $db->expression('`Item`.`flags`+1'));
$this->Item->save();

In reality, accessing the datasource and whatnot should be hidden in
the model, but this shows a bare-bones way of getting it working.

hth
grigri

Dave

2009-11-20 18:44:34 UTC

Permalink

jburns, if you set your debug level to 2 you will see that performs 2
queries AND gets a bunch of data I don't need about the item...

grigri that is very helpful to know that updateAll doesn't escape your
arguments.

I don't need to afterSave currently, but it is good to know I can do it the
second way as well.

Thank you very much.

Post by grigri
It's possible in two ways.
First, you've got the `updateAll` function which doesn't escape or
$this->Item->updateAll(
array('Item.flags' => 'Item.flags + 1'),
array('Item.id' => 666)
);
One possible problem with this is that it will not call `afterSave`,
trigger behaviour methods, etc... because it's not really a save.
If you need this to work with the rest, the solution is a bit more
$db =& ConnectionManager::getDataSource($this->Item->useDbConfig);
$this->Item->set('flags', $db->expression('`Item`.`flags`+1'));
$this->Item->save();
In reality, accessing the datasource and whatnot should be hidden in
the model, but this shows a bare-bones way of getting it working.
hth
grigri

Post by Dave
Hi everybody,
I have a quick question that bugs me each time I run into it in a

project,

Post by Dave
and then I promptly forget about it. It is an easy fix, but it is such
common functionality that I feel there must be some way to do it within
built in functions rather then a custom query.
The issue is incrementing a table field without grabbing the previous

value,

Post by Dave
using MySQL's default function `tablefield` = `tablefield` + 1 .
$this->Item->query('UPDATE `items` SET `flags` = `flags`+1 WHERE
`items`.`id` = ' . $id);
I have tried pretty much every way I can think of with model->saveField

Post by Dave
accomplish this through cake, but I can't get it to work.
First I set the id with $this->Item->id = $id
After that I have tried
$this->Item->saveField('flags','+1');
$this->Item->saveField('flags',' +1');
$this->Item->saveField('flags', 'flags+1');
$this->Item->saveField('flags', '`flags`+1');
So... is there a way to do this with 1 query within cake? I like to have

Post by Dave
few custom queries as possible within my application.
Thanks for any tips,
who knows, if it isn't built in maybe I will be able to make my first
contribution to the Core!
Dave

--
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group, send email to
.
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=.

--

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to cake-php+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit this group at http://groups.google.com/group/cake-php?hl=.

jburns

2009-11-21 05:39:01 UTC

Permalink

You are right.

Can I ask if you are using recursion and containable? If so, you can
be very specific about what tables, records and fields are returned.
I'm still fairly new with Cake but have realised that it is a good
thing to implement these in AppModel and then I have complete control
over data returned within my models and controller functions. It's a
bit more work for me, but well worth it in the performance stakes.

With recursion set at -1 you will only get records from the current
table (in other words, no associated tables). When you want to bring
in associations, increase recursive to 0 or more, then use contain to
bring in the associations. You can specify fields from those
associations too:

$this->Post->contain = array(
'Author' => array(
'fields' => array ('id', 'first_name', 'last_name')
)
);

In your case, with recursion set at -1 you can call...

$this->Post->read(array('title', 'Published'), 1);
$this->Post->set(array(
'title' => 'New title',
'published' => false
));
$this->Post->save();

...and this will only bring back the value of 'title' and 'published'
in the record with an id of 1 and nothing else. That has to be right
on the money, doesn't it?

Post by Dave
jburns, if you set your debug level to 2 you will see that performs 2
queries AND gets a bunch of data I don't need about the item...
grigri that is very helpful to know that updateAll doesn't escape your
arguments.
I don't need to afterSave currently, but it is good to know I can do it the
second way as well.
Thank you very much.

Post by Dave
Hi everybody,
I have a quick question that bugs me each time I run into it in a

project,

value,

Post by Dave
few custom queries as possible within my application.
Thanks for any tips,
who knows, if it isn't built in maybe I will be able to make my first
contribution to the Core!
Dave

--
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To unsubscribe from this group, send email to
.
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=.

Dave

2009-11-21 16:52:09 UTC

Permalink

Hehe it is close to on the money. I do appreciate your help. The goal of
this question was to increment a value with 1 query. You can do this with
MySQL with a simple query settings field = field + 1. I was just wondering
if there was a way to do this within the core as usually your fields are
escaped in cake functions. I have figured out my problem. Thank you
though.

Dave

Post by jburns
You are right.
Can I ask if you are using recursion and containable? If so, you can
be very specific about what tables, records and fields are returned.
I'm still fairly new with Cake but have realised that it is a good
thing to implement these in AppModel and then I have complete control
over data returned within my models and controller functions. It's a
bit more work for me, but well worth it in the performance stakes.
With recursion set at -1 you will only get records from the current
table (in other words, no associated tables). When you want to bring
in associations, increase recursive to 0 or more, then use contain to
bring in the associations. You can specify fields from those
$this->Post->contain = array(
'Author' => array(
'fields' => array ('id', 'first_name', 'last_name')
)
);
In your case, with recursion set at -1 you can call...
$this->Post->read(array('title', 'Published'), 1);
$this->Post->set(array(
'title' => 'New title',
'published' => false
));
$this->Post->save();
...and this will only bring back the value of 'title' and 'published'
in the record with an id of 1 and nothing else. That has to be right
on the money, doesn't it?

the

Post by Dave
second way as well.
Thank you very much.

Post by Dave
Hi everybody,
I have a quick question that bugs me each time I run into it in a

project,

Post by Dave
and then I promptly forget about it. It is an easy fix, but it is

such