Re: Find max(id)

Oh right I see what it's doing now. I would say that it's a bug then. CakePHP should not generate invalid SQL queries. Although I'm pretty sure Cake adds the id field to the field list because it is required for recursively finding associated records, it could prob be modified to always add the MAX() command around the id field it when using "group by", for mssql at least.

Time for a ticket I reckon. You can submit it via trac.cakephp.org

Cheers,
Adam


blake wrote:

I don't think its a bug, I think its more of the way Cake generates queries. Even though the Model.id field was not specified in the find (), Cake includes it in the select anyway. MS SQL complains about the GROUP BY when you don't also include Model.id in the group, since it is being selected. I'm actually not sure why MySQL accepts the query without id in the group, but it does.  I really wanted to avoid using manual queries with this app, since I develop it with MySQL but the production server is MS SQL. So far, everything I've written works on both databases until this.  Is there some way to force Cake to not select Model.id also? Any ideas on an alternative way to do this? It really sucks if I have to start doing manual queries after getting so far without them.   Thanks,  -Blake    On Mar 2, 7:16 pm, Adam Royle <a...@sleekgeek.com.au> wrote:   

I think the majority of CakePHP users are using MySQL, so it could be a bug in the cake's MSSQL datasource. You should do some investigating into what query is generated, does it run if you try it through sql server directly? If not, what needs to be changed for it to work? Then look at the mssql datasource and see if it's a bug or whether it could be improved upon. If not then you may just need to execute a raw sql query instead of using the find methods.  Cheers, Adam  blake wrote:     

I'm trying to do the following find() to find the highest id for each name value in the table.       

$result = $this->Model->find('all', array('conditions' =>  array ('Model.parent_id' =>  $parent_id),                                                            'fields' =>  array('MAX(Model.id) as max_id', 'Model.name'),                                                            'recursive' =>  1,                                                            'group' =>  array('Model.name')                                            ));       

For the example table:       

1    ItemA 2    ItemB 3    ItemC 4    ItemB 5    ItemA       

I want the result (any order):       

5    ItemA 4    ItemB 3    ItemC       

The find() works in MySQL, but not in MS SQL. MS SQL wants me to group on Model.id also, which then returns more results than I want. What is the best way for me to do this query?       

--~--~---------~--~----~------------~-------~--~----~
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+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---