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[]=0&var1[]=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 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].'%' > > > > > 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=0&value1=1&value2=0&value2=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 > > ... > > read more » 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
