Re: custom query returns odd array

[Zoltan]

Anselm,

Thanks that makes sense to me. It's actually not much more work to work
with the data in this format. It's just good to know this is by design
and not my limited knowledge of Cake at work. :)

Thanks,
Zoltan


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake 
PHP" 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: custom query returns odd array

[anselm]

I'm wondering why Cake is returning each row as two separate arrays
instead of just adding the total to the [cities] array. I'm thinking of
just post-processing this to put the total into the cities array, but
I'm wondering if I need to re-state the query a different way?



When there is more than one table in queries, Cake splits the result
into an array, to let you access the data efficently :

SELECT post.title, author.name FROM post, author WHERE author.id =
post.author_id AND post.id = 1

will return something like

array(
 'post' => array(
'title' => '.'
  ),
 'author' => array(
   'name' => ''
 )
)

Other fields, such as the 'count', will go in the '0' index. I'm
relatively new to Cake, so I may be wrong, but as far as I know this is
the way to do this. Also beware - it seems that depending on your mysql
version/configuration, you will get a random stirng instead of '0'. See
those threads :

http://groups-beta.google.com/group/cake-php/browse_thread/thread/9809136278269a49/a57a3ed61b65223f?lnk=gst&q=count&rnum=3#a57a3ed61b65223f
http://groups-beta.google.com/group/cake-php/browse_thread/thread/78fa3ff448d860f/96b5d69df4b42bf8?lnk=gst&q=count&rnum=1#96b5d69df4b42bf8

Anselm


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake 
PHP" 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
-~--~~~~--~~--~--~---

custom query returns odd array

[Zoltan]

Hi,

I'm running a custom query that adds up the total number of
establishments (bars, stores, etc.) in a city and then order then my by
this total:

$aTopCitiesList = $this->query('SELECT `cities`.`id`, `cities`.`name`,
count( `establishments`.`name` ) AS `total`
FROM `cities`
LEFT JOIN `establishments` ON `establishments`.`city_id` =
`cities`.`id`
GROUP BY `cities`.`name`
ORDER BY `total` DESC , `cities`.`name` ASC
LIMIT 12 ');

This returns a result set like:

   [0] => Array
   (
   [cities] => Array
   (
   [id] => 28
   [name] => Barrie
   )

   [0] => Array
   (
   [total] => 181
   )

   )


I'm wondering why Cake is returning each row as two separate arrays
instead of just adding the total to the [cities] array. I'm thinking of
just post-processing this to put the total into the cities array, but
I'm wondering if I need to re-state the query a different way?

(btw: I noticed if I use execute instead of query, the total field is
dropped from the results completely)

Thanks, 
Zoltan 
www.yyztech.ca - Toronto cybercafes, reviews



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake 
PHP" 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
-~--~~~~--~~--~--~---