zen of coding

SELECT … AS … in CakePHP 1.2

This is a simple hint, but hopefully will be useful to some…

You might have noticed some examples where you have:

$this->Profile->find('all', array('fields'=>array('SUM(Profile.votes) AS total_votes')));

But what if you simply needed Profile.field AS another_name?
‘fields’=>array(‘Profile.field AS another_name’) … isn’t going to work, just like that.

What you need to do is escape the fields in your ‘fields’ array, like so:

$this->Profile->find('all', array('fields'=>array('`Profile`.`name` AS `another_name`')));

(Note the backticks)

  • Hi, again, I was wondering about this like 2 hrs (mostly because my internet service was down XD ) until u helped me on the IRC chat so once again thankyou for the help (^.^)

  • teknoid

    @eikishi

    No problem, I figured it might be useful to someone else as well ;)

  • It would be nice to comment that “virtual fields” are going to be returned in the index 0 of the data entry array:

    $data = $this->Profile->find(‘all’, array(‘fields’=>array(‘SUM(Profile.votes) AS total_votes’)));

    $data === array(‘Profile’ => array(‘id’ => 123, ‘status’ => 1), 0 => array(‘total_votes’ => 23));

    So, one should handle it to re-index them, or just be aware of it’s usage.

  • teknoid

    @rafaelbandeira3

    Yes, good point. I’ve always thought it was a little strange…

  • Yes, but it’s the only thing it can do, as Dbo relies on _fetch_field() to map field’s original table, and as virtual table are not from any table they are leaved on the 0 index -> i.e. on DboMysql http://api.cakephp.org/dbo__mysql_8php-source.html#l00436

  • well it cutted of my “Dbo” and “_fetch_field()”

  • teknoid

    @rafaelbandeira3

    I guess you mean the virtual field… but there’s nothing preventing it from being put into the “proper place”, once the result array is built, i.e. in afterFind()…

  • $this->Profile->find(‘all’, array(‘fields’=>array(‘`Profile`.`name` AS `Profile`.`another_name`’)));

    that should ‘auto’ map it to the right model data field in your return array ;)

  • teknoid

    @Christian Winther

    The issue above is that for functions such as SUM() or COUNT() the field appears in and index “below” the rest of the results. For basic SELECT… AS… it works as you’d expect.

  • @teknoid: That’s exactly what I suggested… “The only thing it can do…” refers to the currently used DboSource as it’s the one to fetch and structure the data.

  • teknoid

    @rafaelbandeira3

    Oh, sounds good. It would be good to put a patch together… wish I had a little more free time ;)

  • An easier way would be to use the AliasBehavior by Matthew Harris:

    http://bakery.cakephp.org/articles/view/aliasbehavior

    I have created something similar, only much simpler and for COALESCE() function:

    http://dsi.vozibrale.com/articles/view/simple-coalescebehavior-for-cakephp

  • teknoid

    @dr. Hannibal Lecter

    Thanks for pointing those out. However, the behaviors achieve slightly different things, I’m not sure “easier” is the right term, since this example only shows a basic SELECT… AS… syntax in CakePHP.

  • Agreed, if you want “select…as…” in a single query, then your example is by far simpler and easier :)

  • Dan

    First of all.. thanks for your blog it has been helping me a lot:

    I have a question related to your post above:

    I have a “date” fields in my database with values such as -> 2007-05-01

    I need to select all dates but I just need to retrieve the year, and if possible on the same year, only get DISTINCT years.

    I couldn’t figure out how to use DISTINCT for some reason it didn’t work on the “date” field.

    So I saw somewhere else that I can use DATE_FORMAT inside the query to select only the year portion. But in order for it to work it says I need to use “AS”

    And that’s where your blog came in.. You showed how to use AS, but I couldn’t get it to work with the DATE_FORMAT.

    My objective with this query is to end up with a list of years. So the user can browse by year. I was able to make this work in as a php/mysql query. But couldn’t make it work using the cakephp format find(‘all’)…

  • Dan

    *and if possible on the same QUERY,

    correcting myself.

  • Dan

    If only I was a little smarter… i figured it out what I needed.

    $this->find(‘all’, array(‘fields’=>array(‘DISTINCT DATE_FORMAT(Album.year, “%Y”) AS year’), ‘recursive’=>-1));

  • mathi

    i need coding to populate select box in cakephp
    any body help me

%d bloggers like this: