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 <dominikgajews...@gmail.com>
> And have you tried 'contain' => array('Details'), maybe it will help
>
> 2011/7/5 Quarck <quarcks...@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+unsubscr...@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+unsubscr...@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+unsubscr...@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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
