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.com>wrote: > > > 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 sitehttp://cakeqs.organd 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<cake-php%2bunsubscr...@googlegroups.com>For > > more options, visit this group at > >http://groups.google.com/group/cake-php?hl=en Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
