Re: How compare fields in one query from different tables
I solve my problem!. It necessary to create file of model temp_detail.php
with this content:
class TempDetail extends AppModel {
var $name = 'TempDetail';
var $useTable = *false*;
}
After that everything is works. Here code in the controller:
$tmpModel = 'TempDetail';
$tmpTable = 'temp_details';
$this->Incident->query('CREATE TEMPORARY TABLE temp_details ...');
$this->Incident->query('INSERT INTO temp_details ...');
App::import('Model', 'TempDetail');
$this->loadModel('TempDetail');
--
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
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. With this sql queries I select what I need: 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 != "-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. -- 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
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 != "-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
> And have you tried 'contain' => array('Details'), maybe it will help
>
> 2011/7/5 Quarck :
> > 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 wrote:
> >> Why don't you try to create next condition in your query,
> >> 'Detail.comment_date < Incident.exp_date'
> >>
> >> 2011/6/30 Quarck :
> >>
> >>
> >>
> >>
> >>
> >> > [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.googl
Re: How compare fields in one query from different tables
And have you tried 'contain' => array('Details'), maybe it will help
2011/7/5 Quarck :
> 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 wrote:
>> Why don't you try to create next condition in your query,
>> 'Detail.comment_date < Incident.exp_date'
>>
>> 2011/6/30 Quarck :
>>
>>
>>
>>
>>
>> > [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
Re: How compare fields in one query from different tables
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 wrote:
> Why don't you try to create next condition in your query,
> 'Detail.comment_date < Incident.exp_date'
>
> 2011/6/30 Quarck :
>
>
>
>
>
> > [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
Re: How compare fields in one query from different tables
Why don't you try to create next condition in your query, 'Detail.comment_date < Incident.exp_date' 2011/6/30 Quarck : > [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 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
