Re: How compare fields in one query from different tables

No, it's not work. I guess I figured out how I can solve my problem. I make this sql query:

CREATE TEMPORARY TABLE tmp_details(
id int( 10 ) NOT NULL ,
comment_id int( 5 ) NOT NULL ,
incident_id int( 11 ) NOT NULL ,
PRIMARY KEY ( id )
);
INSERT INTO tmp_details
SELECT Detail.id, MAX( Detail.comment_id ) , Detail.incident_id
FROM details Detail
GROUP BY Detail.incident_id;

SELECT Incident.id, Incident.exp_date, Detail2.comment_date
FROM tmp_details Detail1
INNER JOIN details Detail2 ON Detail1.incident_id = Detail2.incident_id
AND Detail1.comment_id = Detail2.comment_id
INNER JOIN incidents Incident ON Incident.id = Detail1.incident_id
AND Incident.exp_date > Detail2.comment_date
AND Detail2.comment_date != "0000-00-00 00:00:00"

And now there is another problem. How to use temporary tables in Cake? Furthermore I need to paginate results that I will get from last query.

2011/7/6 Dominik Gajewski <dominikgajewski1@gmail.com>

And have you tried 'contain' => array('Details'), maybe it will help

2011/7/5 Quarck <quarckster@gmail.com>:
> I tried
> $this->Incident->find('all', array('conditions' =>
> array('DATE_FORMAT(Detail.comment_date, \'%Y-%m-%d\') <
> Incident.exp_date')));
> and Cake give me an error "SQL Error: 1054: Unknown column
> 'Detail.comment_date' in 'where clause'". And it's absolutely correct
> because sql query is "SELECT `Incident`.`id`, `Incident`.`start_date`,
> `Incident`.`exp_date`, `Incident`.`incoming_num`,
> `Incident`.`incoming_date` FROM `incidents` AS `Incident` WHERE
> DATE_FORMAT(`Detail`.`comment_date`, '%Y-%m-%d') <
> `Incident`.`exp_date`"
> i tried to use joins and it's solved this problem, but how I can
> select row in Detail exactly with last comment_id. I tried this:
> $b = array(
>        array('table' => 'incidents',
>        'alias' => 'Incident1',
>        'type' => 'INNER',
>        'conditions' => array('Detail.incident_id = Incident1.id',
> 'Incident1.exp_date > DATE_FORMAT(Detail.comment_date, \'%Y-%m-%d\')')
>    )
> );
> $this->Incident->Detail->find('all', array('joins' => $b));
>
> On 30 июн, 21:24, Dominik Gajewski <dominikgajews...@gmail.com> wrote:
>> Why don't you try to create next condition in your query,
>> 'Detail.comment_date < Incident.exp_date'
>>
>> 2011/6/30 Quarck <quarcks...@gmail.com>:
>>
>>
>>
>>
>>
>> > [0] => Array
>> > (
>> > [Incident] => Array
>> > (
>> > [id] => 37
>> > [start_date] => 2010-10-08
>> > [exp_date] => 2010-10-14
>> > [incoming_num] => 453-10
>> > [incoming_date] => 2010-10-08
>> > )
>> > [Detail] => Array
>> > (
>> > [0] => Array
>> > (
>> > [id] => 98
>> > [incident_id] => 37
>> > [comment_id] => 1
>> > [comment_date] => 2010-10-11 10:07:29
>> > [notify_only] => 0
>> > )
>> > [1] => Array
>> > (
>> > [id] => 99
>> > [incident_id] => 37
>> > [comment_id] => 2
>> > [comment_date] => 2010-10-11 17:01:09
>> > [notify_only] => 0
>> > )
>> > [2] => Array
>> > (
>> > [id] => 100
>> > [incident_id] => 37
>> > [comment_id] => 3
>> > [comment_date] => 2010-10-12 09:08:01
>> > [notify_only] => 0
>> > )
>> > )
>> > )
>> > [1] => Array
>> > How can I select only those Incident.id, whose last Detail.comment_date <
>> > Incident.exp_date? Is it possible to compare in cakephp fields from
>> > different tables? Thans as advance for any assistant.
>>
>> > --
>> > Our newest site for the community: CakePHP Video Tutorials
>> >http://tv.cakephp.org
>> > Check out the new CakePHP Questions sitehttp://ask.cakephp.organd help
>> > others with their CakePHP related questions.
>>
>> > 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
>>
>> --
>> Pozdrawiam
>> Dominik Gajewski
>
> --
> Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
> Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
>
>
> 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
>



--
Pozdrawiam
Dominik Gajewski

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.


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

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
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