Re: How to use MySql's CASE WHEN function with CakePhp
Try it
$this->TaskLine->find('all',array('fields' => array((CASE `TaskLine.type`
WHEN '0'
THEN 'Type 0'
WHEN '1'
THEN 'Type 1'
END) AS type"'));
--
View this message in context:
http://old.nabble.com/How-to-use-MySql%27s-CASE-WHEN-function-with-CakePhp-tp19994842p27874105.html
Sent from the CakePHP mailing list archive at Nabble.com.
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Re: How to use MySql's CASE WHEN function with CakePhp
Possibly, but it's worked for quite some time now. Until recently,
cake used raw sql internally for `find('count')`, although now there's
a `DboSource::calculate()` method, so I'm not sure.
I don't like using `query()` unless it's really necessary; it's nice
to get the automagic of `find()`, but sometimes it's good to have a
little extra sql in there.
Any core devs to comment on this?
On Oct 16, 2:07 am, James K <[EMAIL PROTECTED]> wrote:
> In these cases, I think it's more appropriate to just use $this-
>
> >Something->query('SELECT ')
>
> Being able to Inject SQL into the fields collection most likely is not
> by design and something like that has a high chance of not working in
> future updates.
>
> - James
>
> On Oct 15, 11:34 am, MarcS <[EMAIL PROTECTED]> wrote:
>
> > thanks so much
>
> > On Oct 15, 4:57 pm, grigri <[EMAIL PROTECTED]> wrote:
>
> > > Double-up the parentheses:
>
> > > $this->Something->find('all', array(
> > > 'fields' => array(
> > > 'Something.id',
> > > '((CASE WHEN Something.id%2=0 THEN \'even\' ELSE \'odd\' END)) AS
> > > parity'
> > > )
> > > ));
>
> > > Works fine on my (almost latest) build [and has done for a while -
> > > I've used this before]
>
> > > hth
> > > grigri
>
> > > On Oct 15, 3:34 pm, MarcS <[EMAIL PROTECTED]> wrote:
>
> > > > Hi everyone,
>
> > > > I'm trying to figure out how I can use the CASE WHEN function with
> > > > cakephp
>
> > > > I'm trying to run the following query
> > > > SELECT `type` , (
>
> > > > CASE
> > > > WHEN `type` = 'type3'
> > > > THEN 1
> > > > WHEN `type` = ''type4'
> > > > THEN 2
> > > > WHEN `type` = ''type1'
> > > > THEN 3
> > > > ELSE 4
> > > > END
> > > > ) AS sort
> > > > FROM `task_lines` AS `TaskLine`
> > > > ORDER BY `sort` ASC
>
> > > > This is the Cake statement which I've tried:
> > > > $this->TaskLine->find('all',array('fields' => array('type',"(
> > > > CASE
> > > > WHEN `type` = 'type3'
> > > > THEN 1
> > > > WHEN `type` = ''type4'
> > > > THEN 2
> > > > WHEN `type` = ''type1'
> > > > THEN 3
> > > > ELSE 4
> > > > END
> > > > ) AS sort"),'order' => 'sort'));
>
> > > > Cake generates the following query:
>
> > > > SELECT `TaskLine`.`type`, `TaskLine`.`( CASE WHEN `type` = 'type3'
> > > > THEN 1 WHEN `type` = 'type4' THEN 2 WHEN `type` = 'type1' THEN 3 ELSE
> > > > 4 END )` AS `sort` FROM `task_lines` AS `TaskLine` WHERE 1 = 1 ORDER
> > > > BY `sort` ASC
>
> > > > which will obviously result in an SQL error.
>
> > > > Any ideas on how to do this?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: How to use MySql's CASE WHEN function with CakePhp
In these cases, I think it's more appropriate to just use $this-
>Something->query('SELECT ')
Being able to Inject SQL into the fields collection most likely is not
by design and something like that has a high chance of not working in
future updates.
- James
On Oct 15, 11:34 am, MarcS <[EMAIL PROTECTED]> wrote:
> thanks so much
>
> On Oct 15, 4:57 pm, grigri <[EMAIL PROTECTED]> wrote:
>
> > Double-up the parentheses:
>
> > $this->Something->find('all', array(
> > 'fields' => array(
> > 'Something.id',
> > '((CASE WHEN Something.id%2=0 THEN \'even\' ELSE \'odd\' END)) AS
> > parity'
> > )
> > ));
>
> > Works fine on my (almost latest) build [and has done for a while -
> > I've used this before]
>
> > hth
> > grigri
>
> > On Oct 15, 3:34 pm, MarcS <[EMAIL PROTECTED]> wrote:
>
> > > Hi everyone,
>
> > > I'm trying to figure out how I can use the CASE WHEN function with
> > > cakephp
>
> > > I'm trying to run the following query
> > > SELECT `type` , (
>
> > > CASE
> > > WHEN `type` = 'type3'
> > > THEN 1
> > > WHEN `type` = ''type4'
> > > THEN 2
> > > WHEN `type` = ''type1'
> > > THEN 3
> > > ELSE 4
> > > END
> > > ) AS sort
> > > FROM `task_lines` AS `TaskLine`
> > > ORDER BY `sort` ASC
>
> > > This is the Cake statement which I've tried:
> > > $this->TaskLine->find('all',array('fields' => array('type',"(
> > > CASE
> > > WHEN `type` = 'type3'
> > > THEN 1
> > > WHEN `type` = ''type4'
> > > THEN 2
> > > WHEN `type` = ''type1'
> > > THEN 3
> > > ELSE 4
> > > END
> > > ) AS sort"),'order' => 'sort'));
>
> > > Cake generates the following query:
>
> > > SELECT `TaskLine`.`type`, `TaskLine`.`( CASE WHEN `type` = 'type3'
> > > THEN 1 WHEN `type` = 'type4' THEN 2 WHEN `type` = 'type1' THEN 3 ELSE
> > > 4 END )` AS `sort` FROM `task_lines` AS `TaskLine` WHERE 1 = 1 ORDER
> > > BY `sort` ASC
>
> > > which will obviously result in an SQL error.
>
> > > Any ideas on how to do this?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: How to use MySql's CASE WHEN function with CakePhp
thanks so much
On Oct 15, 4:57 pm, grigri <[EMAIL PROTECTED]> wrote:
> Double-up the parentheses:
>
> $this->Something->find('all', array(
> 'fields' => array(
> 'Something.id',
> '((CASE WHEN Something.id%2=0 THEN \'even\' ELSE \'odd\' END)) AS
> parity'
> )
> ));
>
> Works fine on my (almost latest) build [and has done for a while -
> I've used this before]
>
> hth
> grigri
>
> On Oct 15, 3:34 pm, MarcS <[EMAIL PROTECTED]> wrote:
>
> > Hi everyone,
>
> > I'm trying to figure out how I can use the CASE WHEN function with
> > cakephp
>
> > I'm trying to run the following query
> > SELECT `type` , (
>
> > CASE
> > WHEN `type` = 'type3'
> > THEN 1
> > WHEN `type` = ''type4'
> > THEN 2
> > WHEN `type` = ''type1'
> > THEN 3
> > ELSE 4
> > END
> > ) AS sort
> > FROM `task_lines` AS `TaskLine`
> > ORDER BY `sort` ASC
>
> > This is the Cake statement which I've tried:
> > $this->TaskLine->find('all',array('fields' => array('type',"(
> > CASE
> > WHEN `type` = 'type3'
> > THEN 1
> > WHEN `type` = ''type4'
> > THEN 2
> > WHEN `type` = ''type1'
> > THEN 3
> > ELSE 4
> > END
> > ) AS sort"),'order' => 'sort'));
>
> > Cake generates the following query:
>
> > SELECT `TaskLine`.`type`, `TaskLine`.`( CASE WHEN `type` = 'type3'
> > THEN 1 WHEN `type` = 'type4' THEN 2 WHEN `type` = 'type1' THEN 3 ELSE
> > 4 END )` AS `sort` FROM `task_lines` AS `TaskLine` WHERE 1 = 1 ORDER
> > BY `sort` ASC
>
> > which will obviously result in an SQL error.
>
> > Any ideas on how to do this?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: How to use MySql's CASE WHEN function with CakePhp
Double-up the parentheses:
$this->Something->find('all', array(
'fields' => array(
'Something.id',
'((CASE WHEN Something.id%2=0 THEN \'even\' ELSE \'odd\' END)) AS
parity'
)
));
Works fine on my (almost latest) build [and has done for a while -
I've used this before]
hth
grigri
On Oct 15, 3:34 pm, MarcS <[EMAIL PROTECTED]> wrote:
> Hi everyone,
>
> I'm trying to figure out how I can use the CASE WHEN function with
> cakephp
>
> I'm trying to run the following query
> SELECT `type` , (
>
> CASE
> WHEN `type` = 'type3'
> THEN 1
> WHEN `type` = ''type4'
> THEN 2
> WHEN `type` = ''type1'
> THEN 3
> ELSE 4
> END
> ) AS sort
> FROM `task_lines` AS `TaskLine`
> ORDER BY `sort` ASC
>
> This is the Cake statement which I've tried:
> $this->TaskLine->find('all',array('fields' => array('type',"(
> CASE
> WHEN `type` = 'type3'
> THEN 1
> WHEN `type` = ''type4'
> THEN 2
> WHEN `type` = ''type1'
> THEN 3
> ELSE 4
> END
> ) AS sort"),'order' => 'sort'));
>
> Cake generates the following query:
>
> SELECT `TaskLine`.`type`, `TaskLine`.`( CASE WHEN `type` = 'type3'
> THEN 1 WHEN `type` = 'type4' THEN 2 WHEN `type` = 'type1' THEN 3 ELSE
> 4 END )` AS `sort` FROM `task_lines` AS `TaskLine` WHERE 1 = 1 ORDER
> BY `sort` ASC
>
> which will obviously result in an SQL error.
>
> Any ideas on how to do this?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
