Re: CakePHP 3.0 orm query question
$query = $table-find()
$query-select(['distance' = $query-newExpr($rawSQL)])-having(['distance
=' = $distance])
On Monday, August 25, 2014 2:59:01 AM UTC+2, Andras Kende wrote:
Hello,
Im trying to convert this geolocator query from 2.5 to 3.0 orm but no luck
so far.
CakePHP 2.5, this works fine:
$results = $this-find('all', array(
'limit' = 200,
'order' = array(
'distance' = 'ASC',
'name' = 'ASC',
),
'recursive' = -1,
'conditions' = HAVING distance $distance,
‘fields' = *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) *
(`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng`
- {$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS `distance`
));
generated sql statement:
SELECT *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - 34.0522) * (`lat` -
34.0522) + COS(`lat` / 57.29578) * COS(34.0522 / 57.29578) * (`lng` -
-118.244) * (`lng` - -118.244)) / 180) * 1, 5) AS `distance` FROM
`hockeypickup`.`locations` AS `Location` HAVING distance 100 ORDER BY
`distance` ASC, `name` ASC LIMIT 200
on CakePHP 3.0 this is far as I got :
$query = $this-find('all', array(
'limit' = 200,
'order' = array(
// 'distance' = 'ASC',
'name' = 'ASC',
),
'conditions' = $conditions,
'select' = *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) *
(`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng`
- {$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS Locations.distance,
// 'having' = `distance` = $distance,
));
distance is not getting recognized so far…
Thank you,
Andras
--
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/d/optout.
Re: CakePHP 3.0 orm query question
Hi Jose,
I got it working as :
$query = $this-find('all', array(
'limit' = 200,
'order' = array(
'distance' = 'ASC',
'name' = 'ASC',
),
'conditions' = $conditions,
'fields' = [name, slug, lat, lng, address,
city, state, postal_code, phone, distance = TRUNCATE((3958 *
3.1415926 * SQRT((`lat` - {$lat}) * (`lat` - {$lat}) + COS(`lat` / 57.29578) *
COS({$lat} / 57.29578) * (`lng` - {$lng}) * (`lng` - {$lng})) / 180) * {$unit},
5)],
'having' = distance = $distance,
));
Just needed to use associative array, in the fields..
Thanks,
Andras
On Aug 26, 2014, at 1:10 AM, José Lorenzo jose@gmail.com wrote:
$query = $table-find()
$query-select(['distance' = $query-newExpr($rawSQL)])-having(['distance
=' = $distance])
On Monday, August 25, 2014 2:59:01 AM UTC+2, Andras Kende wrote:
Hello,
Im trying to convert this geolocator query from 2.5 to 3.0 orm but no luck so
far.
CakePHP 2.5, this works fine:
$results = $this-find('all', array(
'limit' = 200,
'order' = array(
'distance' = 'ASC',
'name' = 'ASC',
),
'recursive' = -1,
'conditions' = HAVING distance $distance,
'fields' = *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) *
(`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng` -
{$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS `distance`
));
generated sql statement:
SELECT *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - 34.0522) * (`lat` -
34.0522) + COS(`lat` / 57.29578) * COS(34.0522 / 57.29578) * (`lng` -
-118.244) * (`lng` - -118.244)) / 180) * 1, 5) AS `distance` FROM
`hockeypickup`.`locations` AS `Location` HAVING distance 100 ORDER BY
`distance` ASC, `name` ASC LIMIT 200
on CakePHP 3.0 this is far as I got :
$query = $this-find('all', array(
'limit' = 200,
'order' = array(
// 'distance' = 'ASC',
'name' = 'ASC',
),
'conditions' = $conditions,
'select' = *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) *
(`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng` -
{$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS Locations.distance,
// 'having' = `distance` = $distance,
));
distance is not getting recognized so far...
Thank you,
Andras
--
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/d/optout.
--
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/d/optout.
CakePHP 3.0 orm query question
Hello,
Im trying to convert this geolocator query from 2.5 to 3.0 orm but no luck so
far.
CakePHP 2.5, this works fine:
$results = $this-find('all', array(
'limit' = 200,
'order' = array(
'distance' = 'ASC',
'name' = 'ASC',
),
'recursive' = -1,
'conditions' = HAVING distance $distance,
'fields' = *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) *
(`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng` -
{$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS `distance`
));
generated sql statement:
SELECT *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - 34.0522) * (`lat` -
34.0522) + COS(`lat` / 57.29578) * COS(34.0522 / 57.29578) * (`lng` - -118.244)
* (`lng` - -118.244)) / 180) * 1, 5) AS `distance` FROM
`hockeypickup`.`locations` AS `Location` HAVING distance 100 ORDER BY
`distance` ASC, `name` ASC LIMIT 200
on CakePHP 3.0 this is far as I got :
$query = $this-find('all', array(
'limit' = 200,
'order' = array(
// 'distance' = 'ASC',
'name' = 'ASC',
),
'conditions' = $conditions,
'select' = *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) *
(`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng` -
{$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS Locations.distance,
// 'having' = `distance` = $distance,
));
distance is not getting recognized so far...
Thank you,
Andras
--
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/d/optout.
Re: Random query question
If you are using MySQL as your database backend, read http://www.desilva.biz/mysql/random.html (found by Googling mysql random row). If you are not using MySQL, check if your database has a similar function. On May 19, 5:56 am, Sarpidon epap...@gmail.com wrote: Hi everybody. I have a companies table and those companies have promotions. Each company can have up to 10 promotions but only 2 can be displayed from each company at one time. I've been trying to figure out how to generate a single query that will select only 2 promotions randomly from each company. Is that even possible? see screenshot attached --http://screencast.com/t/dB2sqcEns so basically randomly select only 2 promotions with the same company_id... possible? how? Thank you for your time. -- 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: Random query question
Thank you guys. Going through your suggestions now. -- 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
Random query question
Hi everybody. I have a companies table and those companies have promotions. Each company can have up to 10 promotions but only 2 can be displayed from each company at one time. I've been trying to figure out how to generate a single query that will select only 2 promotions randomly from each company. Is that even possible? see screenshot attached -- http://screencast.com/t/dB2sqcEns so basically randomly select only 2 promotions with the same company_id... possible? how? Thank you for your time. -- 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: Random query question
one simple way would be to fetch all the records, then generate two non-equal random numbers in the range of found records, then pluck those two out of the result array. The returned records are numbered 0-10 in that array so it will be very simple to do! http://us.php.net/manual/en/function.rand.php Regards, Don On May 19, 5:56 am, Sarpidon epap...@gmail.com wrote: Hi everybody. I have a companies table and those companies have promotions. Each company can have up to 10 promotions but only 2 can be displayed from each company at one time. I've been trying to figure out how to generate a single query that will select only 2 promotions randomly from each company. Is that even possible? see screenshot attached --http://screencast.com/t/dB2sqcEns so basically randomly select only 2 promotions with the same company_id... possible? how? Thank you for your time. -- 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
habtm query question
Hi all,
i'm trying to get a habtm query to work..
I've got the following models
User (habtm Group)
Group (habtm Group, hasmany Rule)
Rule (belongs to Rule)
Trying to get a list of rules for a given User - conditioning the query on
Rule with User.id = $this-auth-user('id')
but the user group is not automatically joined to the Rule model.
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: Query question(s)
I am using the DB to store the search criteria and the result. Not that the result creates more information, it creates only relationships between the stored search criteria and the existing records in other tables. The search is currently only based on one subject, either Article, Blog, Ebook, or Author, but can easily be expanded to search through all of the subjects. The Enquiry model stores the search criteria, thus getting an ID. The model has a HABTM relationship with the above subject models. The Enquiry model passes on the search criteria with the ID to the relevant subject model, which then executes the search within its records. The subject model passes on the resulting subject ids to the HABTM model, which stores the result. The Enquiry model ID is then passed back to the controller, which uses paginate to show the result. A short version of the ER diagram can be seen here: http://jaa.myftp.org:28880/examples/enquiry The way I have implemented this, also allows me to keep a history of search within the session. The history is also presented to the user, thus allowing the user to go back to previous results. Hope the above explanation is usefull to you :) Enjoy, John On Jun 10, 10:04 am, Ed Propsner crotchf...@gmail.com wrote: [snip part for Calvin] @John: Are you using the db to cache your search results on the server side? I learned some hard lessons in the past and need to cut down the load wherever I can. Is there any specific advantages to setting up your search the way you have it? - Ed 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: Query question(s)
@John: I think I'm able to wrap my head around the concept. My project is extremely search heavy and if I'm following you correctly I can see a few distinct advantages to your system. What I like the most is being able to keep a history of searches within the session. I see a catastrophe just waiting to happen 8-) but I'd like to have a go at it on a smaller scale with one of my simpler searches and expand from there if all goes well. I don't want to bite off more than I can chew so for now I'll tackle one experiment at a time and thanks to Calvin this bitwise thing is working out rather well and I'm close to wrapping it up. If it's okay with you I might have a few other questions to throw at ya' when I'm ready to start (I'll start a new post when the time comes). - Ed On Thu, Jun 10, 2010 at 3:34 AM, John Andersen j.andersen...@gmail.comwrote: I am using the DB to store the search criteria and the result. Not that the result creates more information, it creates only relationships between the stored search criteria and the existing records in other tables. The search is currently only based on one subject, either Article, Blog, Ebook, or Author, but can easily be expanded to search through all of the subjects. The Enquiry model stores the search criteria, thus getting an ID. The model has a HABTM relationship with the above subject models. The Enquiry model passes on the search criteria with the ID to the relevant subject model, which then executes the search within its records. The subject model passes on the resulting subject ids to the HABTM model, which stores the result. The Enquiry model ID is then passed back to the controller, which uses paginate to show the result. A short version of the ER diagram can be seen here: http://jaa.myftp.org:28880/examples/enquiry The way I have implemented this, also allows me to keep a history of search within the session. The history is also presented to the user, thus allowing the user to go back to previous results. Hope the above explanation is usefull to you :) Enjoy, John On Jun 10, 10:04 am, Ed Propsner crotchf...@gmail.com wrote: [snip part for Calvin] @John: Are you using the db to cache your search results on the server side? I learned some hard lessons in the past and need to cut down the load wherever I can. Is there any specific advantages to setting up your search the way you have it? - Ed 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.comcake-php%2bunsubscr...@googlegroups.comFor 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
Re: Query question(s)
Manipulating bit fields can be confusing at times (e.g. on Monday it took me a while to realize the query in my original post didn't do what I thought it would), and there's usually more than one way to do it. But, basically, the way I approached it was to group all of the OR conditions together and treat them collectively as another AND condition (so the search has to match all of the ANDs, and at least one of the ORs). That may not be what you want for your search, but it demonstrates how you'd evaluate AND OR conditions. So taking your example of a record that has the bitfield value of 3, if you want to do a purely OR search for Videos(1) or Photos(4) or Text(8), you'd only use the first half the query I wrote: Model.type $orOptions 0 So the database engine would evaluate: 3 13 0 The bit fields in big-endian notation would be: 0011 (3) 1101 (13) So the result is 0001 because both the search params and the record field have the bit for Video set. And since 1 0, that record would be returned as a match. To perform the same search using AND, you'd simply check to see if 3 13 = 13, which it obviously does not. But a record that has a value of 13 for the type field would evaluate to: 1101 1101 = 1101 So such a record would be return as a result. On Jun 8, 10:36 am, Ed Propsner crotchf...@gmail.com wrote: @Calvin: I do like the idea of using array_sum() and storing the options as an INT, I've taken this approach in the past with a different app (once) and it worked out just fine. In this case let's say you have a value of 3 stored in the db representing 2 options ... '1' = 'video, '2' = 'audio. A search on that column would only hit on an exact match, no? I only need the stored value and the search criteria to have one of their values in common, not all. In this case if a user searched just '1' = 'video' it should return the column containing the value of 3 because both the search and the stored data have '1' = 'video' in common. I won't stress the checkbox search too much at this time. I did get it working without writing any kind of custom query. The query itself is to the point but checking the values of the array and setting up the $orOptions to account for each possibility was a tad lengthy. About the checkboxes ... well ... I was submitting them as separate input fields instead of one 'multiple' ... don't ask! 8-). The query giving me problems at this time is the advanced search. It's not much a problem more than I'm unsure of how to approach it. I want to toy around with it for a bit then I'll let know what issues I'm having. @John: [quote] When the user submits the search, the first thing I do, is to save the search parameters in the database (Enquiry model), so as to get an ID.. [/quote] Errr ... Something came up and I have to leave the house but I have a few questions for ya' ... I'll get back you when I get back in. On Tue, Jun 8, 2010 at 3:38 AM, John Andersen j.andersen...@gmail.comwrote: This may not be relevant to your issue, but maybe to your solution. I will try to explain how I did my search functionality. In my application, the user will search for a specific object (Article, Author, Blog, etc.), not a combination of these. The search form provides the following entries: Words [text] - one or more words Word criteria [radiogroup] - : 1) must contain all words. 2) must contain at least one word. 3) must contain exact phrase. Search criteria [checkbox] - : 1) In title (default). 2) In summary. 3) In body. Category(ies) [checkbox] - all categories. When the user submits the search, the first thing I do, is to save the search parameters in the database (Enquiry model), so as to get an ID. The search parameters are then passed on to the responsible model (Article, Author, Blog, etc), which then performs the actual search. The resulting rows (ids) are passed back to the Enquiry model, which saves the result (creates relationships between Enquiry model and responsible model). I then uses the ID to paginate the result, when presenting it to the user. Building the search query: 1) I split into single words from the Words entry. 2) Based on the Word criteria, I prepare the respective AND, OR, or phrase condition(s) for all possible Search criteria. That is one for Title, one for Summary and one for Body. 3) Based on the Search criteria, I include the respective conditions for Title, Summary and/or Body. The resulting find conditions looks like this (Words equal a b c, Word criteria must contain all words, Search critera In title, In summary, In body: [code] Array ( [OR] = Array ( [0] = Array ( [AND] = Array ( [0] = Array ( [LOWER(Article.title) LIKE] = %a% ) [1] = Array
Re: Query question(s)
@Calvin: I have very little experience with bitwise operations but if the concept was totally escaping me and you saw this don't ever be hesitant to tell me I'm being a dummy. I have no reservations with diving into a tutorial and picking up on something new. I never expect the answer but will always appreciate you pointing me in the right direction where i can play around, experiment, and hopefully come up with a somewhat feasible solution on my own. I found a tutorial on bitwise operations and realized I was WAY far off from anything you were suggesting. You were right on the mark! (you the man!) I'll stop being cryptic and let you know I'm working on a makeover for a social networking site for singles. I'll never claim to know everything (even common sense things) I should know to make this all work but what I do appreciate is people like you taking the time to point me in the right direction (and don''t forget John Anderson or Jeremy Burns) they have been instrumental in keeping me on a direct path with Cake conventions. (I still have a few questions for you John but I'll get back to you) . Anyhow there was a time when I thought I was king $hit on turd island with conventional PHP but Cake has totally knocked the wind out of my sails but I'm totally addicted and can't see doing it any other way at this point (thank you guys for all your help ). Just displaying the data using tinyint cut my code down drastically. I haven't implemented the search yet and while I have a busy night in front of me (Stanley Cup finals and all) I'll start the search tomorrow and let you know how I make out. I'm not expecting any problems. Where have bitwise operations been all this time? I'll throw all my code at you sometime tomorrow. 8-) Cya Then - Ed So ... being a complete idiot on bitwise operations I took the tutorial and O' my God, Thanks Calvin! . On Wed, Jun 9, 2010 at 4:20 PM, calvin cal...@rottenrecords.com wrote: Manipulating bit fields can be confusing at times (e.g. on Monday it took me a while to realize the query in my original post didn't do what I thought it would), and there's usually more than one way to do it. But, basically, the way I approached it was to group all of the OR conditions together and treat them collectively as another AND condition (so the search has to match all of the ANDs, and at least one of the ORs). That may not be what you want for your search, but it demonstrates how you'd evaluate AND OR conditions. So taking your example of a record that has the bitfield value of 3, if you want to do a purely OR search for Videos(1) or Photos(4) or Text(8), you'd only use the first half the query I wrote: Model.type $orOptions 0 So the database engine would evaluate: 3 13 0 The bit fields in big-endian notation would be: 0011 (3) 1101 (13) So the result is 0001 because both the search params and the record field have the bit for Video set. And since 1 0, that record would be returned as a match. To perform the same search using AND, you'd simply check to see if 3 13 = 13, which it obviously does not. But a record that has a value of 13 for the type field would evaluate to: 1101 1101 = 1101 So such a record would be return as a result. On Jun 8, 10:36 am, Ed Propsner crotchf...@gmail.com wrote: @Calvin: I do like the idea of using array_sum() and storing the options as an INT, I've taken this approach in the past with a different app (once) and it worked out just fine. In this case let's say you have a value of 3 stored in the db representing 2 options ... '1' = 'video, '2' = 'audio. A search on that column would only hit on an exact match, no? I only need the stored value and the search criteria to have one of their values in common, not all. In this case if a user searched just '1' = 'video' it should return the column containing the value of 3 because both the search and the stored data have '1' = 'video' in common. I won't stress the checkbox search too much at this time. I did get it working without writing any kind of custom query. The query itself is to the point but checking the values of the array and setting up the $orOptions to account for each possibility was a tad lengthy. About the checkboxes ... well ... I was submitting them as separate input fields instead of one 'multiple' ... don't ask! 8-). The query giving me problems at this time is the advanced search. It's not much a problem more than I'm unsure of how to approach it. I want to toy around with it for a bit then I'll let know what issues I'm having. @John: [quote] When the user submits the search, the first thing I do, is to save the search parameters in the database (Enquiry model), so as to get an ID.. [/quote] Errr ... Something came up and I have to leave the house but I have a few questions for ya' ... I'll get back you when I get back in. On Tue, Jun 8, 2010 at 3:38 AM, John
Re: Query question(s)
This may not be relevant to your issue, but maybe to your solution. I will try to explain how I did my search functionality. In my application, the user will search for a specific object (Article, Author, Blog, etc.), not a combination of these. The search form provides the following entries: Words [text] - one or more words Word criteria [radiogroup] - : 1) must contain all words. 2) must contain at least one word. 3) must contain exact phrase. Search criteria [checkbox] - : 1) In title (default). 2) In summary. 3) In body. Category(ies) [checkbox] - all categories. When the user submits the search, the first thing I do, is to save the search parameters in the database (Enquiry model), so as to get an ID. The search parameters are then passed on to the responsible model (Article, Author, Blog, etc), which then performs the actual search. The resulting rows (ids) are passed back to the Enquiry model, which saves the result (creates relationships between Enquiry model and responsible model). I then uses the ID to paginate the result, when presenting it to the user. Building the search query: 1) I split into single words from the Words entry. 2) Based on the Word criteria, I prepare the respective AND, OR, or phrase condition(s) for all possible Search criteria. That is one for Title, one for Summary and one for Body. 3) Based on the Search criteria, I include the respective conditions for Title, Summary and/or Body. The resulting find conditions looks like this (Words equal a b c, Word criteria must contain all words, Search critera In title, In summary, In body: [code] Array ( [OR] = Array ( [0] = Array ( [AND] = Array ( [0] = Array ( [LOWER(Article.title) LIKE] = %a% ) [1] = Array ( [LOWER(Article.title) LIKE] = %b% ) [2] = Array ( [LOWER(Article.title) LIKE] = %c% ) ) ) [1] = Array ( [AND] = Array ( [0] = Array ( [LOWER(Article.summary) LIKE] = %a% ) [1] = Array ( [LOWER(Article.summary) LIKE] = %b% ) [2] = Array ( [LOWER(Article.summary) LIKE] = %c% ) ) ) [2] = Array ( [AND] = Array ( [0] = Array ( [LOWER(Article.content) LIKE] = %a% ) [1] = Array ( [LOWER(Article.content) LIKE] = %b% ) [2] = Array ( [LOWER(Article.content) LIKE] = %c% ) ) ) ) [Article.state] = 2 ) [/code] If you have questions to the above, or just curious about other things, feel free to ask :) Hope you will get your search up and running! Enjoy, John [snip] 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: Query question(s)
@Calvin: I do like the idea of using array_sum() and storing the options as an INT, I've taken this approach in the past with a different app (once) and it worked out just fine. In this case let's say you have a value of 3 stored in the db representing 2 options ... '1' = 'video, '2' = 'audio. A search on that column would only hit on an exact match, no? I only need the stored value and the search criteria to have one of their values in common, not all. In this case if a user searched just '1' = 'video' it should return the column containing the value of 3 because both the search and the stored data have '1' = 'video' in common. I won't stress the checkbox search too much at this time. I did get it working without writing any kind of custom query. The query itself is to the point but checking the values of the array and setting up the $orOptions to account for each possibility was a tad lengthy. About the checkboxes ... well ... I was submitting them as separate input fields instead of one 'multiple' ... don't ask! 8-). The query giving me problems at this time is the advanced search. It's not much a problem more than I'm unsure of how to approach it. I want to toy around with it for a bit then I'll let know what issues I'm having. @John: [quote] When the user submits the search, the first thing I do, is to save the search parameters in the database (Enquiry model), so as to get an ID.. [/quote] Errr ... Something came up and I have to leave the house but I have a few questions for ya' ... I'll get back you when I get back in. On Tue, Jun 8, 2010 at 3:38 AM, John Andersen j.andersen...@gmail.comwrote: This may not be relevant to your issue, but maybe to your solution. I will try to explain how I did my search functionality. In my application, the user will search for a specific object (Article, Author, Blog, etc.), not a combination of these. The search form provides the following entries: Words [text] - one or more words Word criteria [radiogroup] - : 1) must contain all words. 2) must contain at least one word. 3) must contain exact phrase. Search criteria [checkbox] - : 1) In title (default). 2) In summary. 3) In body. Category(ies) [checkbox] - all categories. When the user submits the search, the first thing I do, is to save the search parameters in the database (Enquiry model), so as to get an ID. The search parameters are then passed on to the responsible model (Article, Author, Blog, etc), which then performs the actual search. The resulting rows (ids) are passed back to the Enquiry model, which saves the result (creates relationships between Enquiry model and responsible model). I then uses the ID to paginate the result, when presenting it to the user. Building the search query: 1) I split into single words from the Words entry. 2) Based on the Word criteria, I prepare the respective AND, OR, or phrase condition(s) for all possible Search criteria. That is one for Title, one for Summary and one for Body. 3) Based on the Search criteria, I include the respective conditions for Title, Summary and/or Body. The resulting find conditions looks like this (Words equal a b c, Word criteria must contain all words, Search critera In title, In summary, In body: [code] Array ( [OR] = Array ( [0] = Array ( [AND] = Array ( [0] = Array ( [LOWER(Article.title) LIKE] = %a% ) [1] = Array ( [LOWER(Article.title) LIKE] = %b% ) [2] = Array ( [LOWER(Article.title) LIKE] = %c% ) ) ) [1] = Array ( [AND] = Array ( [0] = Array ( [LOWER(Article.summary) LIKE] = %a% ) [1] = Array ( [LOWER(Article.summary) LIKE] = %b% ) [2] = Array ( [LOWER(Article.summary) LIKE] = %c% ) ) ) [2] = Array ( [AND] = Array ( [0] = Array ( [LOWER(Article.content) LIKE] = %a% ) [1] = Array ( [LOWER(Article.content) LIKE] = %b% ) [2] = Array ( [LOWER(Article.content) LIKE] = %c% ) ) )
Re: Query question(s)
Based on what you have posted I would ask whether a redesign of the database would be a better idea! I refer to the issue you have created yourself, by having one column contain more than one value at the same time: [quote] ... The problem I'm running into is that $array can contain any number and combination of values (A, C, E), (A, B), (E) ... etc. And 'SomeCol.contents' can also contain any number and combination of values stored as a comma separated string. (it was originally stored as a serialized array). ... [/quote] Can you explain why you have not separated/normalized the SomeCol.contents? And do you have other such columns in your database? Enjoy, John On Jun 6, 11:24 pm, Ed Propsner crotchf...@gmail.com wrote: I found a usable solution, a bit exhaustive and long-winded perhaps, but usable nonetheless. I still need to put together a dynamic query and I'm finding myself avoiding having to do it at this time. I need to build a query dynamically based on what elements a user chooses from a form. There could be 20 choices or there could be 2. Every solution I'm coming up with is ridiculously excessive for something that should be so simple. I'm sure the answer is there, I'm just not familiar enough with Cake at this point to see it. - Ed On Sat, Jun 5, 2010 at 4:36 PM, Ed Propsner crotchf...@gmail.com wrote: Perhaps I'm over-complicating this but I'm still having some problems with building my query. I'm looking to do something like: $array = array (A, B, C, D, E); $list = implode( ',' , $array); 'conditions' = array( 'SomeCol.contents' = array($list) ) The problem I'm running into is that $array can contain any number and combination of values (A, C, E), (A, B), (E) ... etc. And 'SomeCol.contents' can also contain any number and combination of values stored as a comma separated string. (it was originally stored as a serialized array). I need the query to return a result if 'SomeCol.contents' and $array have any one of their values in common. I was thinking along the lines of : 'conditions' = array( 'SomeCol.contents LIKE' = '%'.$array[0].'%' OR 'SomeCol.contents LIKE' = '%'.$array[1].'%' etc, etc // this way should include any record in the result that shares a value with $array but I assume I would run into an issue with an undefined index by not knowing how many values $array contains. (I do know it has the potential to store a max of 7 values) I had this all worked out with conventional PHP but now I need to use Paginate and containable with this query as well as a bunch of other conditions (with a set value) and I'm confusing myself more than anything else which isn't a real stretch for me to begin with 8-). Any suggestions are appreciated. Thanks. - Ed On Fri, Jun 4, 2010 at 11:54 PM, Ed Propsner crotchf...@gmail.com wrote: Thanks Calvin, point well taken. I'll just stick with GET like I always have for searches. I tweaked the routing and got things cleaned up a bit so I guess I'm okay with it. I'm not quite sure what I was expecting in the first place? 8-) The search uses a lot of checkboxes and results in something like http://mysite.com/search/results/value1=0value1=1value2=0value2=1. I can't seem to clean it up any more when submitting with GET, or am I still missing something? Anyhow, at least it's working the way that I want it to. I'm still having some issues with query so I'll quit fussing with the url for now. - Ed On Fri, Jun 4, 2010 at 10:15 PM, calvin cal...@rottenrecords.com wrote: POST requests are generally not cached, but you can force it to be cached using the Cache-Control and Expires headers. However, I've never tried this so I don't know if the browser will still show the form submission dialog (it may need to resend the form data to check to see if the document has changed since last requested). It probably won't, but I would still strongly advise against this. A search request is generally an idempotent operation (multiple requests do not have any side-effects), which is precisely what the GET request is designed for. There's no reason to use POST in this case. Cake has a perfectly good way of hiding ugly URL-encoding using its REST-style routing patterns, e.g.: http://yoursite.com/pages/search/foo/bar/foo2/bar2/... You can also use named parameters to make the search URL more readable, e.g.: http://yoursite.com/products/search/q:paegan/artist:acid+bath/categor... Unless you have a ton of search options, I see no reason to use POST. And even if you use POST and generate shorter/cleaner URL--what's the point? What will that clean URL achieve? The user can't bookmark it. They can't link a friend to it. They can't do anything
Re: Query question(s)
I'm not sure what you ought to do about the custom query (you may just
have to build it the hard way or do as Ed suggest and redesign the
DB), but regarding the URL, there are a few ways to go about it.
Firstly, I should point out that the URL you wrote doesn't look right.
Usually, with something like checkboxes, you want to transmit the data
as an array:
http://mysite.com/search/results/value1[]=0value1[]=1value2[]=0value2[]=1
I think that's how URL-encoded array data usually looks.
As to how to get from the standard http URL-encoding to Cake's REST-
style encoding, there are a few ways to go about it. The most obvious
way is to use JavaScript to build the Cake URL. That would be easy to
do, but it's not a good approach from an accessibility standpoint.
Instead, I would recommend accepting the ugly url and then, in the
controller, translate that into a Cake URL and redirect the user
there.
For example, you'd start with a standard search form with an action
url of:
http://mysite.com/search
which produces URLs like:
http://mysite.com/search?value1[]=0value1[]=1value2[]=0value2[]=1...
Then the action that http://mysite.com/search routes to will take
those GET variables and convert them into:
http://mysite.com/search/result/value1:0,1/value2:0,1/...
I'm not sure if Cake has its own way of serializing arrays for URLs,
but that would seem like the most readable way to do it. You might
also try:
/search/result/value1[]:0/value1[]:1/value2[]:0/value2[]:1
or use PHP's serialize() function, which would return something like:
/search/result/value1:a:2:{i:0;i:0;i:1;i:1;}/value2:a:2:{i:0;i:0;i:1;i:
1;}
--which is not very pretty.
On Jun 4, 8:54 pm, Ed Propsner crotchf...@gmail.com wrote:
Thanks Calvin, point well taken. I'll just stick with GET like I always have
for searches.
I tweaked the routing and got things cleaned up a bit so I guess I'm okay
with it.
I'm not quite sure what I was expecting in the first place? 8-)
The search uses a lot of checkboxes and results in something
likehttp://mysite.com/search/results/value1=0value1=1value2=0value2=1.
I can't seem to clean it up any more when submitting with GET, or am I still
missing something?
Anyhow, at least it's working the way that I want it to. I'm still having
some issues with query so I'll quit fussing with the url for now.
- Ed
On Fri, Jun 4, 2010 at 10:15 PM, calvin cal...@rottenrecords.com wrote:
POST requests are generally not cached, but you can force it to be
cached using the Cache-Control and Expires headers. However, I've
never tried this so I don't know if the browser will still show the
form submission dialog (it may need to resend the form data to check
to see if the document has changed since last requested). It probably
won't, but I would still strongly advise against this.
A search request is generally an idempotent operation (multiple
requests do not have any side-effects), which is precisely what the
GET request is designed for. There's no reason to use POST in this
case. Cake has a perfectly good way of hiding ugly URL-encoding using
its REST-style routing patterns, e.g.:
http://yoursite.com/pages/search/foo/bar/foo2/bar2/...
You can also use named parameters to make the search URL more
readable, e.g.:
http://yoursite.com/products/search/q:paegan/artist:acid+bath/categor...
Unless you have a ton of search options, I see no reason to use POST.
And even if you use POST and generate shorter/cleaner URL--what's the
point? What will that clean URL achieve? The user can't bookmark it.
They can't link a friend to it. They can't do anything with it.
A happy compromise would be to do what a lot of forums do, and cache
each search server-side. Then when the user performs a search (with
either POST or GET), they get redirected to the cached search result,
which might be something like:
http://yoursite.com/search/paegan+terrorism+tactics/f83e3a4b389c6b
That will decrease your server load, allow you to use a POST form, and
still allow the user to bookmark/link the search results (at least for
a time).
On Jun 4, 10:40 am, Ed Propsner crotchf...@gmail.com wrote:
I was checking out the book on complex queries and not really finding
what
I'm looking for.
I'm trying to create a query that covers both a basic and advanced
search.
The form may be submitting all or just some of the options available on
the
page depending on what the user chooses.
Once the form has been submitted clicking any one of the query results
would
navigate the user away from the 'results page'.
To avoid getting hit with the form submission dialogue when the users
clicks the back button to return to the search results page
and to cover the 'conditional query' ... I used to use a combo of $_GET
and
if(isset.
ie:
$query= SELECT something FROM somewhere WHERE etc. etc. etc. AND ;
if (isset($_GET['some_value']) $_GET['some_value'] != ''
Re: Query question(s)
If all of your search options are OR conditions, then you could theoretically do something like this: SELECT ... WHERE contents REGEXP '(opt1|opt2|opt3|opt4|opt5|opt6)'; http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp On Jun 6, 1:24 pm, Ed Propsner crotchf...@gmail.com wrote: I found a usable solution, a bit exhaustive and long-winded perhaps, but usable nonetheless. I still need to put together a dynamic query and I'm finding myself avoiding having to do it at this time. I need to build a query dynamically based on what elements a user chooses from a form. There could be 20 choices or there could be 2. Every solution I'm coming up with is ridiculously excessive for something that should be so simple. I'm sure the answer is there, I'm just not familiar enough with Cake at this point to see it. - Ed On Sat, Jun 5, 2010 at 4:36 PM, Ed Propsner crotchf...@gmail.com wrote: Perhaps I'm over-complicating this but I'm still having some problems with building my query. I'm looking to do something like: $array = array (A, B, C, D, E); $list = implode( ',' , $array); 'conditions' = array( 'SomeCol.contents' = array($list) ) The problem I'm running into is that $array can contain any number and combination of values (A, C, E), (A, B), (E) ... etc. And 'SomeCol.contents' can also contain any number and combination of values stored as a comma separated string. (it was originally stored as a serialized array). I need the query to return a result if 'SomeCol.contents' and $array have any one of their values in common. I was thinking along the lines of : 'conditions' = array( 'SomeCol.contents LIKE' = '%'.$array[0].'%' OR 'SomeCol.contents LIKE' = '%'.$array[1].'%' etc, etc // this way should include any record in the result that shares a value with $array but I assume I would run into an issue with an undefined index by not knowing how many values $array contains. (I do know it has the potential to store a max of 7 values) I had this all worked out with conventional PHP but now I need to use Paginate and containable with this query as well as a bunch of other conditions (with a set value) and I'm confusing myself more than anything else which isn't a real stretch for me to begin with 8-). Any suggestions are appreciated. Thanks. - Ed On Fri, Jun 4, 2010 at 11:54 PM, Ed Propsner crotchf...@gmail.com wrote: Thanks Calvin, point well taken. I'll just stick with GET like I always have for searches. I tweaked the routing and got things cleaned up a bit so I guess I'm okay with it. I'm not quite sure what I was expecting in the first place? 8-) The search uses a lot of checkboxes and results in something like http://mysite.com/search/results/value1=0value1=1value2=0value2=1. I can't seem to clean it up any more when submitting with GET, or am I still missing something? Anyhow, at least it's working the way that I want it to. I'm still having some issues with query so I'll quit fussing with the url for now. - Ed On Fri, Jun 4, 2010 at 10:15 PM, calvin cal...@rottenrecords.com wrote: POST requests are generally not cached, but you can force it to be cached using the Cache-Control and Expires headers. However, I've never tried this so I don't know if the browser will still show the form submission dialog (it may need to resend the form data to check to see if the document has changed since last requested). It probably won't, but I would still strongly advise against this. A search request is generally an idempotent operation (multiple requests do not have any side-effects), which is precisely what the GET request is designed for. There's no reason to use POST in this case. Cake has a perfectly good way of hiding ugly URL-encoding using its REST-style routing patterns, e.g.: http://yoursite.com/pages/search/foo/bar/foo2/bar2/... You can also use named parameters to make the search URL more readable, e.g.: http://yoursite.com/products/search/q:paegan/artist:acid+bath/categor... Unless you have a ton of search options, I see no reason to use POST. And even if you use POST and generate shorter/cleaner URL--what's the point? What will that clean URL achieve? The user can't bookmark it. They can't link a friend to it. They can't do anything with it. A happy compromise would be to do what a lot of forums do, and cache each search server-side. Then when the user performs a search (with either POST or GET), they get redirected to the cached search result, which might be something like: http://yoursite.com/search/paegan+terrorism+tactics/f83e3a4b389c6b That will decrease your server load, allow you to use a POST form, and still allow the user to
Re: Query question(s)
@John:
[quote]
Can you explain why you have not separated/normalized the
SomeCol.contents?
And do you have other such columns in your database?
[/quote]
No, that is the only column in the db that stores multiple values in one
field (that aren't for display only). At one point in time the data in that
column wasn't searchable and it never presented an issue. A little hard to
explain but even though there are separate values, with the exception of the
recently added query they get treated as a whole within the app. I figured
that if the app ever changes down the road and needs to handle the values
individually it would be easy enough to explode the data. So to answer your
question of why? To be honest ... I thought I had good enough reasons but in
the end I suppose I didn't think it through well enough or far enough ahead.
If need be a restructuring of the db is not out of the question I just
assumed it wouldn't be a real stretch to come up with a creative solution
which ultimately ended up being the case. Just for the record, I do
recognize that a 'creative' solution is not always the best solution 8-).
@Calvin:
About the queries ... there was a basic and an advanced. The basic is done
and working. With the advanced search since I don't know fields elements are
being submitted for the search I would normally concatenate the query
itself.
[example]
$query= SELECT something FROM somewhere WHERE etc. etc. etc. ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
if (isset($_GET['some_other_value']) $_GET['some_other_value'] != '' )
{
$value = $_GET['some_other_value'];
$query .= AND something = '.addslashes($value).';
}
$query .= WHERE some_col = some_criteria
mysql_query($query);
[/example]
This worked well enough in the past. I agree that if the conditions were all
'OR' I would be good to go, unfortunately they all need to be 'AND'. The
closest example I can find in the book
( http://book.cakephp.org/view/1030/Complex-Find-Conditions ) that seems
even remotely close would be $dbo-buildStatement but to be brutally honest
I have no clue how I would implement that (or something similar) in this
case. Cake will accept the query whichever way I present it but the real
problem lies in 'paginate' and 'containable', both of which I need. I
suppose both could be written manually but then I would be well off the
beaten path as far as Cake conventions and given my track record it would
undoubtedly create some unforeseen problem somewhere down the line.
As far as URL-encoded array data goes I just set the form method to GET and
let Cake do it's thing. In the controller I pull the data from the URL using
$this-params['url']['something'].
If you are saying I can configure the routes to convert it over to named
params I'll play around with it some more. The furthest I experimenting I've
done with routes is to change from mySite/thisIsMyPage to
my-site/this-is-my-page.
- Ed
On Mon, Jun 7, 2010 at 11:55 AM, calvin cal...@rottenrecords.com wrote:
If all of your search options are OR conditions, then you could
theoretically do something like this:
SELECT ... WHERE contents REGEXP '(opt1|opt2|opt3|opt4|opt5|opt6)';
http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp
On Jun 6, 1:24 pm, Ed Propsner crotchf...@gmail.com wrote:
I found a usable solution, a bit exhaustive and long-winded perhaps, but
usable nonetheless.
I still need to put together a dynamic query and I'm finding myself
avoiding
having to do it at this time.
I need to build a query dynamically based on what elements a user chooses
from a form. There could be 20 choices or there could be 2.
Every solution I'm coming up with is ridiculously excessive for something
that should be so simple. I'm sure the answer is there, I'm just not
familiar enough with Cake at this point to see it.
- Ed
On Sat, Jun 5, 2010 at 4:36 PM, Ed Propsner crotchf...@gmail.com
wrote:
Perhaps I'm over-complicating this but I'm still having some problems
with
building my query.
I'm looking to do something like:
$array = array (A, B, C, D, E);
$list = implode( ',' , $array);
'conditions' = array(
'SomeCol.contents' = array($list)
)
The problem I'm running into is that $array can contain any number and
combination of values (A, C, E), (A, B), (E) ... etc.
And 'SomeCol.contents' can also contain any number and combination of
values stored as a comma separated string. (it was originally stored as
a
serialized array).
I need the query to return a result if 'SomeCol.contents' and $array
have
any one of their values in common.
I was thinking along the lines of :
'conditions' = array(
'SomeCol.contents LIKE' =
'%'.$array[0].'%'
Re: Query question(s)
I don't think you need buildStatement() in this case as your query,
although long, shouldn't actually be that complex, even if you end up
with both OR and AND conditions. You just need to take a look at how
OR/AND conditions are constructed in find() and then use them to build
custom pagination queries:
http://book.cakephp.org/view/249/Custom-Query-Pagination
Also, I did a test on GET forms using cake, and it should still create
URLs like:
/search?var1[]=0var1[]=1...
How are you creating the checkboxes? A good way to do it is:
echo $this-Form-input('type', array(
'type' = 'select',
'multiple' = 'checkbox',
'options' = array(
'1' = 'Video',
'2' = 'Audio',
'4' = 'Photo',
'8' = 'Text',
)
));
Also, notice that I use multiples of 2 for the option values. This
allows you to perform bitwise operations when querying the database.
When you save, you just call array_sum() and turn the array into an
integer, and save to a tinyint field.
And when you do a search, you can just create a simple query like:
array(
'or' = array(
'Model.type | '.$orOptions,
'Model.type '.$andOptions
)
);
This circumvents the problem of building long queries using LIKE. I
would strongly advise against hand-building the SQL, especially in
this case as your search query isn't so complex that you can't have
Cake build it for you.
On Jun 7, 12:14 pm, Ed Propsner crotchf...@gmail.com wrote:
@John:
[quote]
Can you explain why you have not separated/normalized the
SomeCol.contents?
And do you have other such columns in your database?
[/quote]
No, that is the only column in the db that stores multiple values in one
field (that aren't for display only). At one point in time the data in that
column wasn't searchable and it never presented an issue. A little hard to
explain but even though there are separate values, with the exception of the
recently added query they get treated as a whole within the app. I figured
that if the app ever changes down the road and needs to handle the values
individually it would be easy enough to explode the data. So to answer your
question of why? To be honest ... I thought I had good enough reasons but in
the end I suppose I didn't think it through well enough or far enough ahead.
If need be a restructuring of the db is not out of the question I just
assumed it wouldn't be a real stretch to come up with a creative solution
which ultimately ended up being the case. Just for the record, I do
recognize that a 'creative' solution is not always the best solution 8-).
�...@calvin:
About the queries ... there was a basic and an advanced. The basic is done
and working. With the advanced search since I don't know fields elements are
being submitted for the search I would normally concatenate the query
itself.
[example]
$query= SELECT something FROM somewhere WHERE etc. etc. etc. ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
if (isset($_GET['some_other_value']) $_GET['some_other_value'] != '' )
{
$value = $_GET['some_other_value'];
$query .= AND something = '.addslashes($value).';
}
$query .= WHERE some_col = some_criteria
mysql_query($query);
[/example]
This worked well enough in the past. I agree that if the conditions were all
'OR' I would be good to go, unfortunately they all need to be 'AND'. The
closest example I can find in the book
(http://book.cakephp.org/view/1030/Complex-Find-Conditions) that seems
even remotely close would be $dbo-buildStatement but to be brutally honest
I have no clue how I would implement that (or something similar) in this
case. Cake will accept the query whichever way I present it but the real
problem lies in 'paginate' and 'containable', both of which I need. I
suppose both could be written manually but then I would be well off the
beaten path as far as Cake conventions and given my track record it would
undoubtedly create some unforeseen problem somewhere down the line.
As far as URL-encoded array data goes I just set the form method to GET and
let Cake do it's thing. In the controller I pull the data from the URL using
$this-params['url']['something'].
If you are saying I can configure the routes to convert it over to named
params I'll play around with it some more. The furthest I experimenting I've
done with routes is to change from mySite/thisIsMyPage to
my-site/this-is-my-page.
- Ed
On Mon, Jun 7, 2010 at 11:55 AM, calvin cal...@rottenrecords.com wrote:
If all of your search options are OR conditions, then you could
theoretically do something like this:
SELECT ... WHERE contents REGEXP '(opt1|opt2|opt3|opt4|opt5|opt6)';
http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp
On Jun 6, 1:24 pm, Ed Propsner crotchf...@gmail.com
Re: Query question(s)
(My earlier post was inaccurate so I removed it. Let's try this
again...)
I don't think you need buildStatement() in this case as your query,
although long, shouldn't actually be that complex, even if you end up
with both OR and AND conditions. You just need to take a look at how
OR/AND conditions are constructed in find() and then use them to build
custom pagination queries:
http://book.cakephp.org/view/249/Custom-Query-Pagination
Also, I did a test on GET forms using cake, and it should still create
URLs like:
/search?var1[]=0var1[]=1...
How are you creating the checkboxes? A good way to do it is:
echo $this-Form-input('type', array(
'type' = 'select',
'multiple' = 'checkbox',
'options' = array(
'1' = 'Video',
'2' = 'Audio',
'4' = 'Photo',
'8' = 'Text',
)
));
Also, notice that I use multiples of 2 for the option values. This
allows you to perform bitwise operations when querying the database.
When you save, you just call array_sum() and turn the array into an
integer, and save to a tinyint field.
And when you do a search, you can just create a simple query like:
array(
'and' = array(
Model.type $orOptions 0,
Model.type $andOptions = $andOptions
)
);
--where $orOptions is just an array_sum() of the OR options and
$andOptions is the array_sum() of the AND options.
So if the user wants to find a directory that has Videos AND Photos OR
Text, then $orOptions would be 8 and $andOptions would be 5 (1 + 4).
This circumvents the problem of building long queries using LIKE and
multiple ANDs and ORs. I would strongly advise against hand-building
the SQL, especially in this case as your search query isn't so complex
that you can't have Cake build it for you.
On Jun 7, 12:14 pm, Ed Propsner crotchf...@gmail.com wrote:
@John:
[quote]
Can you explain why you have not separated/normalized the
SomeCol.contents?
And do you have other such columns in your database?
[/quote]
No, that is the only column in the db that stores multiple values in one
field (that aren't for display only). At one point in time the data in that
column wasn't searchable and it never presented an issue. A little hard to
explain but even though there are separate values, with the exception of the
recently added query they get treated as a whole within the app. I figured
that if the app ever changes down the road and needs to handle the values
individually it would be easy enough to explode the data. So to answer your
question of why? To be honest ... I thought I had good enough reasons but in
the end I suppose I didn't think it through well enough or far enough ahead.
If need be a restructuring of the db is not out of the question I just
assumed it wouldn't be a real stretch to come up with a creative solution
which ultimately ended up being the case. Just for the record, I do
recognize that a 'creative' solution is not always the best solution 8-).
@Calvin:
About the queries ... there was a basic and an advanced. The basic is done
and working. With the advanced search since I don't know fields elements are
being submitted for the search I would normally concatenate the query
itself.
[example]
$query= SELECT something FROM somewhere WHERE etc. etc. etc. ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
if (isset($_GET['some_other_value']) $_GET['some_other_value'] != '' )
{
$value = $_GET['some_other_value'];
$query .= AND something = '.addslashes($value).';
}
$query .= WHERE some_col = some_criteria
mysql_query($query);
[/example]
This worked well enough in the past. I agree that if the conditions were all
'OR' I would be good to go, unfortunately they all need to be 'AND'. The
closest example I can find in the book
(http://book.cakephp.org/view/1030/Complex-Find-Conditions) that seems
even remotely close would be $dbo-buildStatement but to be brutally honest
I have no clue how I would implement that (or something similar) in this
case. Cake will accept the query whichever way I present it but the real
problem lies in 'paginate' and 'containable', both of which I need. I
suppose both could be written manually but then I would be well off the
beaten path as far as Cake conventions and given my track record it would
undoubtedly create some unforeseen problem somewhere down the line.
As far as URL-encoded array data goes I just set the form method to GET and
let Cake do it's thing. In the controller I pull the data from the URL using
$this-params['url']['something'].
If you are saying I can configure the routes to convert it over to named
params I'll play around with it some more. The furthest I experimenting I've
done with routes is to change from mySite/thisIsMyPage to
my-site/this-is-my-page.
- Ed
On Mon, Jun
Re: Query question(s)
Ack, that example wasn't very good either, so let's try _one_ more
time...
Say you want to do a search for records that conform to these
conditions:
-must be of _both_ type Video AND Photo
-must also be _either_ type Audio OR Text
then $andOptions would be 5; $orOptions would be 10.
On Jun 7, 7:38 pm, calvin cal...@rottenrecords.com wrote:
(My earlier post was inaccurate so I removed it. Let's try this
again...)
I don't think you need buildStatement() in this case as your query,
although long, shouldn't actually be that complex, even if you end up
with both OR and AND conditions. You just need to take a look at how
OR/AND conditions are constructed in find() and then use them to build
custom pagination
queries:http://book.cakephp.org/view/249/Custom-Query-Pagination
Also, I did a test on GET forms using cake, and it should still create
URLs like:
/search?var1[]=0var1[]=1...
How are you creating the checkboxes? A good way to do it is:
echo $this-Form-input('type', array(
'type' = 'select',
'multiple' = 'checkbox',
'options' = array(
'1' = 'Video',
'2' = 'Audio',
'4' = 'Photo',
'8' = 'Text',
)
));
Also, notice that I use multiples of 2 for the option values. This
allows you to perform bitwise operations when querying the database.
When you save, you just call array_sum() and turn the array into an
integer, and save to a tinyint field.
And when you do a search, you can just create a simple query like:
array(
'and' = array(
Model.type $orOptions 0,
Model.type $andOptions = $andOptions
)
);
--where $orOptions is just an array_sum() of the OR options and
$andOptions is the array_sum() of the AND options.
So if the user wants to find a directory that has Videos AND Photos OR
Text, then $orOptions would be 8 and $andOptions would be 5 (1 + 4).
This circumvents the problem of building long queries using LIKE and
multiple ANDs and ORs. I would strongly advise against hand-building
the SQL, especially in this case as your search query isn't so complex
that you can't have Cake build it for you.
On Jun 7, 12:14 pm, Ed Propsner crotchf...@gmail.com wrote:
@John:
[quote]
Can you explain why you have not separated/normalized the
SomeCol.contents?
And do you have other such columns in your database?
[/quote]
No, that is the only column in the db that stores multiple values in one
field (that aren't for display only). At one point in time the data in that
column wasn't searchable and it never presented an issue. A little hard to
explain but even though there are separate values, with the exception of the
recently added query they get treated as a whole within the app. I figured
that if the app ever changes down the road and needs to handle the values
individually it would be easy enough to explode the data. So to answer your
question of why? To be honest ... I thought I had good enough reasons but in
the end I suppose I didn't think it through well enough or far enough ahead.
If need be a restructuring of the db is not out of the question I just
assumed it wouldn't be a real stretch to come up with a creative solution
which ultimately ended up being the case. Just for the record, I do
recognize that a 'creative' solution is not always the best solution 8-).
�...@calvin:
About the queries ... there was a basic and an advanced. The basic is done
and working. With the advanced search since I don't know fields elements are
being submitted for the search I would normally concatenate the query
itself.
[example]
$query= SELECT something FROM somewhere WHERE etc. etc. etc. ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
if (isset($_GET['some_other_value']) $_GET['some_other_value'] != '' )
{
$value = $_GET['some_other_value'];
$query .= AND something = '.addslashes($value).';
}
$query .= WHERE some_col = some_criteria
mysql_query($query);
[/example]
This worked well enough in the past. I agree that if the conditions were all
'OR' I would be good to go, unfortunately they all need to be 'AND'. The
closest example I can find in the book
(http://book.cakephp.org/view/1030/Complex-Find-Conditions) that seems
even remotely close would be $dbo-buildStatement but to be brutally honest
I have no clue how I would implement that (or something similar) in this
case. Cake will accept the query whichever way I present it but the real
problem lies in 'paginate' and 'containable', both of which I need. I
suppose both could be written manually but then I would be well off the
beaten path as far as Cake conventions and given my track record it would
undoubtedly create some unforeseen problem somewhere down the line.
As far
Re: Query question(s)
I found a usable solution, a bit exhaustive and long-winded perhaps, but usable nonetheless. I still need to put together a dynamic query and I'm finding myself avoiding having to do it at this time. I need to build a query dynamically based on what elements a user chooses from a form. There could be 20 choices or there could be 2. Every solution I'm coming up with is ridiculously excessive for something that should be so simple. I'm sure the answer is there, I'm just not familiar enough with Cake at this point to see it. - Ed On Sat, Jun 5, 2010 at 4:36 PM, Ed Propsner crotchf...@gmail.com wrote: Perhaps I'm over-complicating this but I'm still having some problems with building my query. I'm looking to do something like: $array = array (A, B, C, D, E); $list = implode( ',' , $array); 'conditions' = array( 'SomeCol.contents' = array($list) ) The problem I'm running into is that $array can contain any number and combination of values (A, C, E), (A, B), (E) ... etc. And 'SomeCol.contents' can also contain any number and combination of values stored as a comma separated string. (it was originally stored as a serialized array). I need the query to return a result if 'SomeCol.contents' and $array have any one of their values in common. I was thinking along the lines of : 'conditions' = array( 'SomeCol.contents LIKE' = '%'.$array[0].'%' OR 'SomeCol.contents LIKE' = '%'.$array[1].'%'etc, etc // this way should include any record in the result that shares a value with $array but I assume I would run into an issue with an undefined index by not knowing how many values $array contains. (I do know it has the potential to store a max of 7 values) I had this all worked out with conventional PHP but now I need to use Paginate and containable with this query as well as a bunch of other conditions (with a set value) and I'm confusing myself more than anything else which isn't a real stretch for me to begin with 8-). Any suggestions are appreciated. Thanks. - Ed On Fri, Jun 4, 2010 at 11:54 PM, Ed Propsner crotchf...@gmail.com wrote: Thanks Calvin, point well taken. I'll just stick with GET like I always have for searches. I tweaked the routing and got things cleaned up a bit so I guess I'm okay with it. I'm not quite sure what I was expecting in the first place? 8-) The search uses a lot of checkboxes and results in something like http://mysite.com/search/results/value1=0value1=1value2=0value2=1. I can't seem to clean it up any more when submitting with GET, or am I still missing something? Anyhow, at least it's working the way that I want it to. I'm still having some issues with query so I'll quit fussing with the url for now. - Ed On Fri, Jun 4, 2010 at 10:15 PM, calvin cal...@rottenrecords.com wrote: POST requests are generally not cached, but you can force it to be cached using the Cache-Control and Expires headers. However, I've never tried this so I don't know if the browser will still show the form submission dialog (it may need to resend the form data to check to see if the document has changed since last requested). It probably won't, but I would still strongly advise against this. A search request is generally an idempotent operation (multiple requests do not have any side-effects), which is precisely what the GET request is designed for. There's no reason to use POST in this case. Cake has a perfectly good way of hiding ugly URL-encoding using its REST-style routing patterns, e.g.: http://yoursite.com/pages/search/foo/bar/foo2/bar2/... You can also use named parameters to make the search URL more readable, e.g.: http://yoursite.com/products/search/q:paegan/artist:acid+bath/category:t-shirt Unless you have a ton of search options, I see no reason to use POST. And even if you use POST and generate shorter/cleaner URL--what's the point? What will that clean URL achieve? The user can't bookmark it. They can't link a friend to it. They can't do anything with it. A happy compromise would be to do what a lot of forums do, and cache each search server-side. Then when the user performs a search (with either POST or GET), they get redirected to the cached search result, which might be something like: http://yoursite.com/search/paegan+terrorism+tactics/f83e3a4b389c6b That will decrease your server load, allow you to use a POST form, and still allow the user to bookmark/link the search results (at least for a time). On Jun 4, 10:40 am, Ed Propsner crotchf...@gmail.com wrote: I was checking out the book on complex queries and not really finding what I'm looking for. I'm trying to create a query that covers both a basic and advanced search. The form may be submitting all or just some of the options available on the
Re: Query question(s)
Perhaps I'm over-complicating this but I'm still having some problems with
building my query.
I'm looking to do something like:
$array = array (A, B, C, D, E);
$list = implode( ',' , $array);
'conditions' = array(
'SomeCol.contents' = array($list)
)
The problem I'm running into is that $array can contain any number and
combination of values (A, C, E), (A, B), (E) ... etc.
And 'SomeCol.contents' can also contain any number and combination of values
stored as a comma separated string. (it was originally stored as a
serialized array).
I need the query to return a result if 'SomeCol.contents' and $array have
any one of their values in common.
I was thinking along the lines of :
'conditions' = array(
'SomeCol.contents LIKE' = '%'.$array[0].'%'
OR
'SomeCol.contents LIKE' = '%'.$array[1].'%'
etc, etc // this way should include any record in the result that shares
a value with $array
but I assume I would run into an issue with an undefined index by not
knowing how many values $array contains. (I do know it has the potential to
store a max of 7 values)
I had this all worked out with conventional PHP but now I need to use
Paginate and containable with this query as well as a bunch of other
conditions (with a set value) and I'm confusing myself more than anything
else which isn't a real stretch for me to begin with 8-).
Any suggestions are appreciated.
Thanks.
- Ed
On Fri, Jun 4, 2010 at 11:54 PM, Ed Propsner crotchf...@gmail.com wrote:
Thanks Calvin, point well taken. I'll just stick with GET like I always
have for searches.
I tweaked the routing and got things cleaned up a bit so I guess I'm okay
with it.
I'm not quite sure what I was expecting in the first place? 8-)
The search uses a lot of checkboxes and results in something like
http://mysite.com/search/results/value1=0value1=1value2=0value2=1.
I can't seem to clean it up any more when submitting with GET, or am I
still missing something?
Anyhow, at least it's working the way that I want it to. I'm still having
some issues with query so I'll quit fussing with the url for now.
- Ed
On Fri, Jun 4, 2010 at 10:15 PM, calvin cal...@rottenrecords.com wrote:
POST requests are generally not cached, but you can force it to be
cached using the Cache-Control and Expires headers. However, I've
never tried this so I don't know if the browser will still show the
form submission dialog (it may need to resend the form data to check
to see if the document has changed since last requested). It probably
won't, but I would still strongly advise against this.
A search request is generally an idempotent operation (multiple
requests do not have any side-effects), which is precisely what the
GET request is designed for. There's no reason to use POST in this
case. Cake has a perfectly good way of hiding ugly URL-encoding using
its REST-style routing patterns, e.g.:
http://yoursite.com/pages/search/foo/bar/foo2/bar2/...
You can also use named parameters to make the search URL more
readable, e.g.:
http://yoursite.com/products/search/q:paegan/artist:acid+bath/category:t-shirt
Unless you have a ton of search options, I see no reason to use POST.
And even if you use POST and generate shorter/cleaner URL--what's the
point? What will that clean URL achieve? The user can't bookmark it.
They can't link a friend to it. They can't do anything with it.
A happy compromise would be to do what a lot of forums do, and cache
each search server-side. Then when the user performs a search (with
either POST or GET), they get redirected to the cached search result,
which might be something like:
http://yoursite.com/search/paegan+terrorism+tactics/f83e3a4b389c6b
That will decrease your server load, allow you to use a POST form, and
still allow the user to bookmark/link the search results (at least for
a time).
On Jun 4, 10:40 am, Ed Propsner crotchf...@gmail.com wrote:
I was checking out the book on complex queries and not really finding
what
I'm looking for.
I'm trying to create a query that covers both a basic and advanced
search.
The form may be submitting all or just some of the options available on
the
page depending on what the user chooses.
Once the form has been submitted clicking any one of the query results
would
navigate the user away from the 'results page'.
To avoid getting hit with the form submission dialogue when the users
clicks the back button to return to the search results page
and to cover the 'conditional query' ... I used to use a combo of $_GET
and
if(isset.
ie:
$query= SELECT something FROM somewhere WHERE etc. etc. etc. AND ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
Query question(s)
I was checking out the book on complex queries and not really finding what
I'm looking for.
I'm trying to create a query that covers both a basic and advanced search.
The form may be submitting all or just some of the options available on the
page depending on what the user chooses.
Once the form has been submitted clicking any one of the query results would
navigate the user away from the 'results page'.
To avoid getting hit with the form submission dialogue when the users
clicks the back button to return to the search results page
and to cover the 'conditional query' ... I used to use a combo of $_GET and
if(isset.
ie:
$query= SELECT something FROM somewhere WHERE etc. etc. etc. AND ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
And so on.
I'm not familiar with using cache for anything. To avoid using get and the
ugly urls would I be able to use post and cache the results also avoiding
the form submission dialogue when returning to the results page?
Also, what is the best approach to setting up a 'conditional' query similar
to what I posted above?
I'll spend some more time digging through the book and experimenting if
someone can point me in the right direction.
Thanks,
- Ed
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: Query question(s)
POST requests are generally not cached, but you can force it to be
cached using the Cache-Control and Expires headers. However, I've
never tried this so I don't know if the browser will still show the
form submission dialog (it may need to resend the form data to check
to see if the document has changed since last requested). It probably
won't, but I would still strongly advise against this.
A search request is generally an idempotent operation (multiple
requests do not have any side-effects), which is precisely what the
GET request is designed for. There's no reason to use POST in this
case. Cake has a perfectly good way of hiding ugly URL-encoding using
its REST-style routing patterns, e.g.:
http://yoursite.com/pages/search/foo/bar/foo2/bar2/...
You can also use named parameters to make the search URL more
readable, e.g.:
http://yoursite.com/products/search/q:paegan/artist:acid+bath/category:t-shirt
Unless you have a ton of search options, I see no reason to use POST.
And even if you use POST and generate shorter/cleaner URL--what's the
point? What will that clean URL achieve? The user can't bookmark it.
They can't link a friend to it. They can't do anything with it.
A happy compromise would be to do what a lot of forums do, and cache
each search server-side. Then when the user performs a search (with
either POST or GET), they get redirected to the cached search result,
which might be something like:
http://yoursite.com/search/paegan+terrorism+tactics/f83e3a4b389c6b
That will decrease your server load, allow you to use a POST form, and
still allow the user to bookmark/link the search results (at least for
a time).
On Jun 4, 10:40 am, Ed Propsner crotchf...@gmail.com wrote:
I was checking out the book on complex queries and not really finding what
I'm looking for.
I'm trying to create a query that covers both a basic and advanced search.
The form may be submitting all or just some of the options available on the
page depending on what the user chooses.
Once the form has been submitted clicking any one of the query results would
navigate the user away from the 'results page'.
To avoid getting hit with the form submission dialogue when the users
clicks the back button to return to the search results page
and to cover the 'conditional query' ... I used to use a combo of $_GET and
if(isset.
ie:
$query= SELECT something FROM somewhere WHERE etc. etc. etc. AND ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
And so on.
I'm not familiar with using cache for anything. To avoid using get and the
ugly urls would I be able to use post and cache the results also avoiding
the form submission dialogue when returning to the results page?
Also, what is the best approach to setting up a 'conditional' query similar
to what I posted above?
I'll spend some more time digging through the book and experimenting if
someone can point me in the right direction.
Thanks,
- Ed
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: Query question(s)
Thanks Calvin, point well taken. I'll just stick with GET like I always have
for searches.
I tweaked the routing and got things cleaned up a bit so I guess I'm okay
with it.
I'm not quite sure what I was expecting in the first place? 8-)
The search uses a lot of checkboxes and results in something like
http://mysite.com/search/results/value1=0value1=1value2=0value2=1.
I can't seem to clean it up any more when submitting with GET, or am I still
missing something?
Anyhow, at least it's working the way that I want it to. I'm still having
some issues with query so I'll quit fussing with the url for now.
- Ed
On Fri, Jun 4, 2010 at 10:15 PM, calvin cal...@rottenrecords.com wrote:
POST requests are generally not cached, but you can force it to be
cached using the Cache-Control and Expires headers. However, I've
never tried this so I don't know if the browser will still show the
form submission dialog (it may need to resend the form data to check
to see if the document has changed since last requested). It probably
won't, but I would still strongly advise against this.
A search request is generally an idempotent operation (multiple
requests do not have any side-effects), which is precisely what the
GET request is designed for. There's no reason to use POST in this
case. Cake has a perfectly good way of hiding ugly URL-encoding using
its REST-style routing patterns, e.g.:
http://yoursite.com/pages/search/foo/bar/foo2/bar2/...
You can also use named parameters to make the search URL more
readable, e.g.:
http://yoursite.com/products/search/q:paegan/artist:acid+bath/category:t-shirt
Unless you have a ton of search options, I see no reason to use POST.
And even if you use POST and generate shorter/cleaner URL--what's the
point? What will that clean URL achieve? The user can't bookmark it.
They can't link a friend to it. They can't do anything with it.
A happy compromise would be to do what a lot of forums do, and cache
each search server-side. Then when the user performs a search (with
either POST or GET), they get redirected to the cached search result,
which might be something like:
http://yoursite.com/search/paegan+terrorism+tactics/f83e3a4b389c6b
That will decrease your server load, allow you to use a POST form, and
still allow the user to bookmark/link the search results (at least for
a time).
On Jun 4, 10:40 am, Ed Propsner crotchf...@gmail.com wrote:
I was checking out the book on complex queries and not really finding
what
I'm looking for.
I'm trying to create a query that covers both a basic and advanced
search.
The form may be submitting all or just some of the options available on
the
page depending on what the user chooses.
Once the form has been submitted clicking any one of the query results
would
navigate the user away from the 'results page'.
To avoid getting hit with the form submission dialogue when the users
clicks the back button to return to the search results page
and to cover the 'conditional query' ... I used to use a combo of $_GET
and
if(isset.
ie:
$query= SELECT something FROM somewhere WHERE etc. etc. etc. AND ;
if (isset($_GET['some_value']) $_GET['some_value'] != '' )
{
$value = $_GET['some_value'];
$query .= AND something = '.addslashes($value).';
}
And so on.
I'm not familiar with using cache for anything. To avoid using get and
the
ugly urls would I be able to use post and cache the results also avoiding
the form submission dialogue when returning to the results page?
Also, what is the best approach to setting up a 'conditional' query
similar
to what I posted above?
I'll spend some more time digging through the book and experimenting if
someone can point me in the right direction.
Thanks,
- Ed
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.comcake-php%2bunsubscr...@googlegroups.comFor
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
Query question
Just doing some messing around on a site and playing with contain vs. $this-model-read($id). When I leave the controller to read the $id SQL Queries = 6 but contain spits out 8. Now the 6 queries was 2010 characters in length while the 8 queries was 400 less at 1600 characters. My question...is it better to have fewer longer queries or more shorter queries when it comes to speed? Both were very similar in time but then there is only me testing the site now...thinking what is best in a real live production environment? I will be containing more queries on other controller and it just happened to be that there were more for this particular one...might be less on others but just wanted to hear some general feedback from people who have more experience with Cake. thanks, Dave --~--~-~--~~~---~--~~ 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: Query question
I don't think the number of characters in a query makes too much difference. Not compared to how the query is formulated, in any case. It's also not so much a Cake thing as between your DB and its PHP wrapper. But, again, it's likely the least thing to be concerned about. On Fri, Apr 24, 2009 at 11:59 PM, Dave Maharaj :: WidePixels.com d...@widepixels.com wrote: Just doing some messing around on a site and playing with contain vs. $this-model-read($id). When I leave the controller to read the $id SQL Queries = 6 but contain spits out 8. Now the 6 queries was 2010 characters in length while the 8 queries was 400 less at 1600 characters. My question...is it better to have fewer longer queries or more shorter queries when it comes to speed? Both were very similar in time but then there is only me testing the site now...thinking what is best in a real live production environment? I will be containing more queries on other controller and it just happened to be that there were more for this particular one...might be less on others but just wanted to hear some general feedback from people who have more experience with Cake. thanks, Dave --~--~-~--~~~---~--~~ 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: Query question
You could possibly do a UNION query? If you need to know which table they
came from, add a hard-coded value to the field list ('TableA' as
which_table).
SELECT id, firstname, lastname FROM tableA where email = 'a...@b.com'
UNION
SELECT id, firstname, lastname FROM tableB where email = 'a...@b.com'
It's the same as doing two queries, but joining the result set might be
faster in the DBMS than joining two arrays with PHP; I've not checked to
see.
Richard
On Tue, Apr 14, 2009 at 4:47 AM, Dave Maharaj :: WidePixels.com
d...@widepixels.com wrote:
I have come into a situation where I need to find something that will be
in either TableA or TableB. Is there an easy way to do this or do i need to
query TableA if no results query TableB?
thanks,
Dave
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
Query question
I have come into a situation where I need to find something that will be in either TableA or TableB. Is there an easy way to do this or do i need to query TableA if no results query TableB? thanks, Dave --~--~-~--~~~---~--~~ 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 Query Question
I am stuck on a query problem :
These are the tables involved :
Group
id
name
User
id
name
Groups_Users (for HABTM)
id
group_id
user_id
Now i have an admin system that lets the admin view the group and all
the related users.
Which is working fine. However, I want to do the OPPOSITE.
View the group and display ALL the users that are not in that group.
How would I structure my $find statement?
Obviously, this is wrong but it is what i need it to do :
$users = $this-User-find('all',array('conditions'=
array('NOT'=array('User.id' =
'GroupsUser.user_id')))
);
This next set of code, is what i want it to do, but not have to
explicitly declare the user ids
This would return all users NOT 1,2,3,4.
$filter = array(1,2,3,4);
$users = $this-User-find('all',array
('conditions'=
array('NOT'=array('User.id' =
$filter)))
);
would appreciate all 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
-~--~~~~--~~--~--~---
pagination / custom query question
I have 2 models Venue and Event. I want to do a search page that returns
both Venue and Event on 1 page and trying to get the ajax pagination feature
in cake rc2 to work.
However, the first parameter for $this-paginate(...) is the 'type' (model).
Is it possible for my controller to have both $this-paginate ('Venue',...)
and $this-paginate ('Event',...) at the same time?
Or, it would be preferable to do a custom query and feed the results of this
query to the paginate() function. Is this possible?
Any other possible ways to do this?
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Query Question
City belongsTo State
Assuming Cake 1.2:
$cities = $this-City-find('list', array('fields'=array('City.id',
City.name', 'State.name'), 'recursive'=1) );
Then you can have a form select with cities as the data source, you
should get exactly what you want.
On May 12, 9:00 am, Kyle Decot [EMAIL PROTECTED] wrote:
I want to make a side nave bar with states and cities of that state as
sub items. How would I write a query to do this? I want something
like:
Florida
_Orlando
_Daytona
Ohio
_Columbus
_New Albany
etc...
Thanks as always for the 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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Query Question
They're not stored in separate tables. They're stored in my skateparks table which has: id | name | address | city | state ...etc --~--~-~--~~~---~--~~ 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: Query Question
look at self relating the table by using a field named parent_id if your baking cake will recognise that the relationship is a child/parent id | parent id | name | address | city | state ...etc 2008/5/12 Kyle Decot [EMAIL PROTECTED]: They're not stored in separate tables. They're stored in my skateparks table which has: id | name | address | city | state ...etc --~--~-~--~~~---~--~~ 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: Query Question
I'd just use SELECT state, city FROM skateparks ORDER BY state, city Then loop it end everytime the state changes you print the state, otherwise just the city. On May 12, 12:39 pm, Kyle Decot [EMAIL PROTECTED] wrote: They're not stored in separate tables. They're stored in my skateparks table which has: id | name | address | city | state ...etc --~--~-~--~~~---~--~~ 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: Query Question
// Controller
$parks = $this-Skatepark-find('list', array(
'fields' = array(
'Skatepark.id', // Key path
'Skatepark.city', // Value path
'Skatepark.state' // Group path
),
'order' = 'Skatepark.city ASC, Skatepark.state ASC'
);
$this-set(compact('parks'));
// parks looks like this
// array('Florida' = array(23 = 'Orlando', 17 = 'Daytona'), 'Ohio'
= [...])
// View
echo 'ul';
foreach ($parks as $state = $parksList) {
echo 'li' ;
echo $state;
echo 'ul';
foreach ($parksList as $id = $city) {
echo 'li' . $html-link($city, array('controller' =
'skateparks', 'action' = 'view', $id)) . '/li';
}
echo '/ul';
echo '/li';
}
echo '/ul';
On May 12, 11:46 am, Sam Sherlock [EMAIL PROTECTED] wrote:
look at self relating the table by using a field named parent_id if your
baking cake will recognise that the relationship is a child/parent
id | parent id | name | address | city | state ...etc
2008/5/12 Kyle Decot [EMAIL PROTECTED]:
They're not stored in separate tables. They're stored in my skateparks
table which has:
id | name | address | city | state ...etc
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
Query Question
I want to make a side nave bar with states and cities of that state as sub items. How would I write a query to do this? I want something like: Florida _Orlando _Daytona Ohio _Columbus _New Albany etc... Thanks as always for the 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Query Question
Games HABTM Categories
I need to $this-paginate('Game'); where Category.name = 'puzzle'
Is this possible? Tried using bindModel but can't figure it out.
--~--~-~--~~~---~--~~
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: Query Question
you set your conditions in the paginate variable. Example:
class GamesController extends AppController {
var $name = 'Games';
var $paginate = array(
'Game' = array(
'limit'=10,
'order'= 'Game.id ASC',
)
);
...
function someAction()
{
...
$conditions=array(your conditions);
$this-paginate['Game']['conditions']=$conditions;
$this-set('games', $this-paginate());
..
}
}
On Apr 2, 10:37 am, dan [EMAIL PROTECTED] wrote:
Games HABTM Categories
I need to $this-paginate('Game'); where Category.name = 'puzzle'
Is this possible? Tried using bindModel but can't figure it out.
--~--~-~--~~~---~--~~
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: Query Question
Thanks for the note but I can't get conditions to work because it's a
HABTM relationship.
I tried
$this-paginate('Game', Category.name = 'puzzle');
but it gives me
SQL Error: 1054: Unknown column 'Category.id' in 'where clause'
The query doesn't join Category to Game so it has no idea what
Category.name is.
--~--~-~--~~~---~--~~
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: Query Question
I figured it out
$this-Game-bindModel(
array('hasOne' = array(
'CategoriesGame' = array(
'className' = 'CategoriesGame'
)
)), false
);
$data = $this-paginate('Game', CategoriesGame.category_id =
'puzzle');
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
newbie query question
I have a Listings table and another Categories table, and Lists hasMany
Categories. I am trying to write a cake query where it finds all lists in a
certain category.
$listings = $this-Listing-findAll(
array('Category.category_id'=$category_id)
);
this returns an error.
Also, if I do $this-Listing-findAll(), it just returns the columns in
Listings, not Categories. How would i do this?
--~--~-~--~~~---~--~~
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: newbie query question
Do you have?
class Listing extends AppModel {
public $belongsTo = array('Category');
}
class Category extends AppModel {
public $hasMay = array('Listing');
}
Then you should be able to:
$listing = $this-Listing-findAll(array('category_id' =
$category_id));
On Jan 26, 9:30 pm, bob [EMAIL PROTECTED] wrote:
I have a Listings table and another Categories table, and Lists hasMany
Categories. I am trying to write a cake query where it finds all lists in a
certain category.
$listings = $this-Listing-findAll(
array('Category.category_id'=$category_id)
);
this returns an error.
Also, if I do $this-Listing-findAll(), it just returns the columns in
Listings, not Categories. How would i do this?
--~--~-~--~~~---~--~~
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: hasMany query question
Hmmm... Me thinks you have a logic problem.
If Classification belongsTo Ad and Location belongsTo Ad then
searching by both Location.id and Classification.id at the same time is
redundant, since they would both share (at most) exactly one parent.
So if Classification belongsTo Ad , then Classification has only one
parent, and you can simply do this:
$this-Ad-Classifications-recursive = 3;
$data =
$this-Ad-Classifications-findById($this-data['Classification']['id']);
$ad = $data['Ad'];
Or if Location belongsTo Ad , then Location has only one parent, and
you can simply do this:
$this-Ad-Location-recursive = 3;
$data = $this-Ad-Location-findById($this-data['Location']['id']);
$ad = $data['Ad'];
Back to the logic problem... If one ad can have more than one
classification, and many adds can share the same classification, then
you are not using the correct relationships. Based on your question, I
suspect you should be using hasAndBelongsToMany. If you think this is
possible, read the model chapter of the cake manual again:
http://manual.cakephp.org/chapter/models Otherwise, ignore my
response.
sc
On Nov 19, 12:18 am, naryga [EMAIL PROTECTED] wrote:
Ok, I have 3 models:
Ad, Classification, Location
Ad hasMany Classification,Location
Location belongsTo Ad
Classification belongsTo Ad
I want to be able to search for Ads based on the associated
Classifications and Locations. I've tried using something like:
code
$this-Ad-findAll(Classification.id =
{$this-data['Classification']['id']} AND Location.id =
{$this-data['Location']['id']});
/code
But this results in: SQL Error in model Post: 1054: Unknown column
'Classification.id' in 'where clause'
Using Debug: 3, I found that the problem seems to be that when
searching on a model with hasMany associations, an SQL alias is not
created for the associated modles (ie, Classification as
classifications). However, changing my where statment to
classifications.id = ... Doesn't seem to help.
If I only wanted to use one of the associated models to limit the
results, I would use
$this-Ad-Classifications-findAll(Classification.id =
{$this-data['Classification']['id']}); which works just fine. Is
there some other way I should be doing this? Thanks in advance!
--~--~-~--~~~---~--~~
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: hasMany query question
Thanks for the reply. Let me expand abit. Model 'A' hasMany 'B' and
hasMany 'C'. I want to find the instance of model 'A' that has a spicific
'B' AND a specific 'C'. Now here's the part I left out. Model 'D' hasMany
'B' and 'E' hasMany 'C'. So, a given 'B' or 'C' belongs to only one 'A',
but also to only one 'D' or 'E' (as apposed to many 'A', 'D' 'E's).
Basically, 'A' hasAndBelongsTo 'D' and 'E', but the join tables are too
complex for cake's standard habt relationship to work (there's more in there
than I've described). So I created a model for the join tables and made the
change in the relationships.
So, one 'Ad' can have many 'Classifications', and many 'Locations', but I'm
looking for the specific 'Ad' that has a 'Classification' AND a 'Location'
containing the correct 'Category' and 'City' respectively.
However, the question remains, is it posible to use a feild from a table
associated with a hasMany relationship in the findAll($conditions) array?
What I've recently decided to try on monday is to do:
$var1 = $this-Ad-Location-findAll($conditions);
$var2 = $this-Ad-Classification-findAll($conditions);
$ads = array_intersect($var1, $var2);
Although that seems like a weird way to do it. Then again, maybe my whole
concept is the weird way to do it in the first place.
--
ng
On 11/19/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hmmm... Me thinks you have a logic problem.
If Classification belongsTo Ad and Location belongsTo Ad then
searching by both Location.id and Classification.id at the same time is
redundant, since they would both share (at most) exactly one parent.
So if Classification belongsTo Ad , then Classification has only one
parent, and you can simply do this:
$this-Ad-Classifications-recursive = 3;
$data =
$this-Ad-Classifications-findById($this-data['Classification']['id']);
$ad = $data['Ad'];
Or if Location belongsTo Ad , then Location has only one parent, and
you can simply do this:
$this-Ad-Location-recursive = 3;
$data = $this-Ad-Location-findById($this-data['Location']['id']);
$ad = $data['Ad'];
Back to the logic problem... If one ad can have more than one
classification, and many adds can share the same classification, then
you are not using the correct relationships. Based on your question, I
suspect you should be using hasAndBelongsToMany. If you think this is
possible, read the model chapter of the cake manual again:
http://manual.cakephp.org/chapter/models Otherwise, ignore my
response.
sc
On Nov 19, 12:18 am, naryga [EMAIL PROTECTED] wrote:
Ok, I have 3 models:
Ad, Classification, Location
Ad hasMany Classification,Location
Location belongsTo Ad
Classification belongsTo Ad
I want to be able to search for Ads based on the associated
Classifications and Locations. I've tried using something like:
code
$this-Ad-findAll(Classification.id =
{$this-data['Classification']['id']} AND Location.id =
{$this-data['Location']['id']});
/code
But this results in: SQL Error in model Post: 1054: Unknown column
'Classification.id' in 'where clause'
Using Debug: 3, I found that the problem seems to be that when
searching on a model with hasMany associations, an SQL alias is not
created for the associated modles (ie, Classification as
classifications). However, changing my where statment to
classifications.id = ... Doesn't seem to help.
If I only wanted to use one of the associated models to limit the
results, I would use
$this-Ad-Classifications-findAll(Classification.id =
{$this-data['Classification']['id']}); which works just fine. Is
there some other way I should be doing this? Thanks in advance!
--
Nathan Garza
AshLeaf Media | Director of Technology Innovations
_
www.ashleafmedia.com | [EMAIL PROTECTED] | 832.514.5726
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
hasMany query question
Ok, I have 3 models:
Ad, Classification, Location
Ad hasMany Classification,Location
Location belongsTo Ad
Classification belongsTo Ad
I want to be able to search for Ads based on the associated
Classifications and Locations. I've tried using something like:
code
$this-Ad-findAll(Classification.id =
{$this-data['Classification']['id']} AND Location.id =
{$this-data['Location']['id']});
/code
But this results in: SQL Error in model Post: 1054: Unknown column
'Classification.id' in 'where clause'
Using Debug: 3, I found that the problem seems to be that when
searching on a model with hasMany associations, an SQL alias is not
created for the associated modles (ie, Classification as
classifications). However, changing my where statment to
classifications.id = ... Doesn't seem to help.
If I only wanted to use one of the associated models to limit the
results, I would use
$this-Ad-Classifications-findAll(Classification.id =
{$this-data['Classification']['id']}); which works just fine. Is
there some other way I should be doing this? Thanks in advance!
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
