Help doing this in CakePHP

[Chad Casselman]

I have a table that looks like:

id created modified domain_id pages search_engine_id
92 2009-11-02 14:32:11 2009-11-02 14:32:11 2 19990 3
90 2009-11-02 14:32:11 2009-11-02 14:32:11 2 725 1
89 2009-11-02 14:32:10 2009-11-02 14:32:10 1 1250 1
88 2009-11-02 10:00:07 2009-11-02 10:00:07 2 19995 3
87 2009-11-02 10:00:07 2009-11-02 10:00:07 1 9612 3
86 2009-11-02 10:00:07 2009-11-02 10:00:07 2 725 1
85 2009-11-02 10:00:07 2009-11-02 10:00:07 1 1250 1
84 2009-11-02 09:59:47 2009-11-02 09:59:47 2 19995 3
83 2009-11-02 09:59:47 2009-11-02 09:59:47 1 9609 3
82 2009-11-02 09:59:47 2009-11-02 09:59:47 2 725 1
81 2009-11-02 09:59:47 2009-11-02 09:59:47 1 1250 1
80 2009-11-02 09:59:39 2009-11-02 09:59:39 2 19995 3
79 2009-11-02 09:59:39 2009-11-02 09:59:39 1 9609 3
78 2009-11-02 09:59:39 2009-11-02 09:59:39 2 725 1
77 2009-11-02 09:59:39 2009-11-02 09:59:39 1 1250 1

I have spent over half the day trying to figure out how to pull the
last inserted row for each domain_id, search_engine_id pair in
CakePHP.

Can anyone help me figure out how to do this within CakePHP?

I really appreciate your time.
Chad

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

[Pablo Viojo]

Something like this may help:

SELECT domain_id, search_engine_id, MAX(id) FROM table GROUP BY domain_id, search_engine_id

Regards,

Pablo Viojo
[hidden email]
http://pviojo.net

¿Que necesitas?
http://needish.com

On Mon, Nov 2, 2009 at 8:29 PM, Chad Casselman <[hidden email]> wrote:

I have a table that looks like:

id      created         modified        domain_id       pages   search_engine_id
92      2009-11-02 14:32:11     2009-11-02 14:32:11     2       19990   3
90      2009-11-02 14:32:11     2009-11-02 14:32:11     2       725     1
89      2009-11-02 14:32:10     2009-11-02 14:32:10     1       1250    1
88      2009-11-02 10:00:07     2009-11-02 10:00:07     2       19995   3
87      2009-11-02 10:00:07     2009-11-02 10:00:07     1       9612    3
86      2009-11-02 10:00:07     2009-11-02 10:00:07     2       725     1
85      2009-11-02 10:00:07     2009-11-02 10:00:07     1       1250    1
84      2009-11-02 09:59:47     2009-11-02 09:59:47     2       19995   3
83      2009-11-02 09:59:47     2009-11-02 09:59:47     1       9609    3
82      2009-11-02 09:59:47     2009-11-02 09:59:47     2       725     1
81      2009-11-02 09:59:47     2009-11-02 09:59:47     1       1250    1
80      2009-11-02 09:59:39     2009-11-02 09:59:39     2       19995   3
79      2009-11-02 09:59:39     2009-11-02 09:59:39     1       9609    3
78      2009-11-02 09:59:39     2009-11-02 09:59:39     2       725     1
77      2009-11-02 09:59:39     2009-11-02 09:59:39     1       1250    1

I have spent over half the day trying to figure out how to pull the
last inserted row for each domain_id, search_engine_id pair in
CakePHP.

Can anyone help me figure out how to do this within CakePHP?

I really appreciate your time.
Chad

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

[Chad Casselman]

Is there anyway to get all the row information with all those ids or
does it require another query to get row details for returned ids?

Thanks,
Chad

On Mon, Nov 2, 2009 at 6:36 PM, Pablo Viojo <[hidden email]> wrote:

