zen of coding

Build a string from a resultset array

Sometimes it’s very handy to be able to quickly build a string from CakePHP’s resultset array. For example you want to build a string of ID’s to be used with the “IN” condition in your DB (such as Profile.id IN(1,2,3,4)… ).

Let’s say we did a find(‘all’… and got an array similar to this one:

Array
(
    [0] => Array
        (
            [Account] => Array
                (
                    [id] => 23
                )

        )

    [1] => Array
        (
            [Account] => Array
                (
                    [id] => 24
                )

        )

    [2] => Array
        (
            [Account] => Array
                (
                    [id] => 25
                )

        )

)

Our goal is to extract the ID’s from the above array into 23,24,25.

Here we go (assuming that $acctIds is our resultset array from above):

$stringOfIds = implode(',', Set::extract($acctIds, '{n}.Account.id'));

Cake’s Set class is full of magic and here we are using the extract method to get just the values we are interested in, then we implode the resulting array with a comma.

Note the {n} above. It simply represents a numeric key and therefore allows us to easily follow the path to the desired value in the array.

 

  • vovich

    it is cool!! thank’s a lot of for such information

  • teknoid

    @vovich

    Glad you found it helpful… btw, I should mention that you can just pass an array of id’s to the find() method and it will automatically build an “IN” query.

  • Magic can go a bit further when combined with the Text helper to to convert tag arrays to a human-readable string in just one line:

    Filed under: toList(Set::extract($articles, ‘{n}.Tag.name’)) ?>

    OUTPUT: “Filed under tag1, tag2 and tag3”.

    (Note the commas and the final “and”). Cake’s Set class is amazing. Once you find it, you can’t live withoout it!

  • Oops… WordPress ate part of my code in the previous comment.

  • teknoid

    @Jaime Gómez Obregón

    I am interested to see the full code… would you mind posting a link to a code bin, perhaps?
    Thanks.

  • tm13

    hi, I have a problem. Currently have time in my DB column, so cake renders a time drop down list. When I print out the array, I get this

    Array (
    [hour] => 07
    [min] => 0
    [meridian] => am
    )

    How do I extract this into 1 string, in this case “07:00 am”?

    Thanks

  • @tm13

    This is more of a PHP question, but the most straight forward way would be:

     echo $timeArray['hour'] . ':' . $timeArray['min'] . ' ' . $timeArray['meridian'];
  • Mark

    Thank you kindly for this very useful tip!

  • Russ

    I’ve tried this example with CakePHP 1.3.7:

    I do a $array = $this->Model->find->(‘all’, array($conditions))

    $stringOfIds = implode(‘,’, Set::extract($array, ‘{n}.Model.id’));

    Then $conditions = array(‘Model.id’ => array($stringOfIds));

    $this->data = $this->Model->find(‘all’, array(‘conditions’ => $conditions));

    This produces the following SQL

    WHERE `Model`.`id` = (‘1,19,186’)

    It’s putting quotes around the new string an thus not producing the IN query. I know it’s probably simple, but do you have any idea what’s causing this?

  • teknoid

    @Russ

    You don’t need to bother with this in recent cake versions.
    ‘SomeModel.id’ => array(1, 2, 3, 4, 5);

    Cake will automagically create an IN (1, 2, 3, 4, 5) query for you.

    Also, based on your example you have an array with a single value (i.e. the string) in it. Because of that, you are getting “=” instead of “IN”.

  • Alex

    Nice..

%d bloggers like this: