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 <[email protected]> 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 [email protected] 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 -~----------~----~----~----~------~----~------~--~---