> Something like this may help:
> SELECT domain_id, search_engine_id, MAX(id) FROM table GROUP BY domain_id,
> search_engine_id
> Regards,
>
> Pablo Viojo
> [hidden email]
> http://pviojo.net
>
> ¿Que necesitas?
> http://needish.com
>
>
> On Mon, Nov 2, 2009 at 8:29 PM, Chad Casselman <[hidden email]> wrote:
>>
>> I have a table that looks like:
>>
>> id      created         modified        domain_id       pages
>> search_engine_id
>> 92      2009-11-02 14:32:11     2009-11-02 14:32:11     2       19990   3
>> 90      2009-11-02 14:32:11     2009-11-02 14:32:11     2       725     1
>> 89      2009-11-02 14:32:10     2009-11-02 14:32:10     1       1250    1
>> 88      2009-11-02 10:00:07     2009-11-02 10:00:07     2       19995   3
>> 87      2009-11-02 10:00:07     2009-11-02 10:00:07     1       9612    3
>> 86      2009-11-02 10:00:07     2009-11-02 10:00:07     2       725     1
>> 85      2009-11-02 10:00:07     2009-11-02 10:00:07     1       1250    1
>> 84      2009-11-02 09:59:47     2009-11-02 09:59:47     2       19995   3
>> 83      2009-11-02 09:59:47     2009-11-02 09:59:47     1       9609    3
>> 82      2009-11-02 09:59:47     2009-11-02 09:59:47     2       725     1
>> 81      2009-11-02 09:59:47     2009-11-02 09:59:47     1       1250    1
>> 80      2009-11-02 09:59:39     2009-11-02 09:59:39     2       19995   3
>> 79      2009-11-02 09:59:39     2009-11-02 09:59:39     1       9609    3
>> 78      2009-11-02 09:59:39     2009-11-02 09:59:39     2       725     1
>> 77      2009-11-02 09:59:39     2009-11-02 09:59:39     1       1250    1
>>
>> I have spent over half the day trying to figure out how to pull the
>> last inserted row for each domain_id, search_engine_id pair in
>> CakePHP.
>>
>> Can anyone help me figure out how to do this within CakePHP?
>>
>> I really appreciate your time.
>> Chad
>>
>>
>
>
> >
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

[Pablo Viojo]

