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
-~----------~----~----~----~------~----~------~--~---
