Complex find to generate totals
Hi All! I have a problem with a complicated find and I'm stumped... My database tables are: Products - id - name ... Orders - id - status ... OrderItems - id - order_id - product_id - quantity - line_total ... And Models are: Orders hasMany OrderItems OrderItems belongTo Products, Orders And I am trying to query all Products to get total number of OrderItems and sum of line_totals where the Order status is completed... So I can generate a table: Product Name | Number of Sales | Sales Total A Bag | 10 | £50.00 ... Can anyone help? I'm getting nowhere fast... -- Like Us on FaceBook https://www.facebook.com/CakePHP Find us on Twitter http://twitter.com/CakePHP --- You received this message because you are subscribed to the Google Groups "CakePHP" group. To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscr...@googlegroups.com. To post to this group, send email to cake-php@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php. For more options, visit https://groups.google.com/groups/opt_out.
Re: complex find()
Needs cleanup and have to put in model. I had to call it on the user
model.
looped thru results and built a new array of user id's. passed array as
condition to paginate.
$from = $this->data['User']['start_date']['year'] . "-" .
$this->data['User']['start_date']['month'] . "-" .
$this->data['User']['start_date']['day'];
$to = $this->data['User']['end_date']['year'] . "-" .
$this->data['User']['end_date']['month'] . "-" .
$this->data['User']['end_date']['day'];
$cond = array('Attendance.created BETWEEN ? AND ?' => array($from,
$to), 'Attendance.school_id' => $session_school_id);
$classes_from_form = $this->data['User']['type'];
$user_list = array();
$users = $this->User->find('all', array('contain' =>
array('Attendance' => array('conditions' => $cond), 'Usermembership' )));
foreach($users as $user){
$count = 0;
$user_id = $user['User']['id'];
foreach($user['Attendance'] as $attendance){
if(($attendance['user_id'] == $user_id) &&
(isset($user['Usermembership']))){
foreach($user['Usermembership'] as $membership){
//check for a valid membership
if(($membership['status'] == 1) &&
($membership['end_date'] >= date('Y-m-d'))){
$count++;
if($count == $classes_from_form){
//build array of user id's
$user_list[]= $user['User']['id'];
}
}
}
}
}
}
$cond3 = array('User.id' => $user_list);
$this->set('attendances', $this->paginate($cond3));
--
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Hi Rockbust, how did you solved to get user data which Attendance match from the array of Attendance u got? I have same issue (with different data but same relations) and I'd appreciate your help. Thanks On Thursday, August 30, 2012 8:48:02 PM UTC-5, rockbust wrote: > > ok figured it out now.. > thanks lowpass > > robert > -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Forgot to add
$this->User->Behaviors->attach('Containable');
wouldn't work without it
--
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
ok figured it out now.. thanks lowpass robert -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Understood. Thank you... Only problem I have is that now the structure is not desirable to test if a user has the correct amount of attendances as set in the form. Returned data example: Array ( [0] => Array ( [Attendance] => Array ( [id] => 85 [created] => 2012-08-28 [modified] => 2012-08-29 06:27:04 [user_id] => 129 [program_id] => 1 [school_id] => 1 ) [User] => Array ( [id] => 129 ) ) [1] => Array ( [Attendance] => Array ( [id] => 84 [created] => 2012-08-26 [modified] => 2012-08-29 06:25:18 [user_id] => 107 [program_id] => 1 [school_id] => 1 ) [User] => Array ( [id] => 107 ) ) [2] => Array ( [Attendance] => Array ( [id] => 83 [created] => 2012-08-25 [modified] => 2012-08-29 06:24:53 [user_id] => 128 [program_id] => 1 [school_id] => 1 ) [User] => Array ( [id] => 128 ) ) [3] => Array ( [Attendance] => Array ( [id] => 81 [created] => 2012-08-28 [modified] => 2012-08-29 03:30:59 [user_id] => 107 [program_id] => 1 [school_id] => 1 ) [User] => Array ( [id] => 107 ) ) For example if $form_attendance = 2 I would like to do a simple foreach on the attendance to see if the count matches $form_attendance. In the format that is returned I do not now how to do this :( Thank you Robert -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
No, you need to run the find on Attendance because your condition is
on that model. User should be in the contain array.
However, your contain syntax is also wrong. It should be like:
$this->find(
'all',
array(
'contain' => array(
'ContainedModelName' => array(
'conditions' => array(
// some conditions
)
)
)
)
);
But, again, you don't want to be doing that here because it would
fetch ALL Users, along with an Attendance array for each User record.
That array would be empty for those Attendance records that don't
match the conditions. What you want instead is to run a find on
Attendance to grab those the conditions match. Containable will then
subsequently grab all the matching Users.
On Thu, Aug 30, 2012 at 6:52 PM, Robert Gravel wrote:
> Hmm does not seem to work. This is now what I am working on.
>
> As a test I ran this on my User Controller that has the related Attendance
>
> $users = $this->User->find('all', array('contain' => 'Attendance.school_id =
> "2"'));
>
> Instead of returning all user records that related Attendance.school_id =
> "2" it gives me every user in the database.
> And oddly for the records that do have attendance records the attendance is
> blank in every record.
> example:
>
> [86] => Array
> (
>[User] => Array
> (
> [id] => 960
> [group_id] => 2
> [last_name] => Camier
> [school_id] => 1
> )
>
> [Attendance] => Array
> (
> )
>
> )
>
> [87] => Array
> (
> [User] => Array
> (
> [id] => 961
> [group_id] => 2
> [last_name] => Miccio
> [school_id] => 1
> )
>
> [Attendance] => Array
> (
> )
>
> )
>
>
>
>
> --
> 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+unsubscr...@googlegroups.com.
> Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
>
>
--
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Maybe I am confused how containable works. I want to return only those user records whose associated attendance match. -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Hmm does not seem to work. This is now what I am working on.
As a test I ran this on my User Controller that has the related Attendance
$users = $this->User->find('all', array('contain' => 'Attendance.school_id
= "2"'));
Instead of returning all user records that related Attendance.school_id =
"2" it gives me every user in the database.
And oddly for the records that do have attendance records the attendance is
blank in every record.
example:
[86] => Array
(
[User] => Array
(
[id] => 960
[group_id] => 2
[last_name] => Camier
[school_id] => 1
)
[Attendance] => Array
(
)
)
[87] => Array
(
[User] => Array
(
[id] => 961
[group_id] => 2
[last_name] => Miccio
[school_id] => 1
)
[Attendance] => Array
(
)
)
--
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Are you running the find() on the User or Attendance model? It looks
to me like it's the former.
Anyway, you could tighten this up a little by putting some code in the
model, and by adding a helper function to bootstrap.php:
function dateStr($a)
{
if (!empty($a['year']) && !empty($a['month']) && !empty($a['day']))
{
return "${a['year']}-${a['month']}-${a['day']}";
}
return null;
}
Also, you can put the school_id in the forma as well, instead of
passing it through the session.
public function admin_attendance_by_date_range()
{
$data = $this->User->attendanceByDateRange(
$this->data['Attendance']['school_id'],
dateStr($this->data['Attendance']['start_date']),
dateStr($this->data['Attendance']['end_date'])
);
...
}
User model:
public function attendanceByDateRange($school_id, $from, $to)
{
return $this->Attendance->find(
'all',
array(
'conditions' => array(
'Attendance.created BETWEEN ? AND ?' =>
array($from, $to),
'Attendance.school_id' => $school_id
),
'contain' => array(
'User' => array(
'fields' => array(
'User.id',
// other User fields you want
)
)
)
)
);
}
On Wed, Aug 29, 2012 at 2:16 AM, rockbust wrote:
> If anyone can point me in the right direction.. It will be much appreciated
>
> I am trying to return a list of User where Attendance.created is between a
> date specified in my form.
> Further Attendance.created needs to be equal or greater than
> attendance_count input from the form.
> Attendance belongsTo User
>
> I have tried this but get sql error Unknown column 'Attendance.created'
>
> function admin_attendance_by_date_range() {
> $this->Attendance->recursive = 0;
> $session_school_id =
> $this->Session->read('Userinfo.currentSchoolid');
>
> $from =
> $this->data['Attendance']['start_date']['year'] . "-" .
> $this->data['Attendance']['start_date']['month'] . "-" .
> $this->data['Attendance']['start_date']['day'];
> $to = $this->data['Attendance']['end_date']['year']
> . "-" .
> $this->data['Attendance']['end_date']['month'] . "-" .
> $this->data['Attendance']['end_date']['day'];
> $cond = array('Attendance.created BETWEEN ? AND ?' =>
> array($from, $to),
> 'Attendance.school_id' => $session_school_id);
>
> Robert
>
>
>
>
> --
> View this message in context:
> http://cakephp.1045679.n5.nabble.com/complex-find-tp5710758.html
> Sent from the CakePHP mailing list archive at Nabble.com.
>
> --
> 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+unsubscr...@googlegroups.com.
> Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
>
>
--
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Thank you. Yes the field is valid. both created and school_id here are my models user: var $hasMany = array( 'Attendance' => array( 'className' => 'Attendance', 'foreignKey' => 'user_id', 'dependent' => false, 'conditions' => '', 'fields' => '', 'order' => '', 'limit' => '', 'offset' => '', 'exclusive' => '', 'finderQuery' => '', 'counterQuery' => '' ), attendance: var $belongsTo = array( 'User' => array( 'className' => 'User', 'foreignKey' => 'user_id', 'conditions' => '', 'fields' => '', 'order' => '' ), Robert On Wed, Aug 29, 2012 at 12:37 PM, Mancho wrote: > Are you sure the field exists in the database? > > -- > Murgan, Alexis Germán > Desarrollador & Diseñador > Web: german.murgan.com.ar > Email: ger...@murgan.com.ar > MSN: ger...@murgan.com.ar > Móvil: +5493424663813 > > -- > 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+unsubscr...@googlegroups.com. > Visit this group at http://groups.google.com/group/cake-php?hl=en-US. > > > -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Are you sure the field exists in the database? -- Murgan, Alexis Germán Desarrollador & Diseñador Web: german.murgan.com.ar Email: ger...@murgan.com.ar MSN: ger...@murgan.com.ar Móvil: +5493424663813 -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
That did not work either. -- View this message in context: http://cakephp.1045679.n5.nabble.com/complex-find-tp5710758p5710795.html Sent from the CakePHP mailing list archive at Nabble.com. -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: complex find()
Looks ok for me, i think if you firing find on Attendance
($this->Attendance->find) You dont need 'Attendance.created', just
'created'. You can try with that.
2012/8/29 rockbust
> If anyone can point me in the right direction.. It will be much appreciated
>
> I am trying to return a list of User where Attendance.created is between a
> date specified in my form.
> Further Attendance.created needs to be equal or greater than
> attendance_count input from the form.
> Attendance belongsTo User
>
> I have tried this but get sql error Unknown column 'Attendance.created'
>
> function admin_attendance_by_date_range() {
> $this->Attendance->recursive = 0;
> $session_school_id =
> $this->Session->read('Userinfo.currentSchoolid');
>
> $from =
> $this->data['Attendance']['start_date']['year'] . "-" .
> $this->data['Attendance']['start_date']['month'] . "-" .
> $this->data['Attendance']['start_date']['day'];
> $to =
> $this->data['Attendance']['end_date']['year'] . "-" .
> $this->data['Attendance']['end_date']['month'] . "-" .
> $this->data['Attendance']['end_date']['day'];
> $cond = array('Attendance.created BETWEEN ? AND ?'
> => array($from, $to),
> 'Attendance.school_id' => $session_school_id);
>
> Robert
>
>
>
>
> --
> View this message in context:
> http://cakephp.1045679.n5.nabble.com/complex-find-tp5710758.html
> Sent from the CakePHP mailing list archive at Nabble.com.
>
> --
> 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+unsubscr...@googlegroups.com.
> Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
>
>
>
--
*Ivan Rimac***
mail: ivn...@gmail.com
*tel: +385 95 555 99 66*
*http://ivanrimac.com*
--
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
complex find()
If anyone can point me in the right direction.. It will be much appreciated
I am trying to return a list of User where Attendance.created is between a
date specified in my form.
Further Attendance.created needs to be equal or greater than
attendance_count input from the form.
Attendance belongsTo User
I have tried this but get sql error Unknown column 'Attendance.created'
function admin_attendance_by_date_range() {
$this->Attendance->recursive = 0;
$session_school_id =
$this->Session->read('Userinfo.currentSchoolid');
$from =
$this->data['Attendance']['start_date']['year'] . "-" .
$this->data['Attendance']['start_date']['month'] . "-" .
$this->data['Attendance']['start_date']['day'];
$to = $this->data['Attendance']['end_date']['year'] .
"-" .
$this->data['Attendance']['end_date']['month'] . "-" .
$this->data['Attendance']['end_date']['day'];
$cond = array('Attendance.created BETWEEN ? AND ?' =>
array($from, $to),
'Attendance.school_id' => $session_school_id);
Robert
--
View this message in context:
http://cakephp.1045679.n5.nabble.com/complex-find-tp5710758.html
Sent from the CakePHP mailing list archive at Nabble.com.
--
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
Re: The new Hash class and complex find statements.
So it isn't possible? -- Dott. Stefano Zoffoli *Web Development & Internet Technologies * *Librasoft Snc *http://www.librasoftsnc.it Via della Luna, 13 47034 Forlimpopoli (FC) Tel. +39 0543 424612 Fax +39 0543 424612 2012/7/6 ankit patel > Stefano Zoffoli I think you are right > > -- > 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
Re: The new Hash class and complex find statements.
Stefano Zoffoli I think you are right -- 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: The new Hash class and complex find statements.
Nope,
This gives me only "Content" values, i also want the associated "User"
array.
( in my example i inverted the arguments of Hash::extract(), but that is
not the problem )
--
Dott. Stefano Zoffoli
*Web Development & Internet Technologies
*
*Librasoft Snc
*http://www.librasoftsnc.it
Via della Luna, 13
47034 Forlimpopoli (FC)
Tel. +39 0543 424612
Fax +39 0543 424612
2012/7/6 Steve Found
> Hash::extract('{n}.Content[type=/Article/]', $items);
>
> I think :)
>
>
>
> On 06/07/12 10:19, Stefano Zoffoli wrote:
>
> Hi,
>
> i'll start with an example. This is my array:
>
> $items = array(
> 0 => array(
> 'Content' => array(
> 'id' => '1',
> ...
> 'type' => 'Article',
> ...
> ),
> 'User' => array(
> ...
> ),
> ),
> 1 => array(
> 'Content' => array(
> 'id' => '2',
> ...
> 'type' => 'Page',
> ...
> ),
> 'User' => array(
> ...
> ),
> ),
> 2 => array(
> 'Content' => array(
> 'id' => '3',
> ...
> 'type' => 'Article',
> ...
> ),
> 'User' => array(
> ...
> ),
> ),
> );
>
> With the old Set class i could use xpath syntax to filter by content
> type like this:
>
> $results = Set::extract('/Content[type=Article]/..', $children);
>
> And it gave me this array:
>
> $results = array(
> 0 => array(
> 'Content' => array(
> 'id' => '1',
> ...
> 'type' => 'Article',
> ...
> ),
> 'User' => array(
> ...
> ),
> ),
> 1 => array(
> 'Content' => array(
> 'id' => '3',
> ...
> 'type' => 'Article',
> ...
> ),
> 'User' => array(
> ...
> ),
> ),
> );
>
> I tried to convert that function call with the new Hash class, but i
> failed.
> I tried:
>
> Hash::extract('{n}.Content[type=Article]', $items);
> Hash::extract('{n}.Content[type=Article]..', $items);
> Hash::extract('{n}[Content.type=Article]', $items);
> Hash::extract('{n}[type=Article]', $items);
> Hash::extract('{n}.[Content.type=Article]', $items);
> Hash::extract('{n}.[type=Article]', $items);
>
> and the same with quoted condition ([type="Article"]).
>
> How can I do to have the same results as before?
>
> --
> Dott. Stefano Zoffoli
> *Web Development & Internet Technologies
> *
> *Librasoft Snc
> *http://www.librasoftsnc.it
> Via della Luna, 13
> 47034 Forlimpopoli (FC)
> Tel. +39 0543 424612
> Fax +39 0543 424612
> --
> 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
>
--
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: The new Hash class and complex find statements.
Hash::extract('{n}.Content[type=/Article/]', $items);
I think :)
On 06/07/12 10:19, Stefano Zoffoli wrote:
Hi,
i'll start with an example. This is my array:
$items = array(
0=> array(
'Content' => array(
'id' => '1',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
1=> array(
'Content' => array(
'id' => '2',
...
'type' => 'Page',
...
),
'User' => array(
...
),
),
2=> array(
'Content' => array(
'id' => '3',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
);
With the old Set class i could use xpath syntax to filter by content
type like this:
$results = Set::extract('/Content[type=Article]/..', $children);
And it gave me this array:
$results = array(
0=> array(
'Content' => array(
'id' => '1',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
1=> array(
'Content' => array(
'id' => '3',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
);
I tried to convert that function call with the new Hash class, but i
failed.
I tried:
Hash::extract('{n}.Content[type=Article]', $items);
Hash::extract('{n}.Content[type=Article]..', $items);
Hash::extract('{n}[Content.type=Article]', $items);
Hash::extract('{n}[type=Article]', $items);
Hash::extract('{n}.[Content.type=Article]', $items);
Hash::extract('{n}.[type=Article]', $items);
and the same with quoted condition ([type="Article"]).
How can I do to have the same results as before?
--
Dott. Stefano Zoffoli
/Web Development & Internet Technologies
/
*Librasoft Snc
*http://www.librasoftsnc.it
Via della Luna, 13
47034 Forlimpopoli (FC)
Tel. +39 0543 424612
Fax +39 0543 424612
--
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
The new Hash class and complex find statements.
Hi,
i'll start with an example. This is my array:
$items = array(
0 => array(
'Content' => array(
'id' => '1',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
1 => array(
'Content' => array(
'id' => '2',
...
'type' => 'Page',
...
),
'User' => array(
...
),
),
2 => array(
'Content' => array(
'id' => '3',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
);
With the old Set class i could use xpath syntax to filter by content type
like this:
$results = Set::extract('/Content[type=Article]/..', $children);
And it gave me this array:
$results = array(
0 => array(
'Content' => array(
'id' => '1',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
1 => array(
'Content' => array(
'id' => '3',
...
'type' => 'Article',
...
),
'User' => array(
...
),
),
);
I tried to convert that function call with the new Hash class, but i failed.
I tried:
Hash::extract('{n}.Content[type=Article]', $items);
Hash::extract('{n}.Content[type=Article]..', $items);
Hash::extract('{n}[Content.type=Article]', $items);
Hash::extract('{n}[type=Article]', $items);
Hash::extract('{n}.[Content.type=Article]', $items);
Hash::extract('{n}.[type=Article]', $items);
and the same with quoted condition ([type="Article"]).
How can I do to have the same results as before?
--
Dott. Stefano Zoffoli
*Web Development & Internet Technologies
*
*Librasoft Snc
*http://www.librasoftsnc.it
Via della Luna, 13
47034 Forlimpopoli (FC)
Tel. +39 0543 424612
Fax +39 0543 424612
--
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: Complex find
Hi: I'm taking a liberty with your Models, because I don't know how
Post can belongTo Post, so I'm making Post belongTo Topic, and I'm
thinking of something along the lines of this:
'fields' => array(
'TopicCategory.title',
'COUNT(DISTINCT Topic.id) As topic_total',
'COUNT(DISTINCT Post.id) As post_total'
),
'joins' => array( array( 'type' => 'INNER',
'table' => 'topics',
'alias' => 'Topic',
'conditions' => array(
'Topic.id =
TopicCategory.topic_id'
)
),
array( 'type' => 'LEFT',
'table' => 'posts',
'alias' => 'Post',
'conditions' => array(
'Post.topic_id =
Topic.id'
)
)
)
'group' => array('TopicCategory.id')
- Rob
On May 11, 10:39 am, "Carlos Eduardo Sotelo Pinto"
wrote:
> Hi people
> I need a help on a find query like this
>
> Models
> ===
> TopicCategory => ( habtm=>Topic )
> Topic => (habtm => TopicCategory, hasMany=>Post)
> Post => (belongsToPost)
>
> I need something like
>
>
> TopicCategory.title
> Topic total
> Post total
>
>
> HOw could be the find query for this result, that as you can see it is a
> forum
>
> thanks
>
> --
> Carlos Eduardo Sotelo Pinto
> PHP Senior Web Developer
> Cell (preferred): (Mov)+51, 959980794 :: (Claro)+51, 952707662
> http://www.csotelo.org
> Skype: csotelop
> Yahoo: csotelop
> MSN: carlos.sotelo.pi...@gmail.com
> GTalk: carlos.sotelo.pi...@gmail.com
> GPG FP:697E FAB8 8E83 1D60 BBFB 2264 9E3D 5761 F855 4F6B
> GNULinux RU #379182 || GNULinux RM #277661
--
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
Complex find
Hi people I need a help on a find query like this Models === TopicCategory => ( habtm=>Topic ) Topic => (habtm => TopicCategory, hasMany=>Post) Post => (belongsToPost) I need something like TopicCategory.title Topic total Post total HOw could be the find query for this result, that as you can see it is a forum thanks -- Carlos Eduardo Sotelo Pinto PHP Senior Web Developer Cell (preferred): (Mov)+51, 959980794 :: (Claro)+51, 952707662 http://www.csotelo.org Skype: csotelop Yahoo: csotelop MSN: carlos.sotelo.pi...@gmail.com GTalk: carlos.sotelo.pi...@gmail.com GPG FP:697E FAB8 8E83 1D60 BBFB 2264 9E3D 5761 F855 4F6B GNULinux RU #379182 || GNULinux RM #277661 -- 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: Need some help with a rather complex find() request
Take a look at the containable behaviour. If it doesn't do it for you out of the box, it'll surely get you close. Jeremy Burns Class Outfit jeremybu...@classoutfit.com http://www.classoutfit.com On 17 Sep 2010, at 15:55, Michael Gaiser wrote: > So, here is the basics of my setup. > > There is a table of Locations. > Each Location has a type. (0=global, 1=national, 2=regional, 3=city, > 4=landmark) > Each Location has a parent_id allowing for you to chain locations. > The chain uses the type field to determine what is appropriate. > A global location will not have any parents. > A regional location can only have a national location as its parent. > A city location cannot have a national location as a parent. > A landmark cannot be a parent, but can be a child of any type. > It is possible to have a national landmark, yet not one in a city thus the > reason this can exist under all locations. > Example Structure: > World (global) > United States (national) > California (regional) > San Francisco (city) > Golden Gate Bridge (landmark) > Colorado (regional) > Mesa Verde (landmark) > Denver (city) > Mile High Stadium (landmark) > United Kingdom (national) > England (regional) > London (city) > Westminster Abby (landmark) > Stonehenge (landmark) > Scotland (regional) > There is a table of Domains. > A Domain is a collection of other domains and locations. > Each Domain shares the same structure and layout of the Locations with a few > slight differences.. > Domains have a type, but lack the landmark type. > A Domain has a parent_id like the Locations and can be chained like them. > A Domain also has locations assigned to it through a join table. > The join record not only contains the domain_id, and the location_id, but > also mode. > Mode is a boolean. > 1 = Add the Location (and all its children) to the Domain > 0 = Subtract the Location (and all its children) from the Domain > > Now for the find query. > I want to start with the id of a domain. > Grab all the children and the children of those children etc of that domain. > Grab the locations that belong to those domains > Grab all the children and the children of the children etc. of that location. > Return an array of all the location ids & names, in their parent hierarchy > > > I know that I could write something that would do many many searches, but I > would really like it if the database would do all the heavy lifting. Thanks. > > > ~Michael > > > > > Check out the new CakePHP Questions site http://cakeqs.org and help others > with their CakePHP related questions. > > 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+unsubscr...@googlegroups.com For more options, visit this group at > http://groups.google.com/group/cake-php?hl=en Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Need some help with a rather complex find() request
So, here is the basics of my setup. - There is a table of Locations. - Each Location has a type. (0=global, 1=national, 2=regional, 3=city, 4=landmark) - Each Location has a parent_id allowing for you to chain locations. - The chain uses the type field to determine what is appropriate. - A global location will not have any parents. - A regional location can only have a national location as its parent. - A city location cannot have a national location as a parent. - A landmark cannot be a parent, but can be a child of any type. - It is possible to have a national landmark, yet not one in a city thus the reason this can exist under all locations. - Example Structure: - World (global) - United States (national) - California (regional) - San Francisco (city) - Golden Gate Bridge (landmark) - Colorado (regional) - Mesa Verde (landmark) - Denver (city) - Mile High Stadium (landmark) - United Kingdom (national) - England (regional) - London (city) - Westminster Abby (landmark) - Stonehenge (landmark) - Scotland (regional) - There is a table of Domains. - A Domain is a collection of other domains and locations. - Each Domain shares the same structure and layout of the Locations with a few slight differences.. - Domains have a type, but lack the landmark type. - A Domain has a parent_id like the Locations and can be chained like them. - A Domain also has locations assigned to it through a join table. - The join record not only contains the domain_id, and the location_id, but also mode. - Mode is a boolean. - 1 = Add the Location (and all its children) to the Domain - 0 = Subtract the Location (and all its children) from the Domain Now for the find query. - I want to start with the id of a domain. - Grab all the children and the children of those children etc of that domain. - Grab the locations that belong to those domains - Grab all the children and the children of the children etc. of that location. - Return an array of all the location ids & names, in their parent hierarchy I know that I could write something that would do many many searches, but I would really like it if the database would do all the heavy lifting. Thanks. ~Michael Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: Complex find
Thanks for the start. It works great!
On Aug 27, 11:49 pm, "eugenioc...@hotmail.com"
wrote:
> Have you try making joins intead of subselects? the subselects are not
> recommended because they usually are very slow. Joins are really
> faster.
>
> Try with something like this;
>
> $options = array(
> 'recursive'=>1,
> 'conditions'=>array(
> 'Encounter.encounterStartTimeStamp >= 2010-08-26'
> ),'joins'=>array(
> array(
> 'type'=>'INNER',
> 'table' => 'entryReasons',
> 'alias' => 'entryReason',
> 'conditions'=>array(
> 'Encounter.entryReason_id=entryReason.id AND
> `entryReason`.`ReasonName` LIKE "SUB%"'
> )
> )
> )
> );
>
> $this->Model->find('all',$options);
>
> Cheers!, Eugenio Fage
>
> On 27 ago, 21:59, milimber wrote:
>
>
>
> > I need to translate this query into afind() statement
>
> > select id, `encounterStartTimeStamp`, `entryReason_id` from
> > `encounters` where encounterStartTimeStamp >= '2010-08-26' and
> > `encounters`.`entryReason_id` in (SELECT `entryReasons`.`ID` FROM
> > `entryReasons` WHERE `ReasonName` LIKE "SUB%")
>
> > I have looked over the docs for thefindfunction and i see the
> > example of using an array to run the SELECT IN: array( "Post.title"
> > => array("First post", "Second post", "Third post")) But since my
> > SELECT IN is a query its self im not sure on how to proceed. I have
> > tried:
>
> > $conditions = array('Encounter.encounterStartTimeStamp >=' =>
> > '2010-08-26',
> > 'Encounter.entryReason_id' => 'SELECT
> > `entryReasons`.`ID` FROM `entryReasons` WHERE `ReasonName` LIKE "SUB
> > %"');
>
> > Im kinda lost here.. any help would be appreciated
>
> > thanks
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Re: Complex find
Have you try making joins intead of subselects? the subselects are not
recommended because they usually are very slow. Joins are really
faster.
Try with something like this;
$options = array(
'recursive'=>1,
'conditions'=>array(
'Encounter.encounterStartTimeStamp >= 2010-08-26'
),'joins'=>array(
array(
'type'=>'INNER',
'table' => 'entryReasons',
'alias' => 'entryReason',
'conditions'=>array(
'Encounter.entryReason_id=entryReason.id AND
`entryReason`.`ReasonName` LIKE "SUB%"'
)
)
)
);
$this->Model->find('all',$options);
Cheers!, Eugenio Fage
On 27 ago, 21:59, milimber wrote:
> I need to translate this query into a find() statement
>
> select id, `encounterStartTimeStamp`, `entryReason_id` from
> `encounters` where encounterStartTimeStamp >= '2010-08-26' and
> `encounters`.`entryReason_id` in (SELECT `entryReasons`.`ID` FROM
> `entryReasons` WHERE `ReasonName` LIKE "SUB%")
>
> I have looked over the docs for the find function and i see the
> example of using an array to run the SELECT IN: array( "Post.title"
> => array("First post", "Second post", "Third post")) But since my
> SELECT IN is a query its self im not sure on how to proceed. I have
> tried:
>
> $conditions = array('Encounter.encounterStartTimeStamp >=' =>
> '2010-08-26',
> 'Encounter.entryReason_id' => 'SELECT
> `entryReasons`.`ID` FROM `entryReasons` WHERE `ReasonName` LIKE "SUB
> %"');
>
> Im kinda lost here.. any help would be appreciated
>
> thanks
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Complex find
I need to translate this query into a find() statement
select id, `encounterStartTimeStamp`, `entryReason_id` from
`encounters` where encounterStartTimeStamp >= '2010-08-26' and
`encounters`.`entryReason_id` in (SELECT `entryReasons`.`ID` FROM
`entryReasons` WHERE `ReasonName` LIKE "SUB%")
I have looked over the docs for the find function and i see the
example of using an array to run the SELECT IN: array("Post.title"
=> array("First post", "Second post", "Third post")) But since my
SELECT IN is a query its self im not sure on how to proceed. I have
tried:
$conditions = array('Encounter.encounterStartTimeStamp >=' =>
'2010-08-26',
'Encounter.entryReason_id' => 'SELECT
`entryReasons`.`ID` FROM `entryReasons` WHERE `ReasonName` LIKE "SUB
%"');
Im kinda lost here.. any help would be appreciated
thanks
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Re: Complex find neighbors query
On Aug 17, 4:07 am, Josh K wrote: > It seems like my problem is that find neighbors is ignoring 'value' => > $id. The $id is set to 8997, but it's returning 11 and 14 and the prev > and next options in the array. Your conditions don't look very conditiony either Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: Complex find neighbors query
It seems like my problem is that find neighbors is ignoring 'value' => $id. The $id is set to 8997, but it's returning 11 and 14 and the prev and next options in the array. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Complex find neighbors query
I have a query that outputs a paginated list of records based on a
staff member's region (comprised of states):
---
$this->paginate =
array('fields'=>array('Application.id'),'conditions'=>'Application.statuslist_id
= Statuslist.id AND Application.state IN
(SELECT S.shortname FROM states S
INNER JOIN regions_states RS ON RS.state_id = S.id
INNER JOIN regions R ON R.id = RS.region_id
AND R.region IN ( SELECT R.region from staffs_regions SR, regions R
where R.id = SR.region_id AND SR.staff_id='.$this->Session-
>read("id").')) ' . $otherstatecondition . ' OR Application.staff_id =
'.$this->Session->read("id"),'order'=>'Application.id desc','limit' =>
$limit, 'page' => 1,'recursive'=>'0','order'=>'Application.id desc');
$this->set('viewallinfo', $this->paginate('Application'));
---
I need to use this query on each individual record to display a next
and previous button, but I'm not sure I have the structure correct for
find neighbors (http://book.cakephp.org/view/811/find-neighbors).
---
$neighbors = $this->Application->find('neighbors',
array('fields'=>'Application.id','conditions'=>'Application.statuslist_id
= Statuslist.id AND Application.state IN
(SELECT S.shortname FROM states S
INNER JOIN regions_states RS ON RS.state_id = S.id
INNER JOIN regions R ON R.id = RS.region_id
AND R.region IN ( SELECT R.region from staffs_regions SR, regions R
where R.id = SR.region_id AND SR.staff_id='.$this->Session-
>read("id").')) ' . $otherstatecondition . ' OR Application.staff_id =
'.$this->Session->read("id"),'value' => $id));
$this->set('neighbors', $neighbors);
---
What should I do or what params are incorrect? The $this->paginate
query works as expected. I'm just having trouble getting it to work
with find neighbors. Thanks for the help.
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Re: Complex find() using Oracle
Do I post those here? If so, here goes... I only have time for a quick test
case
/**
* test that fields() will accept objects made from DboSource::expression
*
* @return void
*/
function testFieldsWithExpression() {
$expression =&
$this->testDb->expression("SUM(DECODE(SUBSTR(Sample.id,1), '1', 1, 0)) AS
\"Sample.case_col\"");
$result = $this->testDb->fields($this->Model, null, array("id",
$expression));
$expected = array(
'`TestModel`.`id`',
"SUM(DECODE(SUBSTR(Sample.id,1), '1', 1, 0)) AS
\"Sample.case_col\""
);
$this->assertEqual($result, $expected);
}
One could argue that I shouldn't be using DECODE anyways as it is Oracle SQL
specific. If this app were to change databases, I would have to rewrite a
lot of custom fields that don't have cross-platform SQL. Is that a valid
argument IYO?
On Thu, Feb 18, 2010 at 1:51 PM, Martin Radosta wrote:
> I would only change:
>
> $this->find('all', array('fields' => $fields,...etc, etc.)
>
> Would be great if you post the test case (or even better, a patch) to avoid
> other people waste time with the same issue.
>
> Bye
>
> MARTIN
>
>
>
>
> On 02/18/2010 05:39 PM, Tony Fugere wrote:
>
> It does not fix the problem. Am I doing it correctly?
>
> $dbo = $this->getDataSource();
> $expression =
> $dbo->expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
> \'service1\',
> ServiceData.stat_value,0)) AS "ServiceData.service1"');
> $fields = array('ServiceData.stat_date',
> $expression);
> $this->find('all', array($fields,...etc, etc.)
>
> Still results in the ugly data.
>
> HOWEVER, I did notice the use of CASE WHEN statements in the test case. So,
> I switched my DECODE to CASE WHEN and it works as expected. I will draw up a
> test case and report the issue of DECODE failing to function properly.
>
> Thanks for your help Martin!
>
> -Tony
>
> On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta
> wrote:
>
>> Check the text case in this commit for an example:
>>
>>
>> http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9
>>
>> let me know if that fix the problem, if not, you can prepare a test case
>> and report the issue.
>>
>>
>>
>>
>> On 02/18/2010 03:21 PM, TonyFugere wrote:
>>
>>> I don't follow what you mean by "Try using expression for fields." Can
>>> you clarify or give me an example?
>>>
>>> On Feb 18, 11:12 am, Martin Radosta wrote:
>>>
>>>
CakePhp is not correctly parsing the alias for the decode fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should also fix the data
missmatch.
Regards
MARTIN
On 02/18/2010 02:38 PM, TonyFugere wrote:
> I've search this group and Google for a solution without any luck.
>
>
> I can read (SELECT) data, but cannot change this database at all. To
> get the data I run the following find:
>
>
> $this->find('all',
>array(
> 'fields' =>array(
>'ServiceData.stat_date',
>'SUBSTR(ServiceData.stat_parameter,1,4) AS
> "ServiceData.schema"',
>'SUBSTR(ServiceData.stat_parameter,19,2) AS
> "ServiceData.state"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
> ServiceData.stat_value,0)) AS "ServiceData.service1"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
> ServiceData.stat_value,0)) AS "ServiceData.service2"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
> ServiceData.stat_value,0)) AS "ServiceData.service3"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
> ServiceData.stat_value,0)) AS "ServiceData.service4"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
> ServiceData.stat_value,0)) AS "ServiceData.service5"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
> ServiceData.stat_value,0)) AS "ServiceData.service6"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
> ServiceData.stat_value,0)) AS "ServiceData.service7"',
>'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
> ServiceData.stat_value,0)) AS "ServiceData.service8"'
> ),
> 'conditions' =>array(
>'SUBSTR(ServiceData.stat_parameter, 5, 13)' =>
> 'customer_lookup',
>'SUBSTR(ServiceData.stat_parameter, 1, 4)' =>$active_schema,
>'ServiceData.stat_server LIKE' =>'PRODUCTION%',
>'ServiceData.stat#' =>999,
>'ServiceData.stat_date' =>$load_date,
> ),
> 'group' =>array(
>'ServiceData.stat_date',
>'SUBSTR(ServiceData.stat_parameter,1,4)',
>'SUBSTR(ServiceData.stat_parameter,19,2)'
> ),
>
Re: Complex find() using Oracle
I would only change:
$this->find('all', array('fields' => $fields,...etc, etc.)
Would be great if you post the test case (or even better, a patch) to
avoid other people waste time with the same issue.
Bye
MARTIN
On 02/18/2010 05:39 PM, Tony Fugere wrote:
It does not fix the problem. Am I doing it correctly?
$dbo = $this->getDataSource();
$expression =
$dbo->expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service1\',
ServiceData.stat_value,0)) AS "ServiceData.service1"');
$fields = array('ServiceData.stat_date',
$expression);
$this->find('all', array($fields,...etc, etc.)
Still results in the ugly data.
HOWEVER, I did notice the use of CASE WHEN statements in the test
case. So, I switched my DECODE to CASE WHEN and it works as expected.
I will draw up a test case and report the issue of DECODE failing to
function properly.
Thanks for your help Martin!
-Tony
On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta
mailto:martinrado...@gmail.com>> wrote:
Check the text case in this commit for an example:
http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9
let me know if that fix the problem, if not, you can prepare a
test case and report the issue.
On 02/18/2010 03:21 PM, TonyFugere wrote:
I don't follow what you mean by "Try using expression for
fields." Can
you clarify or give me an example?
On Feb 18, 11:12 am, Martin Radostamailto:martinrado...@gmail.com>> wrote:
CakePhp is not correctly parsing the alias for the decode
fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should
also fix the data
missmatch.
Regards
MARTIN
On 02/18/2010 02:38 PM, TonyFugere wrote:
I've search this group and Google for a solution
without any luck.
I can read (SELECT) data, but cannot change this
database at all. To
get the data I run the following find:
$this->find('all',
array(
'fields' =>array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
"ServiceData.schema"',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
"ServiceData.state"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service1\',
ServiceData.stat_value,0)) AS "ServiceData.service1"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service2\',
ServiceData.stat_value,0)) AS "ServiceData.service2"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service3\',
ServiceData.stat_value,0)) AS "ServiceData.service3"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service4\',
ServiceData.stat_value,0)) AS "ServiceData.service4"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service5\',
ServiceData.stat_value,0)) AS "ServiceData.service5"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service6\',
ServiceData.stat_value,0)) AS "ServiceData.service6"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service7\',
ServiceData.stat_value,0)) AS "ServiceData.service7"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service8\',
ServiceData.stat_value,0)) AS "ServiceData.service8"'
),
'conditions' =>array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =>
'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =>
$active_schema,
'ServiceData.stat_server LIKE' =>'PRODUCTION%',
'ServiceData.stat#' =>999,
'ServiceData.stat_date' =>$load_date,
),
'group' =>array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
),
'order' =>
array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
);
This is the resulting query:
Re: Complex find() using Oracle
It does not fix the problem. Am I doing it correctly?
$dbo = $this->getDataSource();
$expression =
$dbo->expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service1\',
ServiceData.stat_value,0)) AS "ServiceData.service1"');
$fields = array('ServiceData.stat_date',
$expression);
$this->find('all', array($fields,...etc, etc.)
Still results in the ugly data.
HOWEVER, I did notice the use of CASE WHEN statements in the test case. So,
I switched my DECODE to CASE WHEN and it works as expected. I will draw up a
test case and report the issue of DECODE failing to function properly.
Thanks for your help Martin!
-Tony
On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta wrote:
> Check the text case in this commit for an example:
>
>
> http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9
>
> let me know if that fix the problem, if not, you can prepare a test case
> and report the issue.
>
>
>
>
> On 02/18/2010 03:21 PM, TonyFugere wrote:
>
>> I don't follow what you mean by "Try using expression for fields." Can
>> you clarify or give me an example?
>>
>> On Feb 18, 11:12 am, Martin Radosta wrote:
>>
>>
>>> CakePhp is not correctly parsing the alias for the decode fields, thats
>>> why you see decode... on array keys.
>>> Try using expression for fields. If that works, should also fix the data
>>> missmatch.
>>>
>>> Regards
>>>
>>> MARTIN
>>>
>>> On 02/18/2010 02:38 PM, TonyFugere wrote:
>>>
>>>
>>>
I've search this group and Google for a solution without any luck.
>>>
>>>
I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:
>>>
>>>
$this->find('all',
array(
'fields' =>array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
"ServiceData.schema"',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
"ServiceData.state"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS "ServiceData.service1"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS "ServiceData.service2"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS "ServiceData.service3"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS "ServiceData.service4"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS "ServiceData.service5"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS "ServiceData.service6"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS "ServiceData.service7"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS "ServiceData.service8"'
),
'conditions' =>array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =>
'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =>$active_schema,
'ServiceData.stat_server LIKE' =>'PRODUCTION%',
'ServiceData.stat#' =>999,
'ServiceData.stat_date' =>$load_date,
),
'group' =>array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
),
'order' =>array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
);
>>>
>>>
This is the resulting query:
>>>
>>>
SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS "ServiceData.schema"
, SUBSTR(ServiceData.stat_parameter,19,2) AS "ServiceData.state"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS "ServiceData.service1"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS "ServiceData.service2"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS "ServiceData.service3"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS "ServiceData.service4"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS "ServiceData.service5"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS "ServiceData.service6"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS "ServiceData.service7"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_v
Re: Complex find() using Oracle
Check the text case in this commit for an example:
http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9
let me know if that fix the problem, if not, you can prepare a test case
and report the issue.
On 02/18/2010 03:21 PM, TonyFugere wrote:
I don't follow what you mean by "Try using expression for fields." Can
you clarify or give me an example?
On Feb 18, 11:12 am, Martin Radosta wrote:
CakePhp is not correctly parsing the alias for the decode fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should also fix the data
missmatch.
Regards
MARTIN
On 02/18/2010 02:38 PM, TonyFugere wrote:
I've search this group and Google for a solution without any luck.
I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:
$this->find('all',
array(
'fields' =>array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
"ServiceData.schema"',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
"ServiceData.state"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS "ServiceData.service1"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS "ServiceData.service2"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS "ServiceData.service3"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS "ServiceData.service4"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS "ServiceData.service5"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS "ServiceData.service6"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS "ServiceData.service7"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS "ServiceData.service8"'
),
'conditions' =>array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =>
'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =>$active_schema,
'ServiceData.stat_server LIKE' =>'PRODUCTION%',
'ServiceData.stat#' =>999,
'ServiceData.stat_date' =>$load_date,
),
'group' =>array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
),
'order' =>array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
);
This is the resulting query:
SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS "ServiceData.schema"
, SUBSTR(ServiceData.stat_parameter,19,2) AS "ServiceData.state"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS "ServiceData.service1"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS "ServiceData.service2"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS "ServiceData.service3"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS "ServiceData.service4"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS "ServiceData.service5"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS "ServiceData.service6"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS "ServiceData.service7"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS "ServiceData.service8"
FROM
SERVICE_DATA ServiceData
WHERE
SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
AND ServiceData.stat_server LIKE 'PRODUCTION%'
AND ServiceData.stat_number = 999
AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4)
, SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
SUBSTR(ServiceData.stat_parameter,19,2) ASC
This results in data returning in this form:
array(43) {
[0]=>
array(3) {
["ServiceData"]=>
array(4) {
["stat_date"]=>
string(19) "2010-02-17 09:00:02"
["schema"]=>
string(4) "SCHEMA_A"
["state"]=>
string(2) "()"
["statvalue"]=>
string(3) "116"
}
["SUM(DECODE(SUBSTR(ServiceData"]=>
array(1) {
["stat_parameter,22)"]=>
string(3) "107"
}
[0]=>
array
Re: Complex find() using Oracle
I don't follow what you mean by "Try using expression for fields." Can
you clarify or give me an example?
On Feb 18, 11:12 am, Martin Radosta wrote:
> CakePhp is not correctly parsing the alias for the decode fields, thats
> why you see decode... on array keys.
> Try using expression for fields. If that works, should also fix the data
> missmatch.
>
> Regards
>
> MARTIN
>
> On 02/18/2010 02:38 PM, TonyFugere wrote:
>
> > I've search this group and Google for a solution without any luck.
>
> > I can read (SELECT) data, but cannot change this database at all. To
> > get the data I run the following find:
>
> > $this->find('all',
> > array(
> > 'fields' => array(
> > 'ServiceData.stat_date',
> > 'SUBSTR(ServiceData.stat_parameter,1,4) AS
> > "ServiceData.schema"',
> > 'SUBSTR(ServiceData.stat_parameter,19,2) AS
> > "ServiceData.state"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
> > ServiceData.stat_value,0)) AS "ServiceData.service1"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
> > ServiceData.stat_value,0)) AS "ServiceData.service2"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
> > ServiceData.stat_value,0)) AS "ServiceData.service3"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
> > ServiceData.stat_value,0)) AS "ServiceData.service4"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
> > ServiceData.stat_value,0)) AS "ServiceData.service5"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
> > ServiceData.stat_value,0)) AS "ServiceData.service6"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
> > ServiceData.stat_value,0)) AS "ServiceData.service7"',
> > 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
> > ServiceData.stat_value,0)) AS "ServiceData.service8"'
> > ),
> > 'conditions' => array(
> > 'SUBSTR(ServiceData.stat_parameter, 5, 13)' =>
> > 'customer_lookup',
> > 'SUBSTR(ServiceData.stat_parameter, 1, 4)' => $active_schema,
> > 'ServiceData.stat_server LIKE' => 'PRODUCTION%',
> > 'ServiceData.stat#' => 999,
> > 'ServiceData.stat_date' => $load_date,
> > ),
> > 'group' => array(
> > 'ServiceData.stat_date',
> > 'SUBSTR(ServiceData.stat_parameter,1,4)',
> > 'SUBSTR(ServiceData.stat_parameter,19,2)'
> > ),
> > 'order' => array('SUBSTR(ServiceData.stat_parameter,19,2)')
> > )
> > );
>
> > This is the resulting query:
>
> > SELECT
> > ServiceData.stat_date
> > , SUBSTR(ServiceData.stat_parameter,1,4) AS "ServiceData.schema"
> > , SUBSTR(ServiceData.stat_parameter,19,2) AS "ServiceData.state"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
> > ServiceData.stat_value,0)) AS "ServiceData.service1"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
> > ServiceData.stat_value,0)) AS "ServiceData.service2"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
> > ServiceData.stat_value,0)) AS "ServiceData.service3"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
> > ServiceData.stat_value,0)) AS "ServiceData.service4"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
> > ServiceData.stat_value,0)) AS "ServiceData.service5"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
> > ServiceData.stat_value,0)) AS "ServiceData.service6"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
> > ServiceData.stat_value,0)) AS "ServiceData.service7"
> > , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
> > ServiceData.stat_value,0)) AS "ServiceData.service8"
> > FROM
> > SERVICE_DATA ServiceData
> > WHERE
> > SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
> > AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
> > AND ServiceData.stat_server LIKE 'PRODUCTION%'
> > AND ServiceData.stat_number = 999
> > AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
> > DD HH24:MI:SS')
> > GROUP BY
> > ServiceData.stat_date
> > , SUBSTR(ServiceData.stat_parameter,1,4)
> > , SUBSTR(ServiceData.stat_parameter,19,2)
> > ORDER BY
> > SUBSTR(ServiceData.stat_parameter,19,2) ASC
>
> > This results in data returning in this form:
>
> > array(43) {
> > [0]=>
> > array(3) {
> > ["ServiceData"]=>
> > array(4) {
> > ["stat_date"]=>
> > string(19) "2010-02-17 09:00:02"
> > ["schema"]=>
> > string(4) "SCHEMA_A"
> > ["state"]=>
> > string(2) "()"
> > ["statvalue"]=>
> > string(3) "116"
> > }
> > ["SUM(DECODE(SUBSTR(ServiceData"]=>
> > array(1) {
> > ["stat_parameter,22)"]=>
> > string(3) "107"
> > }
> > [0]=>
> > array(3) {
> > ["'service1'"
Re: Complex find() using Oracle
CakePhp is not correctly parsing the alias for the decode fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should also fix the data
missmatch.
Regards
MARTIN
On 02/18/2010 02:38 PM, TonyFugere wrote:
I've search this group and Google for a solution without any luck.
I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:
$this->find('all',
array(
'fields' => array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
"ServiceData.schema"',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
"ServiceData.state"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS "ServiceData.service1"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS "ServiceData.service2"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS "ServiceData.service3"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS "ServiceData.service4"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS "ServiceData.service5"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS "ServiceData.service6"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS "ServiceData.service7"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS "ServiceData.service8"'
),
'conditions' => array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =>
'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' => $active_schema,
'ServiceData.stat_server LIKE' => 'PRODUCTION%',
'ServiceData.stat#' => 999,
'ServiceData.stat_date' => $load_date,
),
'group' => array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
),
'order' => array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
);
This is the resulting query:
SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS "ServiceData.schema"
, SUBSTR(ServiceData.stat_parameter,19,2) AS "ServiceData.state"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS "ServiceData.service1"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS "ServiceData.service2"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS "ServiceData.service3"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS "ServiceData.service4"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS "ServiceData.service5"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS "ServiceData.service6"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS "ServiceData.service7"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS "ServiceData.service8"
FROM
SERVICE_DATA ServiceData
WHERE
SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
AND ServiceData.stat_server LIKE 'PRODUCTION%'
AND ServiceData.stat_number = 999
AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4)
, SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
SUBSTR(ServiceData.stat_parameter,19,2) ASC
This results in data returning in this form:
array(43) {
[0]=>
array(3) {
["ServiceData"]=>
array(4) {
["stat_date"]=>
string(19) "2010-02-17 09:00:02"
["schema"]=>
string(4) "SCHEMA_A"
["state"]=>
string(2) "()"
["statvalue"]=>
string(3) "116"
}
["SUM(DECODE(SUBSTR(ServiceData"]=>
array(1) {
["stat_parameter,22)"]=>
string(3) "107"
}
[0]=>
array(3) {
["'service1'"]=>
string(2) "40"
["'service2'"]=>
string(3) "116"
["'service3'"]=>
string(2) "17"
}
}
}
Notice the odd array keys "SUM(DECODE(SUBSTR(ServiceData" and
"'service1'" (this is the decode value in single ticks). Also the
value to service1 is the value that belongs in service2, service2 has
the value for service5, and service3 has the value for service8???
If I remove the SUM(DECODE(SUBSTR( fields, then everything works as
expected:
array(43) {
[0]=>
array(1) {
["ServiceData"]=>
array(3) {
["stat_date"]=>
Complex find() using Oracle
I've search this group and Google for a solution without any luck.
I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:
$this->find('all',
array(
'fields' => array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
"ServiceData.schema"',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
"ServiceData.state"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS "ServiceData.service1"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS "ServiceData.service2"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS "ServiceData.service3"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS "ServiceData.service4"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS "ServiceData.service5"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS "ServiceData.service6"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS "ServiceData.service7"',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS "ServiceData.service8"'
),
'conditions' => array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =>
'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' => $active_schema,
'ServiceData.stat_server LIKE' => 'PRODUCTION%',
'ServiceData.stat#' => 999,
'ServiceData.stat_date' => $load_date,
),
'group' => array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
),
'order' => array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
);
This is the resulting query:
SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS "ServiceData.schema"
, SUBSTR(ServiceData.stat_parameter,19,2) AS "ServiceData.state"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS "ServiceData.service1"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS "ServiceData.service2"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS "ServiceData.service3"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS "ServiceData.service4"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS "ServiceData.service5"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS "ServiceData.service6"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS "ServiceData.service7"
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS "ServiceData.service8"
FROM
SERVICE_DATA ServiceData
WHERE
SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
AND ServiceData.stat_server LIKE 'PRODUCTION%'
AND ServiceData.stat_number = 999
AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4)
, SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
SUBSTR(ServiceData.stat_parameter,19,2) ASC
This results in data returning in this form:
array(43) {
[0]=>
array(3) {
["ServiceData"]=>
array(4) {
["stat_date"]=>
string(19) "2010-02-17 09:00:02"
["schema"]=>
string(4) "SCHEMA_A"
["state"]=>
string(2) "()"
["statvalue"]=>
string(3) "116"
}
["SUM(DECODE(SUBSTR(ServiceData"]=>
array(1) {
["stat_parameter,22)"]=>
string(3) "107"
}
[0]=>
array(3) {
["'service1'"]=>
string(2) "40"
["'service2'"]=>
string(3) "116"
["'service3'"]=>
string(2) "17"
}
}
}
Notice the odd array keys "SUM(DECODE(SUBSTR(ServiceData" and
"'service1'" (this is the decode value in single ticks). Also the
value to service1 is the value that belongs in service2, service2 has
the value for service5, and service3 has the value for service8???
If I remove the SUM(DECODE(SUBSTR( fields, then everything works as
expected:
array(43) {
[0]=>
array(1) {
["ServiceData"]=>
array(3) {
["stat_date"]=>
string(19) "2010-02-17 09:00:02"
["schema"]=>
string(4) "SCHEMA_A"
["state"]=>
string(2) "()"
}
}
}
I have tried the afterFind solution posted by TekNoid, but it did not
work.
http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-fields-in-cakephps-find/
Any hints or ideas on how I can overcome the issues
Re: Complex Find Conditions
What if you ask the question naming the Models and associations
between them, and what you were expecting to get back as the result of
the query. Finds and conditions depend greatly on the associations
between the models involded. Some use joins, most do not and all that.
SQL syntax also looks like a mess after google has messed with it.
Starting with a query and trying to duplicate it is usually more
complex than working from the Cake point of view... at least when
trying to build a find.
I am guesssing, but I think your main problem might be that Cake does
very few joins by default.
For example:
Post hasMany Comment - a find where you contain the comments to those
by a certain commenter will return ALL Posts and all will contain the
Comment key in the array. Contain will only make sure that some
Comment keys are empty and some have comments by that one commenter.
With a join you were expecting to only get the Post that actually have
Comments by that commenter. Not so with containable, sorry.
If you need to force Cake to do joins you can still paginate if you
build something like what Nate wrote about in january:
http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find
I took some ideas from that and made a joining feature that would join
models for me when needed.
http://bin.cakephp.org/saved/49446
I have linked to these before but I think they are still valid
techniques in some cases.
Martin
On Aug 18, 12:08 pm, gjofum wrote:
> I have no idea.
>
> I tried many things and nothing works.
> For exampple:
> $this->Item->find("all", array("fields" => array("Item.id",
> "Item.thumb"),
> "contain" => array(
> "ItemContent" => array(
> "conditions" => array("ItemContent.language_id" =>
> $this->language_id),
>
> "fields" => array( "ItemContent.name",
> "ItemContent.description", "ItemContent.url")))
> ));
>
> I'll have to use $this->Model->query() but then I won't be able to
> paginate it.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find Conditions
I have no idea.
I tried many things and nothing works.
For exampple:
$this->Item->find("all", array("fields" => array("Item.id",
"Item.thumb"),
"contain" => array(
"ItemContent" => array(
"conditions" => array("ItemContent.language_id" => $this-
>language_id),
"fields" => array( "ItemContent.name",
"ItemContent.description", "ItemContent.url")))
));
I'll have to use $this->Model->query() but then I won't be able to
paginate it.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Complex Find Conditions
I don't know how to make my own sql code into Complex Find Conditions.
Especially with use of INNER JOIN or LEFT INNER.
For example:
$items = $this->Tag->query("SELECT Item.id, Item.thumb,
ItemContent.name, ItemContent.description, ItemContent.url,
Module.code FROM items AS Item INNER JOIN items_content AS ItemContent
ON ItemContent.item_id = Item.id INNER JOIN modules AS Module ON
Module.id = Item.module_id INNER JOIN items_tags AS ItemTag ON
ItemTag.item_id = Item.id WHERE ItemContent.language_id = $this-
>language_id AND ItemTag.tag_id = $id AND Item.visible = 0 ORDER BY
Item.id DESC");
Please help.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: complex find condition, HELP
http://book.cakephp.org/view/74/Complex-Find-Conditions
On May 17, 7:53 pm, JamesF wrote:
> sounds like you need to user 'OR'.
>
> $this->Car->find('all', array('conditions'=>array('Model.type'=>array
> ('SUV', 'OR'=>array('Convertible' , 'Truck'));
>
> On May 17, 1:54 pm, brian wrote:
>
> > Have a look at the 'matches' example here:
>
> >http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-...
>
> > On Sun, May 17, 2009 at 11:18 AM, foldiman wrote:
>
> > > I'm stuck on constructing a complex find statement. I have a Car model
> > > and a Cartype model that are associated via HABTM. In other words, a
> > > Jeep (Car) can be an SUV (Cartype), a Truck (Cartype), and a
> > > Convertible (Cartype).
>
> > > I'm stuck on constructing the find query when searching for Cars by
> > > Cartype. If the relationship WAS Cartype HasMany Car (ie. a Car can
> > > only have one Cartype) it would be easy
>
> > > $search_criteria = array('SUV', 'Truck', 'Convertible');
>
> > > $params = array(
> > > 'conditions' = array('Car.cartype_id' => $search_criteria);
> > > )
>
> > > $this->Car->find('all', $params);
>
> > > BUT, the Car model can have many Cartypes...meaning the Car model does
> > > not have a `cartype_id` field.
>
> > > So how do construct a query that would mean, "give me all the Cars
> > > that are related to at least one Cartype contained in the search
> > > criteria"? Does this make sense?
>
> > > Thanks.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: complex find condition, HELP
sounds like you need to user 'OR'.
$this->Car->find('all', array('conditions'=>array('Model.type'=>array
('SUV', 'OR'=>array('Convertible' , 'Truck'));
On May 17, 1:54 pm, brian wrote:
> Have a look at the 'matches' example here:
>
> http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-...
>
> On Sun, May 17, 2009 at 11:18 AM, foldiman wrote:
>
> > I'm stuck on constructing a complex find statement. I have a Car model
> > and a Cartype model that are associated via HABTM. In other words, a
> > Jeep (Car) can be an SUV (Cartype), a Truck (Cartype), and a
> > Convertible (Cartype).
>
> > I'm stuck on constructing the find query when searching for Cars by
> > Cartype. If the relationship WAS Cartype HasMany Car (ie. a Car can
> > only have one Cartype) it would be easy
>
> > $search_criteria = array('SUV', 'Truck', 'Convertible');
>
> > $params = array(
> > 'conditions' = array('Car.cartype_id' => $search_criteria);
> > )
>
> > $this->Car->find('all', $params);
>
> > BUT, the Car model can have many Cartypes...meaning the Car model does
> > not have a `cartype_id` field.
>
> > So how do construct a query that would mean, "give me all the Cars
> > that are related to at least one Cartype contained in the search
> > criteria"? Does this make sense?
>
> > Thanks.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: complex find condition, HELP
Have a look at the 'matches' example here:
http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find
On Sun, May 17, 2009 at 11:18 AM, foldiman wrote:
>
> I'm stuck on constructing a complex find statement. I have a Car model
> and a Cartype model that are associated via HABTM. In other words, a
> Jeep (Car) can be an SUV (Cartype), a Truck (Cartype), and a
> Convertible (Cartype).
>
> I'm stuck on constructing the find query when searching for Cars by
> Cartype. If the relationship WAS Cartype HasMany Car (ie. a Car can
> only have one Cartype) it would be easy
>
> $search_criteria = array('SUV', 'Truck', 'Convertible');
>
> $params = array(
> 'conditions' = array('Car.cartype_id' => $search_criteria);
> )
>
> $this->Car->find('all', $params);
>
> BUT, the Car model can have many Cartypes...meaning the Car model does
> not have a `cartype_id` field.
>
> So how do construct a query that would mean, "give me all the Cars
> that are related to at least one Cartype contained in the search
> criteria"? Does this make sense?
>
> Thanks.
> >
>
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
complex find condition, HELP
I'm stuck on constructing a complex find statement. I have a Car model
and a Cartype model that are associated via HABTM. In other words, a
Jeep (Car) can be an SUV (Cartype), a Truck (Cartype), and a
Convertible (Cartype).
I'm stuck on constructing the find query when searching for Cars by
Cartype. If the relationship WAS Cartype HasMany Car (ie. a Car can
only have one Cartype) it would be easy
$search_criteria = array('SUV', 'Truck', 'Convertible');
$params = array(
'conditions' = array('Car.cartype_id' => $search_criteria);
)
$this->Car->find('all', $params);
BUT, the Car model can have many Cartypes...meaning the Car model does
not have a `cartype_id` field.
So how do construct a query that would mean, "give me all the Cars
that are related to at least one Cartype contained in the search
criteria"? Does this make sense?
Thanks.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help with Complex Find Conditions
Thank you thank you very very much appreciated
brian-9 wrote:
>
>
> My bad. Try this:
>
> $this->Recipe->find('all', array('conditions' => array(
> 'OR' => array(
> 'Recipe.name LIKE' => '%' . $item . '%',
> 'Recipe.caption LIKE' => '%' . $item . '%'
> )
> ),
> 'order' => 'Recipe.name',
> 'recursive' => 1
> )
> );
>
> Aso, set debug to 2 and look at the SELECT Cake is generating.
>
> On Thu, Feb 5, 2009 at 12:17 PM, monirr444 wrote:
>>
>>
>>
>>
>>
>> function search($item = null) {
>> $item = $this->params['url']['q'];
>>$recipes = $this->Recipe->find('all', array('conditions' => array(
>> 'OR' =>array(
>> array('Recipe.name LIKE' => '%' . $item . '%',
>> array('Recipe.caption LIKE' => '%' . $item . '%',
>>
>>'order' =>
>> 'Recipe.name',
>>
>> 'recursive' => 1,
>> ));
>>
>> i tried put wont give the results as u can see above
>>
>> when i put individual it work lets say Recipe.ingredients LIKE
>> its show the the result good but only ingredients but i want both
>>
>>
>> thank you very much appreciated
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> brian-9 wrote:
>>>
>>>
>>> try
>>>
>>> 'conditions' => array(
>>> 'OR' => array(
>>> array('Recipe.name LIKE' => '%' . $item . '%'),
>>> array('Recipe.ingredients LIKE' => '%' . $item . '%')
>>> )
>>> )
>>>
>>> On Thu, Feb 5, 2009 at 10:34 AM, monirr444 wrote:
>>>>
>>>>
>>>> function search($item = null) {
>>>>$item = $this->params['url']['q'];
>>>>$recipes = $this->Recipe->find('all', array('conditions'
>>>> =>
>>>> array('Recipe.name LIKE' => '%' . $item . '%'),
>>>>'order' =>
>>>> 'Recipe.name',
>>>>'recursive' => 1,
>>>> ));
>>>>
>>>>//var_dump($recipes);
>>>>
>>>>if (!$item) {
>>>>$this->Session->setFlash(__('No Search Items
>>>> Found',
>>>> true));
>>>>$this->redirect(array('action'=>'index'));
>>>>}
>>>>
>>>>$this->set('search', $recipes);
>>>>}
>>>>
>>>> this is my public website search when ever i search it give me results
>>>> by
>>>> name
>>>> how to put 2 condations so it search by name and ingredients
>>>> i used "OR" but didnt work maybe i used it worng
>>>> like this
>>>>
>>>> "array('Recipe.name LIKE' => '%' . $item . '%'), "
>>>> "array('Recipe.ingredients LIKE' => '%' . $item . '%'), "
>>>> i wanna 2 these whenever i search by name and ingredients it should
>>>> give
>>>> both results
>>>> i looked the cake book i coudnt find any
>>>>
>>>> thank you so much
>>>> --
>>>> View this message in context:
>>>> http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2275441.html
>>>> Sent from the CakePHP mailing list archive at Nabble.com.
>>>>
>>>>
>>>> >
>>>>
>>>
>>> >
>>>
>>>
>>
>> --
>> View this message in context:
>> http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2276013.html
>> Sent from the CakePHP mailing list archive at Nabble.com.
>>
>>
>> >
>>
>
> >
>
>
--
View this message in context:
http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2276368.html
Sent from the CakePHP mailing list archive at Nabble.com.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help with Complex Find Conditions
My bad. Try this:
$this->Recipe->find('all', array('conditions' => array(
'OR' => array(
'Recipe.name LIKE' => '%' . $item . '%',
'Recipe.caption LIKE' => '%' . $item . '%'
)
),
'order' => 'Recipe.name',
'recursive' => 1
)
);
Aso, set debug to 2 and look at the SELECT Cake is generating.
On Thu, Feb 5, 2009 at 12:17 PM, monirr444 wrote:
>
>
>
>
>
> function search($item = null) {
> $item = $this->params['url']['q'];
>$recipes = $this->Recipe->find('all', array('conditions' => array(
> 'OR' =>array(
> array('Recipe.name LIKE' => '%' . $item . '%',
> array('Recipe.caption LIKE' => '%' . $item . '%',
>
>'order' =>
> 'Recipe.name',
>
> 'recursive' => 1,
> ));
>
> i tried put wont give the results as u can see above
>
> when i put individual it work lets say Recipe.ingredients LIKE
> its show the the result good but only ingredients but i want both
>
>
> thank you very much appreciated
>
>
>
>
>
>
>
>
>
>
>
>
>
> brian-9 wrote:
>>
>>
>> try
>>
>> 'conditions' => array(
>> 'OR' => array(
>> array('Recipe.name LIKE' => '%' . $item . '%'),
>> array('Recipe.ingredients LIKE' => '%' . $item . '%')
>> )
>> )
>>
>> On Thu, Feb 5, 2009 at 10:34 AM, monirr444 wrote:
>>>
>>>
>>> function search($item = null) {
>>>$item = $this->params['url']['q'];
>>>$recipes = $this->Recipe->find('all', array('conditions'
>>> =>
>>> array('Recipe.name LIKE' => '%' . $item . '%'),
>>>'order' =>
>>> 'Recipe.name',
>>>'recursive' => 1,
>>> ));
>>>
>>>//var_dump($recipes);
>>>
>>>if (!$item) {
>>>$this->Session->setFlash(__('No Search Items
>>> Found',
>>> true));
>>>$this->redirect(array('action'=>'index'));
>>>}
>>>
>>>$this->set('search', $recipes);
>>>}
>>>
>>> this is my public website search when ever i search it give me results
>>> by
>>> name
>>> how to put 2 condations so it search by name and ingredients
>>> i used "OR" but didnt work maybe i used it worng
>>> like this
>>>
>>> "array('Recipe.name LIKE' => '%' . $item . '%'), "
>>> "array('Recipe.ingredients LIKE' => '%' . $item . '%'), "
>>> i wanna 2 these whenever i search by name and ingredients it should give
>>> both results
>>> i looked the cake book i coudnt find any
>>>
>>> thank you so much
>>> --
>>> View this message in context:
>>> http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2275441.html
>>> Sent from the CakePHP mailing list archive at Nabble.com.
>>>
>>>
>>> >
>>>
>>
>> >
>>
>>
>
> --
> View this message in context:
> http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2276013.html
> Sent from the CakePHP mailing list archive at Nabble.com.
>
>
> >
>
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help with Complex Find Conditions
function search($item = null) {
$item = $this->params['url']['q'];
$recipes = $this->Recipe->find('all', array('conditions' => array(
'OR' =>array(
array('Recipe.name LIKE' => '%' . $item . '%',
array('Recipe.caption LIKE' => '%' . $item . '%',
'order' =>
'Recipe.name',
'recursive' => 1,
));
i tried put wont give the results as u can see above
when i put individual it work lets say Recipe.ingredients LIKE
its show the the result good but only ingredients but i want both
thank you very much appreciated
brian-9 wrote:
>
>
> try
>
> 'conditions' => array(
> 'OR' => array(
> array('Recipe.name LIKE' => '%' . $item . '%'),
> array('Recipe.ingredients LIKE' => '%' . $item . '%')
> )
> )
>
> On Thu, Feb 5, 2009 at 10:34 AM, monirr444 wrote:
>>
>>
>> function search($item = null) {
>>$item = $this->params['url']['q'];
>>$recipes = $this->Recipe->find('all', array('conditions'
>> =>
>> array('Recipe.name LIKE' => '%' . $item . '%'),
>>'order' =>
>> 'Recipe.name',
>>'recursive' => 1,
>> ));
>>
>>//var_dump($recipes);
>>
>>if (!$item) {
>>$this->Session->setFlash(__('No Search Items
>> Found',
>> true));
>>$this->redirect(array('action'=>'index'));
>>}
>>
>>$this->set('search', $recipes);
>>}
>>
>> this is my public website search when ever i search it give me results
>> by
>> name
>> how to put 2 condations so it search by name and ingredients
>> i used "OR" but didnt work maybe i used it worng
>> like this
>>
>> "array('Recipe.name LIKE' => '%' . $item . '%'), "
>> "array('Recipe.ingredients LIKE' => '%' . $item . '%'), "
>> i wanna 2 these whenever i search by name and ingredients it should give
>> both results
>> i looked the cake book i coudnt find any
>>
>> thank you so much
>> --
>> View this message in context:
>> http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2275441.html
>> Sent from the CakePHP mailing list archive at Nabble.com.
>>
>>
>> >
>>
>
> >
>
>
--
View this message in context:
http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2276013.html
Sent from the CakePHP mailing list archive at Nabble.com.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help with Complex Find Conditions
try
'conditions' => array(
'OR' => array(
array('Recipe.name LIKE' => '%' . $item . '%'),
array('Recipe.ingredients LIKE' => '%' . $item . '%')
)
)
On Thu, Feb 5, 2009 at 10:34 AM, monirr444 wrote:
>
>
> function search($item = null) {
>$item = $this->params['url']['q'];
>$recipes = $this->Recipe->find('all', array('conditions' =>
> array('Recipe.name LIKE' => '%' . $item . '%'),
>'order' =>
> 'Recipe.name',
>'recursive' => 1,
> ));
>
>//var_dump($recipes);
>
>if (!$item) {
>$this->Session->setFlash(__('No Search Items Found',
> true));
>$this->redirect(array('action'=>'index'));
>}
>
>$this->set('search', $recipes);
>}
>
> this is my public website search when ever i search it give me results by
> name
> how to put 2 condations so it search by name and ingredients
> i used "OR" but didnt work maybe i used it worng
> like this
>
> "array('Recipe.name LIKE' => '%' . $item . '%'), "
> "array('Recipe.ingredients LIKE' => '%' . $item . '%'), "
> i wanna 2 these whenever i search by name and ingredients it should give
> both results
> i looked the cake book i coudnt find any
>
> thank you so much
> --
> View this message in context:
> http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2275441.html
> Sent from the CakePHP mailing list archive at Nabble.com.
>
>
> >
>
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Help with Complex Find Conditions
function search($item = null) {
$item = $this->params['url']['q'];
$recipes = $this->Recipe->find('all', array('conditions' =>
array('Recipe.name LIKE' => '%' . $item . '%'),
'order' =>
'Recipe.name',
'recursive' => 1,
));
//var_dump($recipes);
if (!$item) {
$this->Session->setFlash(__('No Search Items Found',
true));
$this->redirect(array('action'=>'index'));
}
$this->set('search', $recipes);
}
this is my public website search when ever i search it give me results by
name
how to put 2 condations so it search by name and ingredients
i used "OR" but didnt work maybe i used it worng
like this
"array('Recipe.name LIKE' => '%' . $item . '%'), "
"array('Recipe.ingredients LIKE' => '%' . $item . '%'), "
i wanna 2 these whenever i search by name and ingredients it should give
both results
i looked the cake book i coudnt find any
thank you so much
--
View this message in context:
http://n2.nabble.com/Help-with-Complex-Find-Conditions-tp2275441p2275441.html
Sent from the CakePHP mailing list archive at Nabble.com.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help requested for a "complex" find
On Thu, Jan 8, 2009 at 5:16 AM, Adam Royle wrote:
>
> Hi Rob,
>
> For complex conditions you're probably better off using a combination
> of strings and arrays.
>
> I find they're much simpler to read than cake's array syntax...
>
> 'conditions' => array(
>array('alert_subject_id' => $subject),
>'(Alert.start_date IS NULL OR Alert.start_date < NOW())',
>'(Alert.end_date IS NULL OR Alert.end_date > NOW())',
> )
I like that. To my eye, at least, it's far more readable. Thanks for the hint.
> Also, the reason why your initial array wasn't working is because you
> defined two "OR" keys in the same array, when only one can exist.
Yeah, eventually I figured that out on my own (which was a very small
consolation for not having seen it immediately). :-)
--
Rob Wilkerson
http://robwilkerson.org
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help requested for a "complex" find
Hi Rob,
For complex conditions you're probably better off using a combination
of strings and arrays.
I find they're much simpler to read than cake's array syntax...
'conditions' => array(
array('alert_subject_id' => $subject),
'(Alert.start_date IS NULL OR Alert.start_date < NOW())',
'(Alert.end_date IS NULL OR Alert.end_date > NOW())',
)
Also, the reason why your initial array wasn't working is because you
defined two "OR" keys in the same array, when only one can exist.
Cheers,
Adam
On Jan 8, 6:58 am, Rob Wilkerson wrote:
> I'm working on what I guess I would consider the very low end of a
> complex find and yet I can't seem to get it quite right.
>
> In my Alert model, I'm trying to find all of the alerts of a
> particular type whose start date is null or less than NOW and whose
> end date is null or greater than NOW:
>
> return $this->find (
> 'list',
> array (
> 'fields' => array ( 'Alert.message' ),
> 'conditions' => array (
> 'Alert.alert_subject_id' => strtoupper ( $subject ),
> 'OR' => array (
> 'Alert.start_date' => null,
> 'Alert.start_date <= ' => date ( 'Y-m-d
> H:i:s' )
> ),
> 'OR' => array (
> 'Alert.end_date' => null,
> 'Alert.end_date >= ' => date ( 'Y-m-d
> H:i:s' )
> )
> )
> )
> );
>
> The SQL I get out seems to ignore the start_date conditions, but
> includes the end_date conditions as I'd expect. If I switch the order,
> then the end_date conditions are ignored and the start_date conditions
> are translated to SQL as I'd expect. In my debug output, here's what I
> get for this particular find() syntax (formatted for some semblance of
> readability):
>
> SELECT
> `Alert`.`id`,
> `Alert`.`message`
> FROM `alerts` AS `Alert`
> WHERE
> `Alert`.`alert_subject_id` = 'TICKET'
> AND (
> (`Alert`.`end_date` IS NULL) OR (`Alert`.`end_date` >=
> '2009-01-07 15:54:09')
> )
>
> As you can see, no mention of the start_date conditions at all. Am I
> missing an array or something? Have I been looking at this too long to
> see something else that's absurdly obvious? I'm using the final
> release of v1.2.
>
> Any additional eyeballs would be a much appreciated sanity check.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help requested for a "complex" find
On Wed, Jan 7, 2009 at 6:05 PM, gearvOsh wrote:
>
> Can you paste the query you get with this, would be easier to help:
>
> return $this->find ('list', array(
>'fields' => array ('Alert.message'),
>'conditions' => array(
>'Alert.alert_subject_id' => strtoupper ($subject),
>'AND' => array(
>'OR' => array(
>'Alert.start_date' => null,
>'Alert.start_date <= ' => date ( 'Y-m-d H:i:s' )
>),
>'OR' => array(
>'Alert.end_date' => null,
>'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' )
>)
>)
>)
> ));
Sorry, gearvOsh. I don't remember it precisely , but as I recally,
rather than the AND of two ORs: (a or b) AND (c or d), it returned the
OR of two ANDs: (a and b) OR (c and d). I did get it working as shown,
though, if that helps any. If you're curious for academic reasons,
i'll be happy to throw the query in and post the results for you.
Thanks again for your time.
--
Rob Wilkerson
http://robwilkerson.org
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help requested for a "complex" find
Can you paste the query you get with this, would be easier to help:
return $this->find ('list', array(
'fields' => array ('Alert.message'),
'conditions' => array(
'Alert.alert_subject_id' => strtoupper ($subject),
'AND' => array(
'OR' => array(
'Alert.start_date' => null,
'Alert.start_date <= ' => date ( 'Y-m-d H:i:s' )
),
'OR' => array(
'Alert.end_date' => null,
'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' )
)
)
)
));
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help requested for a "complex" find
On Wed, Jan 7, 2009 at 5:12 PM, Rob Wilkerson wrote: > On Wed, Jan 7, 2009 at 4:50 PM, gearvOsh wrote: >> >> Here you go: >> >> return $this->find ( >>'list', >>array ( >>'fields' => array ( 'Alert.message' ), >>'conditions' => array ( >>'Alert.alert_subject_id' => strtoupper ( $subject ), >>'OR' => array( >>array( >>'Alert.start_date' => null, >>'Alert.start_date <= ' => date ( 'Y-m-d >> H:i:s' ) >>), >>array ( >>'Alert.end_date' => null, >>'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' ) >>) >>) >>) >>) >> ); > > I really appreciate the help, but that's not quite right. That syntax > ANDs the two start_date values and ANDs the two end_date values, but > ORs the two condition pairs. I need to do exactly the opposite: > (start_date condition1 OR start_date condition2) AND (end_date > condition1 OR end_date condition2). Aaaah. Having finally realized that I was defining two 'OR' keys in the same array, I tried it by creating an array out of each or and it worked. The syntax doesn't read very well to me as a newbie, but it does make sense: return $this->find ( 'list', array ( 'fields' => array ( 'Alert.message' ), 'conditions' => array ( 'Alert.alert_subject_id' => strtoupper ( $subject ), array ( 'OR' => array ( 'Alert.start_date' => null, 'Alert.start_date <= ' => date ( 'Y-m-d H:i:s' ) ) ), array ( 'OR' => array ( 'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' ) , 'Alert.end_date' => null ) ) ) ) ); -- Rob Wilkerson http://robwilkerson.org --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Help requested for a "complex" find
On Wed, Jan 7, 2009 at 4:50 PM, gearvOsh wrote: > > Here you go: > > return $this->find ( >'list', >array ( >'fields' => array ( 'Alert.message' ), >'conditions' => array ( >'Alert.alert_subject_id' => strtoupper ( $subject ), >'OR' => array( >array( >'Alert.start_date' => null, >'Alert.start_date <= ' => date ( 'Y-m-d H:i:s' > ) >), >array ( >'Alert.end_date' => null, >'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' ) >) >) >) >) > ); I really appreciate the help, but that's not quite right. That syntax ANDs the two start_date values and ANDs the two end_date values, but ORs the two condition pairs. I need to do exactly the opposite: (start_date condition1 OR start_date condition2) AND (end_date condition1 OR end_date condition2). Your solution lead me to try this (which I think I'd already tried, but after a while I lost track): return $this->find ( 'list', array ( 'fields' => array ( 'Alert.message' ), 'conditions' => array ( 'Alert.alert_subject_id' => strtoupper ( $subject ), 'AND' => array ( 'OR' => array ( 'Alert.start_date' => null, 'Alert.start_date <= ' => date ( 'Y-m-d H:i:s' ) ), 'OR' => array ( 'Alert.end_date' => null, 'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' ) ) ) ) ) ); Unfortunately, I got the same result as above. I also tried another variation on that with no luck. I'm going to try a simple, stupid test next to see if I can get it working in a simpler form and work my way up to what I need. I jumped right in because I didn't expect to have so much trouble with it. :-) Thanks again. -- Rob Wilkerson http://robwilkerson.org --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Help requested for a "complex" find
Here you go: return $this->find ( 'list', array ( 'fields' => array ( 'Alert.message' ), 'conditions' => array ( 'Alert.alert_subject_id' => strtoupper ( $subject ), 'OR' => array( array( 'Alert.start_date' => null, 'Alert.start_date <= ' => date ( 'Y-m-d H:i:s' ) ), array ( 'Alert.end_date' => null, 'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' ) ) ) ) ) ); --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Help requested for a "complex" find
I'm working on what I guess I would consider the very low end of a complex find and yet I can't seem to get it quite right. In my Alert model, I'm trying to find all of the alerts of a particular type whose start date is null or less than NOW and whose end date is null or greater than NOW: return $this->find ( 'list', array ( 'fields' => array ( 'Alert.message' ), 'conditions' => array ( 'Alert.alert_subject_id' => strtoupper ( $subject ), 'OR' => array ( 'Alert.start_date' => null, 'Alert.start_date <= ' => date ( 'Y-m-d H:i:s' ) ), 'OR' => array ( 'Alert.end_date' => null, 'Alert.end_date >= ' => date ( 'Y-m-d H:i:s' ) ) ) ) ); The SQL I get out seems to ignore the start_date conditions, but includes the end_date conditions as I'd expect. If I switch the order, then the end_date conditions are ignored and the start_date conditions are translated to SQL as I'd expect. In my debug output, here's what I get for this particular find() syntax (formatted for some semblance of readability): SELECT `Alert`.`id`, `Alert`.`message` FROM `alerts` AS `Alert` WHERE `Alert`.`alert_subject_id` = 'TICKET' AND ( (`Alert`.`end_date` IS NULL) OR (`Alert`.`end_date` >= '2009-01-07 15:54:09') ) As you can see, no mention of the start_date conditions at all. Am I missing an array or something? Have I been looking at this too long to see something else that's absurdly obvious? I'm using the final release of v1.2. Any additional eyeballs would be a much appreciated sanity check. --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Help with complex find() spanning several models
Awesome. Thanks.
On Oct 31, 4:37 pm, teknoid <[EMAIL PROTECTED]> wrote:
> By default joins are only built for hasOne or belongsTo.
> Here's how to "trick" cake into building joins for deep model
> bindings:http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/
>
> there is another post on my blog if you search, which has a slightly
> more advanced example of the same principal.
>
> On Oct 31, 4:28 pm, 33rtp <[EMAIL PROTECTED]> wrote:
>
> > Thanks for the help teknoid. I've done that, and I think there are
> > some possibilities there, but they are a little longer than I'd like.
>
> > For those who might come after this and not feel like reading the full
> > situation above, my question in a nut-shell really is:
>
> > How do you run a find on a field in a Model that is two associations
> > away rather than just one?
>
> > E.g. - When querying Post which belongsTo Artist which hasMany
> > Subscription, how do I compare a field in Post to a field in
> > Subscription? Cake doesn't seem to want to create a second JOIN in
> > the SQL output.
>
> > On Oct 31, 3:28 pm, teknoid <[EMAIL PROTECTED]> wrote:
>
> > > That's a lot to read, but I can point you in the direction of checking
> > > out the Containable behavior (in the manual).
> > > ... as well as really carefully reading up on the model associations
> > > and data retrieval.
>
> > > On Oct 31, 2:30 pm, 33rtp <[EMAIL PROTECTED]> wrote:
>
> > > > Hey all...
>
> > > > New to PHP, Cake, and MySQL so bear with me.
>
> > > > I've been searching high and low for the best way to make this query
> > > > work, but just haven't gotten it yet.
>
> > > > Here's my setup:
>
> > > > I have models for Users, Subscriptions, Authors, and Posts where:
> > > > User HasMany Subscription (pk_User.id, fk_Subscription.user_id),
> > > > Author, HasMany Subscription (pk_Author.id,
> > > > fk_Subscription.author_id),
> > > > Author HasMany Post (pk_Author.id, fk_Post.author_id)
> > > > and all of the related BelongsTo's as well.
>
> > > > I want to search $this->User->Subscription->Author->Post (from the
> > > > UsersController) for all posts where the logged in user has a current
> > > > subscription to that(/those) author(s).
>
> > > > E.g. - ($this->Auth->user('id') = Subscription.user_id AND
> > > > Subscription.expiration_date >= date ('Y m d') AND
> > > > Subscription.author_id = Post.author_id).
>
> > > > Further, each Post contains a second field (INT) called
> > > > Post.access_level and this must be lower than the value stored in
> > > > Subscriptions.subscription_level.
>
> > > > The trick, of course, isn't just doing this, but doing it
> > > > efficiently. Here are the options I've thought of.
>
> > > > - I could query $this->User->Subscriptions for relevant subscriptions
> > > > and return either the full array (set to $subscriptions) or a 'list'
> > > > of key/value pairs where key => author_id and value =>
> > > > subscription_level. However, in issuing a second find() call to Post,
> > > > I don't know how I would compare the key/value pairs in the
> > > > $subscriptions array with the values for 'Post.author_id' and
> > > > 'Post.access_level' where the evaluation occurs at the row level in
> > > > $subscriptions. With the find('all') array I mentioned first,
> > > > $subscriptions returns an array with [key][Subscription][field] so I
> > > > can't set conditions for 'Post.author_id' and 'Post.access_level'
> > > > without a foreach() which I don't want because of the extra database
> > > > queries it would generate.
>
> > > > -Alternately, I could use BindModel() to create a HABTM (or I could
> > > > just create it permanently in the models) relationship between
> > > > Subscription and Post. This option requires an extra join table in my
> > > > database though, and may result in slower database queries as all the
> > > > joins are performed. Additionally, there are other models (File,
> > > > Event, etc) that are owned by Author and each of these would require
> > > > an extra join table and HABTM relationship. I could be wrong, but
> > > > doing all of this seems somehow redundant and there should be a more
> > > > elegant solution.
>
> > > > -There are also probably other ways using PHP functions to manipulate
> > > > the arrays after they have been returned from find calls (e.g.
> > > > Subscriptions->find w/ conditions and Post->find w/ conditions and
> > > > then some PHP array functions to compare those two arrays), but I'm
> > > > too new to this to know where to even start with that.
>
> > > > There's got to be a simple method I'm missing (or just don't know
> > > > about yet). Any ideas?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cak
Re: Help with complex find() spanning several models
By default joins are only built for hasOne or belongsTo.
Here's how to "trick" cake into building joins for deep model
bindings:
http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/
there is another post on my blog if you search, which has a slightly
more advanced example of the same principal.
On Oct 31, 4:28 pm, 33rtp <[EMAIL PROTECTED]> wrote:
> Thanks for the help teknoid. I've done that, and I think there are
> some possibilities there, but they are a little longer than I'd like.
>
> For those who might come after this and not feel like reading the full
> situation above, my question in a nut-shell really is:
>
> How do you run a find on a field in a Model that is two associations
> away rather than just one?
>
> E.g. - When querying Post which belongsTo Artist which hasMany
> Subscription, how do I compare a field in Post to a field in
> Subscription? Cake doesn't seem to want to create a second JOIN in
> the SQL output.
>
> On Oct 31, 3:28 pm, teknoid <[EMAIL PROTECTED]> wrote:
>
> > That's a lot to read, but I can point you in the direction of checking
> > out the Containable behavior (in the manual).
> > ... as well as really carefully reading up on the model associations
> > and data retrieval.
>
> > On Oct 31, 2:30 pm, 33rtp <[EMAIL PROTECTED]> wrote:
>
> > > Hey all...
>
> > > New to PHP, Cake, and MySQL so bear with me.
>
> > > I've been searching high and low for the best way to make this query
> > > work, but just haven't gotten it yet.
>
> > > Here's my setup:
>
> > > I have models for Users, Subscriptions, Authors, and Posts where:
> > > User HasMany Subscription (pk_User.id, fk_Subscription.user_id),
> > > Author, HasMany Subscription (pk_Author.id,
> > > fk_Subscription.author_id),
> > > Author HasMany Post (pk_Author.id, fk_Post.author_id)
> > > and all of the related BelongsTo's as well.
>
> > > I want to search $this->User->Subscription->Author->Post (from the
> > > UsersController) for all posts where the logged in user has a current
> > > subscription to that(/those) author(s).
>
> > > E.g. - ($this->Auth->user('id') = Subscription.user_id AND
> > > Subscription.expiration_date >= date ('Y m d') AND
> > > Subscription.author_id = Post.author_id).
>
> > > Further, each Post contains a second field (INT) called
> > > Post.access_level and this must be lower than the value stored in
> > > Subscriptions.subscription_level.
>
> > > The trick, of course, isn't just doing this, but doing it
> > > efficiently. Here are the options I've thought of.
>
> > > - I could query $this->User->Subscriptions for relevant subscriptions
> > > and return either the full array (set to $subscriptions) or a 'list'
> > > of key/value pairs where key => author_id and value =>
> > > subscription_level. However, in issuing a second find() call to Post,
> > > I don't know how I would compare the key/value pairs in the
> > > $subscriptions array with the values for 'Post.author_id' and
> > > 'Post.access_level' where the evaluation occurs at the row level in
> > > $subscriptions. With the find('all') array I mentioned first,
> > > $subscriptions returns an array with [key][Subscription][field] so I
> > > can't set conditions for 'Post.author_id' and 'Post.access_level'
> > > without a foreach() which I don't want because of the extra database
> > > queries it would generate.
>
> > > -Alternately, I could use BindModel() to create a HABTM (or I could
> > > just create it permanently in the models) relationship between
> > > Subscription and Post. This option requires an extra join table in my
> > > database though, and may result in slower database queries as all the
> > > joins are performed. Additionally, there are other models (File,
> > > Event, etc) that are owned by Author and each of these would require
> > > an extra join table and HABTM relationship. I could be wrong, but
> > > doing all of this seems somehow redundant and there should be a more
> > > elegant solution.
>
> > > -There are also probably other ways using PHP functions to manipulate
> > > the arrays after they have been returned from find calls (e.g.
> > > Subscriptions->find w/ conditions and Post->find w/ conditions and
> > > then some PHP array functions to compare those two arrays), but I'm
> > > too new to this to know where to even start with that.
>
> > > There's got to be a simple method I'm missing (or just don't know
> > > about yet). Any ideas?
>
>
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help with complex find() spanning several models
Thanks for the help teknoid. I've done that, and I think there are
some possibilities there, but they are a little longer than I'd like.
For those who might come after this and not feel like reading the full
situation above, my question in a nut-shell really is:
How do you run a find on a field in a Model that is two associations
away rather than just one?
E.g. - When querying Post which belongsTo Artist which hasMany
Subscription, how do I compare a field in Post to a field in
Subscription? Cake doesn't seem to want to create a second JOIN in
the SQL output.
On Oct 31, 3:28 pm, teknoid <[EMAIL PROTECTED]> wrote:
> That's a lot to read, but I can point you in the direction of checking
> out the Containable behavior (in the manual).
> ... as well as really carefully reading up on the model associations
> and data retrieval.
>
> On Oct 31, 2:30 pm, 33rtp <[EMAIL PROTECTED]> wrote:
>
> > Hey all...
>
> > New to PHP, Cake, and MySQL so bear with me.
>
> > I've been searching high and low for the best way to make this query
> > work, but just haven't gotten it yet.
>
> > Here's my setup:
>
> > I have models for Users, Subscriptions, Authors, and Posts where:
> > User HasMany Subscription (pk_User.id, fk_Subscription.user_id),
> > Author, HasMany Subscription (pk_Author.id,
> > fk_Subscription.author_id),
> > Author HasMany Post (pk_Author.id, fk_Post.author_id)
> > and all of the related BelongsTo's as well.
>
> > I want to search $this->User->Subscription->Author->Post (from the
> > UsersController) for all posts where the logged in user has a current
> > subscription to that(/those) author(s).
>
> > E.g. - ($this->Auth->user('id') = Subscription.user_id AND
> > Subscription.expiration_date >= date ('Y m d') AND
> > Subscription.author_id = Post.author_id).
>
> > Further, each Post contains a second field (INT) called
> > Post.access_level and this must be lower than the value stored in
> > Subscriptions.subscription_level.
>
> > The trick, of course, isn't just doing this, but doing it
> > efficiently. Here are the options I've thought of.
>
> > - I could query $this->User->Subscriptions for relevant subscriptions
> > and return either the full array (set to $subscriptions) or a 'list'
> > of key/value pairs where key => author_id and value =>
> > subscription_level. However, in issuing a second find() call to Post,
> > I don't know how I would compare the key/value pairs in the
> > $subscriptions array with the values for 'Post.author_id' and
> > 'Post.access_level' where the evaluation occurs at the row level in
> > $subscriptions. With the find('all') array I mentioned first,
> > $subscriptions returns an array with [key][Subscription][field] so I
> > can't set conditions for 'Post.author_id' and 'Post.access_level'
> > without a foreach() which I don't want because of the extra database
> > queries it would generate.
>
> > -Alternately, I could use BindModel() to create a HABTM (or I could
> > just create it permanently in the models) relationship between
> > Subscription and Post. This option requires an extra join table in my
> > database though, and may result in slower database queries as all the
> > joins are performed. Additionally, there are other models (File,
> > Event, etc) that are owned by Author and each of these would require
> > an extra join table and HABTM relationship. I could be wrong, but
> > doing all of this seems somehow redundant and there should be a more
> > elegant solution.
>
> > -There are also probably other ways using PHP functions to manipulate
> > the arrays after they have been returned from find calls (e.g.
> > Subscriptions->find w/ conditions and Post->find w/ conditions and
> > then some PHP array functions to compare those two arrays), but I'm
> > too new to this to know where to even start with that.
>
> > There's got to be a simple method I'm missing (or just don't know
> > about yet). Any ideas?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help with complex find() spanning several models
On Oct 31, 3:28 pm, teknoid <[EMAIL PROTECTED]> wrote:
> That's a lot to read, but I can point you in the direction of checking
> out the Containable behavior (in the manual).
> ... as well as really carefully reading up on the model associations
> and data retrieval.
>
> On Oct 31, 2:30 pm, 33rtp <[EMAIL PROTECTED]> wrote:
>
> > Hey all...
>
> > New to PHP, Cake, and MySQL so bear with me.
>
> > I've been searching high and low for the best way to make this query
> > work, but just haven't gotten it yet.
>
> > Here's my setup:
>
> > I have models for Users, Subscriptions, Authors, and Posts where:
> > User HasMany Subscription (pk_User.id, fk_Subscription.user_id),
> > Author, HasMany Subscription (pk_Author.id,
> > fk_Subscription.author_id),
> > Author HasMany Post (pk_Author.id, fk_Post.author_id)
> > and all of the related BelongsTo's as well.
>
> > I want to search $this->User->Subscription->Author->Post (from the
> > UsersController) for all posts where the logged in user has a current
> > subscription to that(/those) author(s).
>
> > E.g. - ($this->Auth->user('id') = Subscription.user_id AND
> > Subscription.expiration_date >= date ('Y m d') AND
> > Subscription.author_id = Post.author_id).
>
> > Further, each Post contains a second field (INT) called
> > Post.access_level and this must be lower than the value stored in
> > Subscriptions.subscription_level.
>
> > The trick, of course, isn't just doing this, but doing it
> > efficiently. Here are the options I've thought of.
>
> > - I could query $this->User->Subscriptions for relevant subscriptions
> > and return either the full array (set to $subscriptions) or a 'list'
> > of key/value pairs where key => author_id and value =>
> > subscription_level. However, in issuing a second find() call to Post,
> > I don't know how I would compare the key/value pairs in the
> > $subscriptions array with the values for 'Post.author_id' and
> > 'Post.access_level' where the evaluation occurs at the row level in
> > $subscriptions. With the find('all') array I mentioned first,
> > $subscriptions returns an array with [key][Subscription][field] so I
> > can't set conditions for 'Post.author_id' and 'Post.access_level'
> > without a foreach() which I don't want because of the extra database
> > queries it would generate.
>
> > -Alternately, I could use BindModel() to create a HABTM (or I could
> > just create it permanently in the models) relationship between
> > Subscription and Post. This option requires an extra join table in my
> > database though, and may result in slower database queries as all the
> > joins are performed. Additionally, there are other models (File,
> > Event, etc) that are owned by Author and each of these would require
> > an extra join table and HABTM relationship. I could be wrong, but
> > doing all of this seems somehow redundant and there should be a more
> > elegant solution.
>
> > -There are also probably other ways using PHP functions to manipulate
> > the arrays after they have been returned from find calls (e.g.
> > Subscriptions->find w/ conditions and Post->find w/ conditions and
> > then some PHP array functions to compare those two arrays), but I'm
> > too new to this to know where to even start with that.
>
> > There's got to be a simple method I'm missing (or just don't know
> > about yet). Any ideas?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Help with complex find() spanning several models
That's a lot to read, but I can point you in the direction of checking
out the Containable behavior (in the manual).
... as well as really carefully reading up on the model associations
and data retrieval.
On Oct 31, 2:30 pm, 33rtp <[EMAIL PROTECTED]> wrote:
> Hey all...
>
> New to PHP, Cake, and MySQL so bear with me.
>
> I've been searching high and low for the best way to make this query
> work, but just haven't gotten it yet.
>
> Here's my setup:
>
> I have models for Users, Subscriptions, Authors, and Posts where:
> User HasMany Subscription (pk_User.id, fk_Subscription.user_id),
> Author, HasMany Subscription (pk_Author.id,
> fk_Subscription.author_id),
> Author HasMany Post (pk_Author.id, fk_Post.author_id)
> and all of the related BelongsTo's as well.
>
> I want to search $this->User->Subscription->Author->Post (from the
> UsersController) for all posts where the logged in user has a current
> subscription to that(/those) author(s).
>
> E.g. - ($this->Auth->user('id') = Subscription.user_id AND
> Subscription.expiration_date >= date ('Y m d') AND
> Subscription.author_id = Post.author_id).
>
> Further, each Post contains a second field (INT) called
> Post.access_level and this must be lower than the value stored in
> Subscriptions.subscription_level.
>
> The trick, of course, isn't just doing this, but doing it
> efficiently. Here are the options I've thought of.
>
> - I could query $this->User->Subscriptions for relevant subscriptions
> and return either the full array (set to $subscriptions) or a 'list'
> of key/value pairs where key => author_id and value =>
> subscription_level. However, in issuing a second find() call to Post,
> I don't know how I would compare the key/value pairs in the
> $subscriptions array with the values for 'Post.author_id' and
> 'Post.access_level' where the evaluation occurs at the row level in
> $subscriptions. With the find('all') array I mentioned first,
> $subscriptions returns an array with [key][Subscription][field] so I
> can't set conditions for 'Post.author_id' and 'Post.access_level'
> without a foreach() which I don't want because of the extra database
> queries it would generate.
>
> -Alternately, I could use BindModel() to create a HABTM (or I could
> just create it permanently in the models) relationship between
> Subscription and Post. This option requires an extra join table in my
> database though, and may result in slower database queries as all the
> joins are performed. Additionally, there are other models (File,
> Event, etc) that are owned by Author and each of these would require
> an extra join table and HABTM relationship. I could be wrong, but
> doing all of this seems somehow redundant and there should be a more
> elegant solution.
>
> -There are also probably other ways using PHP functions to manipulate
> the arrays after they have been returned from find calls (e.g.
> Subscriptions->find w/ conditions and Post->find w/ conditions and
> then some PHP array functions to compare those two arrays), but I'm
> too new to this to know where to even start with that.
>
> There's got to be a simple method I'm missing (or just don't know
> about yet). Any ideas?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Help with complex find() spanning several models
Hey all...
New to PHP, Cake, and MySQL so bear with me.
I've been searching high and low for the best way to make this query
work, but just haven't gotten it yet.
Here's my setup:
I have models for Users, Subscriptions, Authors, and Posts where:
User HasMany Subscription (pk_User.id, fk_Subscription.user_id),
Author, HasMany Subscription (pk_Author.id,
fk_Subscription.author_id),
Author HasMany Post (pk_Author.id, fk_Post.author_id)
and all of the related BelongsTo's as well.
I want to search $this->User->Subscription->Author->Post (from the
UsersController) for all posts where the logged in user has a current
subscription to that(/those) author(s).
E.g. - ($this->Auth->user('id') = Subscription.user_id AND
Subscription.expiration_date >= date ('Y m d') AND
Subscription.author_id = Post.author_id).
Further, each Post contains a second field (INT) called
Post.access_level and this must be lower than the value stored in
Subscriptions.subscription_level.
The trick, of course, isn't just doing this, but doing it
efficiently. Here are the options I've thought of.
- I could query $this->User->Subscriptions for relevant subscriptions
and return either the full array (set to $subscriptions) or a 'list'
of key/value pairs where key => author_id and value =>
subscription_level. However, in issuing a second find() call to Post,
I don't know how I would compare the key/value pairs in the
$subscriptions array with the values for 'Post.author_id' and
'Post.access_level' where the evaluation occurs at the row level in
$subscriptions. With the find('all') array I mentioned first,
$subscriptions returns an array with [key][Subscription][field] so I
can't set conditions for 'Post.author_id' and 'Post.access_level'
without a foreach() which I don't want because of the extra database
queries it would generate.
-Alternately, I could use BindModel() to create a HABTM (or I could
just create it permanently in the models) relationship between
Subscription and Post. This option requires an extra join table in my
database though, and may result in slower database queries as all the
joins are performed. Additionally, there are other models (File,
Event, etc) that are owned by Author and each of these would require
an extra join table and HABTM relationship. I could be wrong, but
doing all of this seems somehow redundant and there should be a more
elegant solution.
-There are also probably other ways using PHP functions to manipulate
the arrays after they have been returned from find calls (e.g.
Subscriptions->find w/ conditions and Post->find w/ conditions and
then some PHP array functions to compare those two arrays), but I'm
too new to this to know where to even start with that.
There's got to be a simple method I'm missing (or just don't know
about yet). Any ideas?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Troubles doing a complex find
Ok thanks and have a nice day!
On 10/15/2008, Benni Graf <[EMAIL PROTECTED]> wrote:
>
> Hi there!
>
> Thank you very much everybody, that actually should've jumped my eye
> by itself, I'm sorry for bothering you ;-)...
>
> For sure those 'AND' as array-indexes overwrite each other, so only
> the last statement made it to the query-generating magic.
>
> For the records: The solution is to 'wrap' those three 'AND'-arrays
> into another, unindexed, array:
>
> 'OR'=>array(
> array('AND'=>array(
>// some conditions
> )),
> array('AND'=>array(
>// some other conditions
> )),
> array('AND'=>array(
>// even more conditions
> ))
> )
>
> I actually don't know if that's the most beautiful solution, since
> those 'wrapper'-arrays look kind of a thorn in my side. So if anybody
> would come up with a better solution, I'd totally go for it ;-). But
> principally it works like this.
>
> Once again thank you, have a nice day,
> Benni.
>
> Btw. @Xaver Mathews: I wanted the results where either of the three
> condition-blocks (consisting of 3-4 statements which all had to be
> true) are true.
> >
>
--
Xavier A. Mathews
Student/Developer/Web-Master
GG Client Based Tech Support Specialist
Hazel Crest Illinois
[EMAIL PROTECTED]
"Fear of a name, only increases fear of the thing itself."
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Troubles doing a complex find
Hi there!
Thank you very much everybody, that actually should've jumped my eye
by itself, I'm sorry for bothering you ;-)...
For sure those 'AND' as array-indexes overwrite each other, so only
the last statement made it to the query-generating magic.
For the records: The solution is to 'wrap' those three 'AND'-arrays
into another, unindexed, array:
'OR'=>array(
array('AND'=>array(
// some conditions
)),
array('AND'=>array(
// some other conditions
)),
array('AND'=>array(
// even more conditions
))
)
I actually don't know if that's the most beautiful solution, since
those 'wrapper'-arrays look kind of a thorn in my side. So if anybody
would come up with a better solution, I'd totally go for it ;-). But
principally it works like this.
Once again thank you, have a nice day,
Benni.
Btw. @Xaver Mathews: I wanted the results where either of the three
condition-blocks (consisting of 3-4 statements which all had to be
true) are true.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Troubles doing a complex find
I am sorry but what is the point of this post? What is the problem? On 10/14/2008, ORCC <[EMAIL PROTECTED]> wrote: > > AND logical operator is asociative, hence: > > 'OR' => Array ( >'AND' => Array ( /*put all conditions here*/) > ) > > is equivalent, and here you don't rewrite the Array index. > > > > > -- Xavier A. Mathews Student/Developer/Web-Master GG Client Based Tech Support Specialist Hazel Crest Illinois [EMAIL PROTECTED] "Fear of a name, only increases fear of the thing itself." --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Troubles doing a complex find
AND logical operator is asociative, hence: 'OR' => Array ( 'AND' => Array ( /*put all conditions here*/) ) is equivalent, and here you don't rewrite the Array index. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Troubles doing a complex find
Hi there!
I'm getting some data via this statement:
$offtimes = $this->Offtime->find('all', array(
'conditions'=>array(
'AND'=>array(
'Offtime.therapist_id'=>$therapistid, // appears in the query
'OR'=>array(
'AND'=>array(
// some conditions // don't appear in the query
),
'AND'=>array(
// some other conditions // don't appear in the query
),
'AND'=>array(
// more conditions - only those appear in the query!
)
)
)
),
'recursive'=>-1
));
(left the actual conditions out for clarity)
Unfortunately, only the last of the 3 AND-block gets considered for
the query, the others are 'ignored' (they just don't appear in the
query). The querys given in debug2-mode show me that. Also when I
shuffle the order of the 3 AND-Blocks, only the last one appears in
the query... Any ideas?
Best regards and thanks for the Help!
Btw, if you want to post some code, I also posted this to the bin...:
http://bin.cakephp.org/saved/38480
Greetings, Benni.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Troubles doing a complex find
It looks to me like it's nothing to do with Cake - see your array:
'OR'=>array(
'AND'=>array(
// some conditions // don't appear in the query
),
'AND'=>array(
// some other conditions // don't appear in the query
),
'AND'=>array(
// more conditions - only those appear in the query!
)
The OR array has multiple settings under the key AND - each of your
new ands overwrites the previous one, hence only the last conditions
appear.
I'm no expert, so take a look at
http://book.cakephp.org/view/74/Complex-Find-Conditions
or search this group for good examples, but my guess would be that all
the conditions should go in just one AND array...
On Oct 14, 11:30 pm, Benni Graf <[EMAIL PROTECTED]> wrote:
> Hi there!
> I'm getting some data via this statement:
>
> $offtimes = $this->Offtime->find('all', array(
> 'conditions'=>array(
> 'AND'=>array(
> 'Offtime.therapist_id'=>$therapistid, // appears in the query
> 'OR'=>array(
> 'AND'=>array(
> // some conditions // don't appear in the query
> ),
> 'AND'=>array(
> // some other conditions // don't appear in the query
> ),
> 'AND'=>array(
> // more conditions - only those appear in the query!
> )
> )
> )
> ),
> 'recursive'=>-1
> ));
>
> (left the actual conditions out for clarity)
>
> Unfortunately, only the last of the 3 AND-block gets considered for
> the query, the others are 'ignored' (they just don't appear in the
> query). The querys given in debug2-mode show me that. Also when I
> shuffle the order of the 3 AND-Blocks, only the last one appears in
> the query... Any ideas?
>
> Best regards and thanks for the Help!
>
> Btw, if you want to post some code, I also posted this to the
> bin...:http://bin.cakephp.org/saved/38480
>
> Greetings, Benni.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find with HABTM Relationship
Eek that first line was supposed to read Hey all, I'm sorry IF something like this already exists but I really had no clue what to search for. On Oct 12, 10:26 pm, JoshSchramm <[EMAIL PROTECTED]> wrote: > Hey all, I'm sorry something like this already exists but I really had > no clue what to search for. > > I'm trying to set up a rather complex find. It works something like > this. > > I have users, those users can have friends (think social networking > esque). Friends is simply a backrefrence to the users table. I.e. my > join table Friends_Users contains to fields (user_id, friend_id) both > of which point to the Users table. > > Users can have Tips. Therefore Friends have tips. > > Relationally > > Users HABTM Friends. > Users HasMany Tips > Tips BelongsTo Users > > I'm trying to do a find that returns all the tips owned by any friend > of the passed in user as well as any tips owned by the user itself. > > The following SQL query works - > > SELECT * > FROM `tips` > JOIN users ON users.id = tips.user_id > JOIN friends_users ON tips.user_id = friends_users.friend_id > WHERE (friends_users.user_id =2 or tips.user_id=2) > LIMIT 0 , 30 > > But i have no idea how to do that in cakephp world. I posted this on > stackoverflow here > -http://stackoverflow.com/questions/196488/complex-find-in-cake-php > - but havent heard much back yet. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Complex Find with HABTM Relationship
Hey all, I'm sorry something like this already exists but I really had no clue what to search for. I'm trying to set up a rather complex find. It works something like this. I have users, those users can have friends (think social networking esque). Friends is simply a backrefrence to the users table. I.e. my join table Friends_Users contains to fields (user_id, friend_id) both of which point to the Users table. Users can have Tips. Therefore Friends have tips. Relationally Users HABTM Friends. Users HasMany Tips Tips BelongsTo Users I'm trying to do a find that returns all the tips owned by any friend of the passed in user as well as any tips owned by the user itself. The following SQL query works - SELECT * FROM `tips` JOIN users ON users.id = tips.user_id JOIN friends_users ON tips.user_id = friends_users.friend_id WHERE (friends_users.user_id =2 or tips.user_id=2) LIMIT 0 , 30 But i have no idea how to do that in cakephp world. I posted this on stackoverflow here - http://stackoverflow.com/questions/196488/complex-find-in-cake-php - but havent heard much back yet. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Complex find queries: conditions in the assoc model
You have to put the conditions in the association itself.
var $hasMany = array('InvoiceItem' =>
array(
'className' => 'InvoiceItem',
'conditions' => 'InvoiceItem.invoice_id IS NULL',
)
);
If you need to do this dynamically, then look at bindModel /
unbindModel (or any one of the extensions for this process, such as
Mariano's Bindable Behaviour -
http://bakery.cakephp.org/articles/view/bindable-behavior-control-your-model-bindings
);
On Dec 17, 9:00 am, the_woodsman <[EMAIL PROTECTED]> wrote:
> Hi Bakers!
>
> After a long break from development, Im back at it, and I have a
> problem querying my models.
>
> I want to get all client records, where their associated invoiceitems
> (client hasMany invoiceitems) have a particular condition.
>
> Now, If I do
>
> $this->Client->findAll("name='some name' ")
>
> All is well, my invoice table is joined in one query, and all the
> others are joined in their own queries too:
>
> SELECT `Client`.`id`, `Client`.`name`, `Client`.`address`,
> `Client`.`postcode`, `Client`.`contact`, `Client`.`email`,
> `Client`.`method`, `Client`.`paymentterms`, `Client`.`signedinvoices`
> FROM `clients` AS `Client` WHERE name='some name' 1 1
> 0
> 2 SELECT `Invoiceitem`.`id`, `Invoiceitem`.`client_id`,
> `Invoiceitem`.`date`, `Invoiceitem`.`description`,
> `Invoiceitem`.`cost`, `Invoiceitem`.`quantity`,
> `Invoiceitem`.`invoice_id`, `Invoiceitem`.`type`,
> `Invoiceitem`.`service_category_id`, `Invoiceitem`.`user_id` FROM
> `invoiceitems` AS `Invoiceitem` WHERE `Invoiceitem`.`client_id` IN
> (230)
>
> But when I try to add conditions for the associated Invoiceitem:
>
> SELECT `Client`.`id`, `Client`.`name`, `Client`.`address`,
> `Client`.`postcode`, `Client`.`contact`, `Client`.`email`,
> `Client`.`method`, `Client`.`paymentterms`, `Client`.`signedinvoices`
> FROM `clients` AS `Client` WHERE name='some name' AND invoice_id IS
> NULL
>
> 1054: Unknown column 'invoice_id' in 'where clause'
>
> These conditions are inserted in the first query, only for the Client
> table, which of course fails.
>
> I've tried this with adding the table name, no change (1054: Unknown
> column 'Invoiceitem.invoice_id' in 'where clause'), and specifying
> with the new expects behaviour exactly which models use.
>
> What's the Cake way to add conditions for the associated model??
>
> Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Complex find queries: conditions in the assoc model
Hi Bakers!
After a long break from development, Im back at it, and I have a
problem querying my models.
I want to get all client records, where their associated invoiceitems
(client hasMany invoiceitems) have a particular condition.
Now, If I do
$this->Client->findAll("name='some name' ")
All is well, my invoice table is joined in one query, and all the
others are joined in their own queries too:
SELECT `Client`.`id`, `Client`.`name`, `Client`.`address`,
`Client`.`postcode`, `Client`.`contact`, `Client`.`email`,
`Client`.`method`, `Client`.`paymentterms`, `Client`.`signedinvoices`
FROM `clients` AS `Client` WHERE name='some name' 1 1 0
2 SELECT `Invoiceitem`.`id`, `Invoiceitem`.`client_id`,
`Invoiceitem`.`date`, `Invoiceitem`.`description`,
`Invoiceitem`.`cost`, `Invoiceitem`.`quantity`,
`Invoiceitem`.`invoice_id`, `Invoiceitem`.`type`,
`Invoiceitem`.`service_category_id`, `Invoiceitem`.`user_id` FROM
`invoiceitems` AS `Invoiceitem` WHERE `Invoiceitem`.`client_id` IN
(230)
But when I try to add conditions for the associated Invoiceitem:
SELECT `Client`.`id`, `Client`.`name`, `Client`.`address`,
`Client`.`postcode`, `Client`.`contact`, `Client`.`email`,
`Client`.`method`, `Client`.`paymentterms`, `Client`.`signedinvoices`
FROM `clients` AS `Client` WHERE name='some name' AND invoice_id IS
NULL
1054: Unknown column 'invoice_id' in 'where clause'
These conditions are inserted in the first query, only for the Client
table, which of course fails.
I've tried this with adding the table name, no change (1054: Unknown
column 'Invoiceitem.invoice_id' in 'where clause'), and specifying
with the new expects behaviour exactly which models use.
What's the Cake way to add conditions for the associated model??
Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find Conditions (using arrays) , a bug in 1.2?
On Nov 21, 3:23 pm, Adam Royle <[EMAIL PROTECTED]> wrote:
> I agree - it gets pretty convoluted when you add a bit of complexity
> into the conditions.
Where is the complexity in the stated example?
Any nested array that is written on a single line is going to be
'difficult' to read as such it's easier to read if it's written as:
$conditions['OR']['PromoCode.start_date'] = "> $datetime";
$conditions['OR']['PromoCode.promo_code'] = $promo_code;
$conditions['NOT']['PromoCode.active'] = 1; // or
$condiions['PromoCode.active'] = '<> ' . 1;
$count =$this->PromoCode->findCount($conditions);
OR as:
$conditions = array(
'OR' => array(
'PromoCode.start_date' => "> $datetime",
'PromoCode.promo_code' => $promo_code
)
'NOT' => array('PromoCode.active' => 1) // or 'PromoCode.active' =>
'<> ' . 1
);
$count =$this->PromoCode->findCount($conditions);
Both of which are unambiguos (, identical) and easy to read. If you /
do/ write a more complex constraint you'll find it's quite easy to do
so if you adopt on or the other means of writing them.
my 2c, hth,
AD
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find Conditions (using arrays) , a bug in 1.2?
I agree - it gets pretty convoluted when you add a bit of complexity
into the conditions.
However, I haven't yet come up with a better syntax (aside from
writing plain sql of course).
Have you got any suggestions?
Adam
On Nov 21, 3:23 am, zonium <[EMAIL PROTECTED]> wrote:
> I figured it out:
> The statement should be:
> $count
> =$this->PromoCode->findCount(array('or'=>array(array("PromoCode.start_date"
> =>">
>
> $datetime", 'PromoCode.promo_code'=>$promo_code),
> array('PromoCode.active'=>'<>1';
>
> and the result is as expected:
> SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> (((`PromoCode`.`start_date` > '2007-11-20 09:11:23') AND
> (`PromoCode`.`promo_code` = 'freemug')) OR (`PromoCode`.`active` <>
> 1))
>
> But ... Too complicated a statement for a simple query. Don't you
> think?
> Thanks!
>
> On Nov 20, 8:55 am, zonium <[EMAIL PROTECTED]> wrote:
>
> > In fact, my select statement was
>
> > $count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
> > $datetime", 'PromoCode.promo_code'=>$promo_code,
> > "OR"=>array('PromoCode.active'=>'<>1')));
> > and
>
> > Cake created:
> > SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> > `PromoCode`.`start_date` > '2007-11-19 08:50:20' AND
> > `PromoCode`.`promo_code` = 'freemug' AND ((`PromoCode`.`active` <> 1))
>
> > I expected
>
> > SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> > (`PromoCode`.`start_date` > '2007-11-19 08:50:20' AND
> > `PromoCode`.`promo_code` = 'freemug') OR ((`PromoCode`.`active` <> 1))
>
> > Thanks,
>
> > On Nov 19, 8:42 pm, Grant Cox <[EMAIL PROTECTED]> wrote:
>
> > > Put both conditions in the 'or' key:
>
> > > $datetime = date("Y-m-d H:i:s");
> > > $count
> > > =$this->PromoCode->findCount(array("OR"=>array("PromoCode.start_date"
> > > =>"> $datetime",
>
> > > 'PromoCode.active'=>'<>1')));
>
> > > On Nov 20, 11:49 am, zonium <[EMAIL PROTECTED]> wrote:
>
> > > > I have these statements:
>
> > > > $datetime = date("Y-m-d H:i:s");
> > > > $count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
> > > > $datetime", "OR"=>array('PromoCode.active'=>'<>1')));
>
> > > > With debug enabled I saw cake tried to create this query:
>
> > > > SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> > > > `PromoCode`.`start_date` > '2007-11-19 17:38:54' AND
> > > > ((`PromoCode`.`active` <> 1))
>
> > > > According to the official manual (http://manual.cakephp.org/chapter/
> > > > models) I expected
>
> > > > OR (`PromoCode`.`active` <> 1)
> > > > instead of
> > > > AND ((`PromoCode`.`active` <> 1))
>
> > > > Is it a bug or my statement was not correctly written.
>
> > > > Thanks,
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find Conditions (using arrays) , a bug in 1.2?
I figured it out:
The statement should be:
$count =$this->PromoCode-
>findCount(array('or'=>array(array("PromoCode.start_date" =>">
$datetime", 'PromoCode.promo_code'=>$promo_code),
array('PromoCode.active'=>'<>1';
and the result is as expected:
SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
(((`PromoCode`.`start_date` > '2007-11-20 09:11:23') AND
(`PromoCode`.`promo_code` = 'freemug')) OR (`PromoCode`.`active` <>
1))
But ... Too complicated a statement for a simple query. Don't you
think?
Thanks!
On Nov 20, 8:55 am, zonium <[EMAIL PROTECTED]> wrote:
> In fact, my select statement was
>
> $count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
> $datetime", 'PromoCode.promo_code'=>$promo_code,
> "OR"=>array('PromoCode.active'=>'<>1')));
> and
>
> Cake created:
> SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> `PromoCode`.`start_date` > '2007-11-19 08:50:20' AND
> `PromoCode`.`promo_code` = 'freemug' AND ((`PromoCode`.`active` <> 1))
>
> I expected
>
> SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> (`PromoCode`.`start_date` > '2007-11-19 08:50:20' AND
> `PromoCode`.`promo_code` = 'freemug') OR ((`PromoCode`.`active` <> 1))
>
> Thanks,
>
> On Nov 19, 8:42 pm, Grant Cox <[EMAIL PROTECTED]> wrote:
>
> > Put both conditions in the 'or' key:
>
> > $datetime = date("Y-m-d H:i:s");
> > $count
> > =$this->PromoCode->findCount(array("OR"=>array("PromoCode.start_date" =>">
> > $datetime",
>
> > 'PromoCode.active'=>'<>1')));
>
> > On Nov 20, 11:49 am, zonium <[EMAIL PROTECTED]> wrote:
>
> > > I have these statements:
>
> > > $datetime = date("Y-m-d H:i:s");
> > > $count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
> > > $datetime", "OR"=>array('PromoCode.active'=>'<>1')));
>
> > > With debug enabled I saw cake tried to create this query:
>
> > > SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> > > `PromoCode`.`start_date` > '2007-11-19 17:38:54' AND
> > > ((`PromoCode`.`active` <> 1))
>
> > > According to the official manual (http://manual.cakephp.org/chapter/
> > > models) I expected
>
> > > OR (`PromoCode`.`active` <> 1)
> > > instead of
> > > AND ((`PromoCode`.`active` <> 1))
>
> > > Is it a bug or my statement was not correctly written.
>
> > > Thanks,
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find Conditions (using arrays) , a bug in 1.2?
In fact, my select statement was
$count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
$datetime", 'PromoCode.promo_code'=>$promo_code,
"OR"=>array('PromoCode.active'=>'<>1')));
and
Cake created:
SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
`PromoCode`.`start_date` > '2007-11-19 08:50:20' AND
`PromoCode`.`promo_code` = 'freemug' AND ((`PromoCode`.`active` <> 1))
I expected
SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
(`PromoCode`.`start_date` > '2007-11-19 08:50:20' AND
`PromoCode`.`promo_code` = 'freemug') OR ((`PromoCode`.`active` <> 1))
Thanks,
On Nov 19, 8:42 pm, Grant Cox <[EMAIL PROTECTED]> wrote:
> Put both conditions in the 'or' key:
>
> $datetime = date("Y-m-d H:i:s");
> $count =$this->PromoCode->findCount(array("OR"=>array("PromoCode.start_date"
> =>"> $datetime",
>
> 'PromoCode.active'=>'<>1')));
>
> On Nov 20, 11:49 am, zonium <[EMAIL PROTECTED]> wrote:
>
> > I have these statements:
>
> > $datetime = date("Y-m-d H:i:s");
> > $count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
> > $datetime", "OR"=>array('PromoCode.active'=>'<>1')));
>
> > With debug enabled I saw cake tried to create this query:
>
> > SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> > `PromoCode`.`start_date` > '2007-11-19 17:38:54' AND
> > ((`PromoCode`.`active` <> 1))
>
> > According to the official manual (http://manual.cakephp.org/chapter/
> > models) I expected
>
> > OR (`PromoCode`.`active` <> 1)
> > instead of
> > AND ((`PromoCode`.`active` <> 1))
>
> > Is it a bug or my statement was not correctly written.
>
> > Thanks,
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find Conditions (using arrays) , a bug in 1.2?
Put both conditions in the 'or' key:
$datetime = date("Y-m-d H:i:s");
$count =$this->PromoCode-
>findCount(array("OR"=>array("PromoCode.start_date" =>"> $datetime",
'PromoCode.active'=>'<>1')));
On Nov 20, 11:49 am, zonium <[EMAIL PROTECTED]> wrote:
> I have these statements:
>
> $datetime = date("Y-m-d H:i:s");
> $count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
> $datetime", "OR"=>array('PromoCode.active'=>'<>1')));
>
> With debug enabled I saw cake tried to create this query:
>
> SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
> `PromoCode`.`start_date` > '2007-11-19 17:38:54' AND
> ((`PromoCode`.`active` <> 1))
>
> According to the official manual (http://manual.cakephp.org/chapter/
> models) I expected
>
> OR (`PromoCode`.`active` <> 1)
> instead of
> AND ((`PromoCode`.`active` <> 1))
>
> Is it a bug or my statement was not correctly written.
>
> Thanks,
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Complex Find Conditions (using arrays) , a bug in 1.2?
I have these statements:
$datetime = date("Y-m-d H:i:s");
$count =$this->PromoCode->findCount(array("PromoCode.start_date" =>">
$datetime", "OR"=>array('PromoCode.active'=>'<>1')));
With debug enabled I saw cake tried to create this query:
SELECT COUNT(*) AS `count` FROM `promo_codes` AS `PromoCode` WHERE
`PromoCode`.`start_date` > '2007-11-19 17:38:54' AND
((`PromoCode`.`active` <> 1))
According to the official manual (http://manual.cakephp.org/chapter/
models) I expected
OR (`PromoCode`.`active` <> 1)
instead of
AND ((`PromoCode`.`active` <> 1))
Is it a bug or my statement was not correctly written.
Thanks,
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Complex Find Conditions with "sound like" OR soundex()
On Apr 24, 2007, at 1:17 AM, c3k wrote:
>
> in a model, how can I create a condition array that generate an sql
> query like:
>
> SELECT * FROM dbo.Orders
> WHERE SOUNDEX(ShipName) = SOUNDEX('ernest')
$this->Order->find("SOUNDEX(Order.ShipName) = SOUNDEX('ernest')");
>
> OR
>
> SELECT * FROM dbo.Orders
> WHERE ShipName SOUNDS LIKE 'ernest'
$this->Order->find("Order.ShipName SOUNDS LIKE 'ernest'");
(off-the-cuff, ymmv)
-- John
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Complex Find Conditions with "sound like" OR soundex()
in a model, how can I create a condition array that generate an sql
query like:
SELECT * FROM dbo.Orders
WHERE SOUNDEX(ShipName) = SOUNDEX('ernest')
OR
SELECT * FROM dbo.Orders
WHERE ShipName SOUNDS LIKE 'ernest'
???
Is there a way whitout have to use $model->query();
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Question Regarding Complex Find Conditions and HABTM
> To answer my own question, I've found this open Trac ticket which > addresses this issue: https://trac.cakephp.org/ticket/1209 looks like > it's currently slotted for 1.2 release. check out AD7six's post here http://groups.google.com/group/cake-php/browse_thread/thread/f23b1825050ad543/014092749592de70?lnk=gst&q=habtm+search hth jon -- jon bennett t: +44 (0) 1225 341 039 w: http://www.jben.net/ iChat (AIM): jbendotnet Skype: jon-bennett --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Question Regarding Complex Find Conditions and HABTM
On Mar 27, 10:50 am, "space_boy" <[EMAIL PROTECTED]> wrote:
> In the 'Complex Find Conditions (using arrays)' section of the Models
> chapter in the manual (http://manual.cakephp.org/chapter/models), it
> is suggested that you can search multiple models using a conditions
> array such as:
>
> array
> ("Author.name" => "Bob", "or" => array
> (
> "Post.title" => "LIKE %magic%",
> "Post.created" => "> " . date('Y-m-d', strtotime("-2 weeks")
> )
> )
>
> which would find all the posts that contain a certain keyword or that
> were created in the past two weeks, but were written by Bob.
>
> I am trying to apply this example to two models that are associated by
> a HABTM relationship (i.e. Post and Tag), so that I can search for
> posts that were created after a certain date, that are also tagged
> with some keyword.
>
> i.e. my conditions array looks like:
> array
> (
> "Tag.name" = "LIKE %magic%",
> "Post.created" => "> " . date('Y-m-d', strtotime("-2 weeks")
> )
>
> However, when I execute $this->Post->findAll() using this conditions
> array from my posts_controller, I receive an error saying 'SQL Error
> in model Post: 1054: Unknown column 'Tag.name' in 'where clause'',
> after examining the SQL that is generated by Cake it is clear that the
> tags table isn't being referenced at all by the query.
>
> Have I missed something here? Are these kinds of complex find
> conditions expected to work for HABTM models?
>
> Thanks.
To answer my own question, I've found this open Trac ticket which
addresses this issue: https://trac.cakephp.org/ticket/1209 looks like
it's currently slotted for 1.2 release.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Question Regarding Complex Find Conditions and HABTM
In the 'Complex Find Conditions (using arrays)' section of the Models
chapter in the manual (http://manual.cakephp.org/chapter/models), it
is suggested that you can search multiple models using a conditions
array such as:
array
("Author.name" => "Bob", "or" => array
(
"Post.title" => "LIKE %magic%",
"Post.created" => "> " . date('Y-m-d', strtotime("-2 weeks")
)
)
which would find all the posts that contain a certain keyword or that
were created in the past two weeks, but were written by Bob.
I am trying to apply this example to two models that are associated by
a HABTM relationship (i.e. Post and Tag), so that I can search for
posts that were created after a certain date, that are also tagged
with some keyword.
i.e. my conditions array looks like:
array
(
"Tag.name" = "LIKE %magic%",
"Post.created" => "> " . date('Y-m-d', strtotime("-2 weeks")
)
However, when I execute $this->Post->findAll() using this conditions
array from my posts_controller, I receive an error saying 'SQL Error
in model Post: 1054: Unknown column 'Tag.name' in 'where clause'',
after examining the SQL that is generated by Cake it is clear that the
tags table isn't being referenced at all by the query.
Have I missed something here? Are these kinds of complex find
conditions expected to work for HABTM models?
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