You can do something using subqueries (but if you're quering the same table on main and sub query, it will not perform well) 

I recommend to do two queries instead.

Regards,

Pablo Viojo
[hidden email]
http://pviojo.net

¿Que necesitas?
http://needish.com

On Mon, Nov 2, 2009 at 8:49 PM, Chad Casselman <[hidden email]> wrote:

Is there anyway to get all the row information with all those ids or
does it require another query to get row details for returned ids?

Thanks,
Chad

On Mon, Nov 2, 2009 at 6:36 PM, Pablo Viojo <[hidden email]> wrote:
> Something like this may help:
> SELECT domain_id, search_engine_id, MAX(id) FROM table GROUP BY domain_id,
> search_engine_id
> Regards,
>
> Pablo Viojo
> [hidden email]
> http://pviojo.net
>
> ¿Que necesitas?
> http://needish.com
>
>
> On Mon, Nov 2, 2009 at 8:29 PM, Chad Casselman <[hidden email]> wrote:
>>
>> I have a table that looks like:
>>
>> id      created         modified        domain_id       pages
>> search_engine_id
>> 92      2009-11-02 14:32:11     2009-11-02 14:32:11     2       19990   3
>> 90      2009-11-02 14:32:11     2009-11-02 14:32:11     2       725     1
>> 89      2009-11-02 14:32:10     2009-11-02 14:32:10     1       1250    1
>> 88      2009-11-02 10:00:07     2009-11-02 10:00:07     2       19995   3
>> 87      2009-11-02 10:00:07     2009-11-02 10:00:07     1       9612    3
>> 86      2009-11-02 10:00:07     2009-11-02 10:00:07     2       725     1
>> 85      2009-11-02 10:00:07     2009-11-02 10:00:07     1       1250    1
>> 84      2009-11-02 09:59:47     2009-11-02 09:59:47     2       19995   3
>> 83      2009-11-02 09:59:47     2009-11-02 09:59:47     1       9609    3
>> 82      2009-11-02 09:59:47     2009-11-02 09:59:47     2       725     1
>> 81      2009-11-02 09:59:47     2009-11-02 09:59:47     1       1250    1
>> 80      2009-11-02 09:59:39     2009-11-02 09:59:39     2       19995   3
>> 79      2009-11-02 09:59:39     2009-11-02 09:59:39     1       9609    3
>> 78      2009-11-02 09:59:39     2009-11-02 09:59:39     2       725     1
>> 77      2009-11-02 09:59:39     2009-11-02 09:59:39     1       1250    1
>>
>> I have spent over half the day trying to figure out how to pull the
>> last inserted row for each domain_id, search_engine_id pair in
>> CakePHP.
>>
>> Can anyone help me figure out how to do this within CakePHP?
>>
>> I really appreciate your time.
>> Chad
>>
>>
>
>
> >
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

[Chad Casselman]

Any idea how to use this approach with pagination?

Chad

On Mon, Nov 2, 2009 at 7:11 PM, Pablo Viojo <[hidden email]> wrote:

> You can do something using subqueries (but if you're quering the same table
> on main and sub query, it will not perform well)
> I recommend to do two queries instead.
> Regards,
>
> Pablo Viojo
> [hidden email]
> http://pviojo.net
>
> ¿Que necesitas?
> http://needish.com
>
>
> On Mon, Nov 2, 2009 at 8:49 PM, Chad Casselman <[hidden email]> wrote:
>>
>> Is there anyway to get all the row information with all those ids or
>> does it require another query to get row details for returned ids?
>>
>> Thanks,
>> Chad
>>
>> On Mon, Nov 2, 2009 at 6:36 PM, Pablo Viojo <[hidden email]> wrote:
>> > Something like this may help:
>> > SELECT domain_id, search_engine_id, MAX(id) FROM table GROUP
>> > BY domain_id,
>> > search_engine_id
>> > Regards,
>> >
>> > Pablo Viojo
>> > [hidden email]
>> > http://pviojo.net
>> >
>> > ¿Que necesitas?
>> > http://needish.com
>> >
>> >
>> > On Mon, Nov 2, 2009 at 8:29 PM, Chad Casselman <[hidden email]>
>> > wrote:
>> >>
>> >> I have a table that looks like:
>> >>
>> >> id      created         modified        domain_id       pages
>> >> search_engine_id
>> >> 92      2009-11-02 14:32:11     2009-11-02 14:32:11     2       19990
>> >> 3
>> >> 90      2009-11-02 14:32:11     2009-11-02 14:32:11     2       725
>> >> 1
>> >> 89      2009-11-02 14:32:10     2009-11-02 14:32:10     1       1250
>> >>  1
>> >> 88      2009-11-02 10:00:07     2009-11-02 10:00:07     2       19995
>> >> 3
>> >> 87      2009-11-02 10:00:07     2009-11-02 10:00:07     1       9612
>> >>  3
>> >> 86      2009-11-02 10:00:07     2009-11-02 10:00:07     2       725
>> >> 1
>> >> 85      2009-11-02 10:00:07     2009-11-02 10:00:07     1       1250
>> >>  1
>> >> 84      2009-11-02 09:59:47     2009-11-02 09:59:47     2       19995
>> >> 3
>> >> 83      2009-11-02 09:59:47     2009-11-02 09:59:47     1       9609
>> >>  3
>> >> 82      2009-11-02 09:59:47     2009-11-02 09:59:47     2       725
>> >> 1
>> >> 81      2009-11-02 09:59:47     2009-11-02 09:59:47     1       1250
>> >>  1
>> >> 80      2009-11-02 09:59:39     2009-11-02 09:59:39     2       19995
>> >> 3
>> >> 79      2009-11-02 09:59:39     2009-11-02 09:59:39     1       9609
>> >>  3
>> >> 78      2009-11-02 09:59:39     2009-11-02 09:59:39     2       725
>> >> 1
>> >> 77      2009-11-02 09:59:39     2009-11-02 09:59:39     1       1250
>> >>  1
>> >>
>> >> I have spent over half the day trying to figure out how to pull the
>> >> last inserted row for each domain_id, search_engine_id pair in
>> >> CakePHP.
>> >>
>> >> Can anyone help me figure out how to do this within CakePHP?
>> >>
>> >> I really appreciate your time.
>> >> Chad
>> >>
>> >>
>> >
>> >
>> > >
>> >
>>
>>
>
>
> >
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

[schneimi]

Hi,

if I understood you right, this could be what you need:

$result = $this->YourModel->find('all', array('group' => array
(domain_'id', 'search_engine_id'),
                                                               'order'
=> 'MAX(created)'));

With pagination it should look like this:

    $this->paginate['YourModel'] = array('group' => array(domain_'id',
'search_engine_id'),
                                                        'order' => 'MAX
(created)');
    $this->paginate('YourModel');

In both cases you can get related data with the 'contain' option in
the array.

Hope this helps,

Michael

On 3 Nov., 00:29, Chad Casselman <[hidden email]> wrote:

> I have a table that looks like:
>
> id      created         modified        domain_id       pages   search_engine_id
> 92      2009-11-02 14:32:11     2009-11-02 14:32:11     2       19990   3
> 90      2009-11-02 14:32:11     2009-11-02 14:32:11     2       725     1
> 89      2009-11-02 14:32:10     2009-11-02 14:32:10     1       1250    1
> 88      2009-11-02 10:00:07     2009-11-02 10:00:07     2       19995   3
> 87      2009-11-02 10:00:07     2009-11-02 10:00:07     1       9612    3
> 86      2009-11-02 10:00:07     2009-11-02 10:00:07     2       725     1
> 85      2009-11-02 10:00:07     2009-11-02 10:00:07     1       1250    1
> 84      2009-11-02 09:59:47     2009-11-02 09:59:47     2       19995   3
> 83      2009-11-02 09:59:47     2009-11-02 09:59:47     1       9609    3
> 82      2009-11-02 09:59:47     2009-11-02 09:59:47     2       725     1
> 81      2009-11-02 09:59:47     2009-11-02 09:59:47     1       1250    1
> 80      2009-11-02 09:59:39     2009-11-02 09:59:39     2       19995   3
> 79      2009-11-02 09:59:39     2009-11-02 09:59:39     1       9609    3
> 78      2009-11-02 09:59:39     2009-11-02 09:59:39     2       725     1
> 77      2009-11-02 09:59:39     2009-11-02 09:59:39     1       1250    1
>
> I have spent over half the day trying to figure out how to pull the
> last inserted row for each domain_id, search_engine_id pair in
> CakePHP.
>
> Can anyone help me figure out how to do this within CakePHP?
>
> I really appreciate your time.
> Chad

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

[Chad Casselman]

Just to clarify, neither of the above works do to the fact the group
happens before the MAX, so MAX does not always return the max value,
normally the first.

Would still like to find a way to pull the latest row for set of data.

Chad

On Tue, Nov 3, 2009 at 5:44 AM, schneimi <[hidden email]> wrote:

>
> Hi,
>
> if I understood you right, this could be what you need:
>
> $result = $this->YourModel->find('all', array('group' => array
> (domain_'id', 'search_engine_id'),
>                                                               'order'
> => 'MAX(created)'));
>
> With pagination it should look like this:
>
>    $this->paginate['YourModel'] = array('group' => array(domain_'id',
> 'search_engine_id'),
>                                                        'order' => 'MAX
> (created)');
>    $this->paginate('YourModel');
>
> In both cases you can get related data with the 'contain' option in
> the array.
>
> Hope this helps,
>
> Michael
>
> On 3 Nov., 00:29, Chad Casselman <[hidden email]> wrote:
>> I have a table that looks like:
>>
>> id      created         modified        domain_id       pages   search_engine_id
>> 92      2009-11-02 14:32:11     2009-11-02 14:32:11     2       19990   3
>> 90      2009-11-02 14:32:11     2009-11-02 14:32:11     2       725     1
>> 89      2009-11-02 14:32:10     2009-11-02 14:32:10     1       1250    1
>> 88      2009-11-02 10:00:07     2009-11-02 10:00:07     2       19995   3
>> 87      2009-11-02 10:00:07     2009-11-02 10:00:07     1       9612    3
>> 86      2009-11-02 10:00:07     2009-11-02 10:00:07     2       725     1
>> 85      2009-11-02 10:00:07     2009-11-02 10:00:07     1       1250    1
>> 84      2009-11-02 09:59:47     2009-11-02 09:59:47     2       19995   3
>> 83      2009-11-02 09:59:47     2009-11-02 09:59:47     1       9609    3
>> 82      2009-11-02 09:59:47     2009-11-02 09:59:47     2       725     1
>> 81      2009-11-02 09:59:47     2009-11-02 09:59:47     1       1250    1
>> 80      2009-11-02 09:59:39     2009-11-02 09:59:39     2       19995   3
>> 79      2009-11-02 09:59:39     2009-11-02 09:59:39     1       9609    3
>> 78      2009-11-02 09:59:39     2009-11-02 09:59:39     2       725     1
>> 77      2009-11-02 09:59:39     2009-11-02 09:59:39     1       1250    1
>>
>> I have spent over half the day trying to figure out how to pull the
>> last inserted row for each domain_id, search_engine_id pair in
>> CakePHP.
>>
>> Can anyone help me figure out how to do this within CakePHP?
>>
>> I really appreciate your time.
>> Chad
> >
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

[itc-media.com]

how about using UNION ?

SELECT domain_id, search_engine_id, created, modified,  MAX(id) FROM
table GROUP BY domain_id
UNION
SELECT domain_id, search_engine_id, created, modified,  MAX(id) FROM
table GROUP BY search_engine_id


Regards,
Dragos

On Nov 4, 8:00 pm, Chad Casselman <[hidden email]> wrote:

> Just to clarify, neither of the above works do to the fact the group
> happens before the MAX, so MAX does not always return the max value,
> normally the first.
>
> Would still like to find a way to pull the latest row for set of data.
>
> Chad
>
>
>
> On Tue, Nov 3, 2009 at 5:44 AM, schneimi <[hidden email]> wrote:
>
> > Hi,
>
> > if I understood you right, this could be what you need:
>
> > $result = $this->YourModel->find('all', array('group' => array
> > (domain_'id', 'search_engine_id'),
> >                                                               'order'
> > => 'MAX(created)'));
>
> > With pagination it should look like this:
>
> >    $this->paginate['YourModel'] = array('group' => array(domain_'id',
> > 'search_engine_id'),
> >                                                        'order' => 'MAX
> > (created)');
> >    $this->paginate('YourModel');
>
> > In both cases you can get related data with the 'contain' option in
> > the array.
>
> > Hope this helps,
>
> > Michael
>
> > On 3 Nov., 00:29, Chad Casselman <[hidden email]> wrote:
> >> I have a table that looks like:
>
> >> id      created         modified        domain_id       pages   search_engine_id
> >> 92      2009-11-02 14:32:11     2009-11-02 14:32:11     2       19990   3
> >> 90      2009-11-02 14:32:11     2009-11-02 14:32:11     2       725     1
> >> 89      2009-11-02 14:32:10     2009-11-02 14:32:10     1       1250    1
> >> 88      2009-11-02 10:00:07     2009-11-02 10:00:07     2       19995   3
> >> 87      2009-11-02 10:00:07     2009-11-02 10:00:07     1       9612    3
> >> 86      2009-11-02 10:00:07     2009-11-02 10:00:07     2       725     1
> >> 85      2009-11-02 10:00:07     2009-11-02 10:00:07     1       1250    1
> >> 84      2009-11-02 09:59:47     2009-11-02 09:59:47     2       19995   3
> >> 83      2009-11-02 09:59:47     2009-11-02 09:59:47     1       9609    3
> >> 82      2009-11-02 09:59:47     2009-11-02 09:59:47     2       725     1
> >> 81      2009-11-02 09:59:47     2009-11-02 09:59:47     1       1250    1
> >> 80      2009-11-02 09:59:39     2009-11-02 09:59:39     2       19995   3
> >> 79      2009-11-02 09:59:39     2009-11-02 09:59:39     1       9609    3
> >> 78      2009-11-02 09:59:39     2009-11-02 09:59:39     2       725     1
> >> 77      2009-11-02 09:59:39     2009-11-02 09:59:39     1       1250    1
>
> >> I have spent over half the day trying to figure out how to pull the
> >> last inserted row for each domain_id, search_engine_id pair in
> >> CakePHP.
>
> >> Can anyone help me figure out how to do this within CakePHP?
>
> >> I really appreciate your time.
> >> Chad

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---