(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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]>
> > 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 <[email protected]>
> > 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 <[email protected]>
> > 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 <[email protected]> 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
>
> ...
>
> read more »
On Jun 7, 12:14 pm, Ed Propsner <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]>
> > 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 <[email protected]>
> > 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 <[email protected]>
> > 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 <[email protected]> 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
>
> ...
>
> 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 [email protected]
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
