Re: Find max(id)

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
-~----------~----~----~----~------~----~------~--~---