Just to mention, MySQL supports some good search directly, if you're
willing to store your searchable text (or index) in a FULLTEXT
field...
And there's a sound matching function too!
On Dec 8, 8:13 pm, Clay <[EMAIL PROTECTED]> wrote:
> Yes that makes sense.
>
> Unfortunately it still does not solve my main problem which is how to
> (in a single query) search fields of hasMany and hasAndBelongsToMany
> related models as well as those of the base model and its belongsTo
> and hasOne relations. Simply turning recursive up does not work
> because though those results are returned the tables and their columns
> are still not in the generated SQL.
>
> I could attempt to trick Cake into doing those joins by binding
> temporarily as a hasOne instead of a hasMany as I have seen done, but
> this seems like a hack...
>
> I am still looking at Sphinx and hoping it will solve all my problems!
>
> -Clay
>
> On Dec 8, 10:16 am, grigri <[EMAIL PROTECTED]> wrote:
>
> > The implode('+', ...) does exactly what you'd think - creates a sum of
> > terms.
>
> > Each entry in the array is a 'x LIKE y' expression, which will return
> > 1 if it's true and 0 if it's false.
>
> > Therefore to count how many matched, you just need to add them
> > together, which is what the code does.
>
> > For example the order bit for the above example will be
>
> > ORDER BY ((name LIKE '%red%')+(name LIKE '%maple%')) DESC, ...
>
> > For 'Red Maple' this will be 1+1=2
> > For 'Aardvark Maple' this will be 0+1=1
>
> > Hope this makes sense
>
> > (I'm still looking at Sphinx myself, looks good so far)
>
> > hth
> > grigri
>
> > On Dec 8, 3:26 pm, Clay <[EMAIL PROTECTED]> wrote:
>
> > > Thanks for the input!
>
> > > Actually I am already doing something similar to this but I didn't
> > > know how to count the matches. I guess I am confused about your use of
> > > implode on +, I assume this is something MySQL will parse and
> > > understand?
>
> > > If that is the case then I should give this a shot since it seems like
> > > only a minor modification to my current code. Although now that I look
> > > at it again it will not be quite that simple, since Cake does not JOIN
> > > LEFT the models which are hasMany relationship...
>
> > > Thanks also to all who recommend Sphinx! It seems very powerful and I
> > > am going to read up on the documentation to see how well it will fit
> > > my needs!
>
> > > -Clay
>
> > > On Dec 8, 3:18 am, grigri <[EMAIL PROTECTED]> wrote:
>
> > > > Try this:
>
> > > > $terms = explode(' ', 'Red Maple'); // Examplesearchterm
> > > > $conditions = array();
> > > > $order = array();
> > > > foreach ($terms as $term) {
> > > > $conditions[] = array('CommonName.name LIKE ' => '%'.$term.'%');
> > > > $order[] = '(CommonName.name LIKE \'%'.$term.'%\')';
>
> > > > }
>
> > > > $order = array('('.implode('+', $order).') DESC', 'CommonName.name
> > > > ASC');
> > > > $conditions = array('or' => $conditions);
>
> > > > $results= $this->CommonName->find('all', array(
> > > > 'order' => $order,
> > > > 'conditions' => $conditions
> > > > ));
>
> > > > This will ensure that each result contains at least one of the
> > > > keywords.
>
> > > > The primaryorderingof each result is based on how many keywords it
> > > > matches.
>
> > > > The secondary is based on the normal ascending order.
>
> > > > So, if you had 'Red Maple', 'Bored Maple', 'Red Something', 'Aardvark
> > > > Maple'
>
> > > > and you searched for 'Red Maple'
>
> > > > Then the scoring would be:
>
> > > > 'Red Maple' : 2 (matches 'Red' and 'Maple')
> > > > 'Bored Maple': 1 (matches 'Maple')
> > > > 'Aardvark Red': 1 (matches 'Red')
> > > > 'Aardvark Maple': 1 (matches 'Maple')
>
> > > > So theorderingwould be
>
> > > > Red Maple, Aardvark Maple, Aardvark Red, Bored Maple
>
> > > > This works with pagination too, you need to assign the order in the
> > > > pagination array directly:
>
> > > > $this->paginate['Model']['order'] = $order;
> > > > $results= $this->paginate('Model', $conditions);
>
> > > > With some SQL jiggery-pokery, it's also possible to order them based
> > > > on the number of occurences (probably not relevant here) or the length
> > > > of each matched keyword ('Maple' matches higher than 'Red' because
> > > > it's longer, so 'Bored Maple' would appear before 'Aardvark Maple').
>
> > > > Not sure how efficient a query this is, but it seems to work
>
> > > > hth
> > > > grigri
>
> > > > On Dec 6, 9:22 pm, Clay <[EMAIL PROTECTED]> wrote:
>
> > > > > My app has the following:
>
> > > > > Genus hasMany Species
> > > > > Species hasMany CommonName
>
> > > > > and the corresponding belongsTo relationships as well. Each of these
> > > > > models has a name field, and Genus and Species have several other
> > > > > fields.
>
> > > > > Say for example I have a Species Acer rubrum. This Species belongsTo
> > > > > Genus Acer and has a CommonName Red Maple.
>
> > > > > I want my users to be able tosearchthe database for "Red Maple" and
> > > > > get as a result Species Acer rubrum (with the CommonName Red Maple
> > > > > highlighted to show that's what it matched. I can do this already.
> > > > > Yay.
>
> > > > > However if I have another Species with CommonName Bored Maple, I want
> > > > > to display this result too, but lower in the list than Red Maple.
> > > > > Since Bored comes before Red alphabetically I can't just order by
> > > > > CommonName.name to get the result I want.
>
> > > > > So what I know I could do is a series of finds:
> > > > > 1) Find exactmatch: CommonName.name => $searchStr
> > > > > 2) Find startingmatch: CommonName.name LIKE => $searchStr . '%'
> > > > > 3) Find internalmatch: CommonName.name LIKE => '%' . $searchStr . '%'
>
> > > > > And then merge them into myresultsarray.
>
> > > > > However, this seems very inefficient. Is there a way I can use cake's
> > > > > find() function to do this all in one go whileorderingthem in order
> > > > > ofbestmatch? I have thought about this for quite some time and done
> > > > > some research but I can't seem to find any clever solution.
>
> > > > > I might be able to do a custom query with a bunch of SELECTs as above
> > > > > combined with UNION or something, but this would be a pretty complex
> > > > > query and not really much more efficient than what I already know how
> > > > > to do.
>
> > > > > Thanks in advance any genius here who has a solution!
>
> > > > > -Clay
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
