Overall, I've been disappointed with the MySQL full text searching.
First off, you have to use a MyISAM table, so you can't use
transactions.
Then you've got the results - the built-in stopword list, and the
minimum length of terms, which can't be changed in a normal install
(Our company normall does small-medium business websites, using shared
hosting, and we can't change everything we want to).
And then, do you use binary search? If you do then the results
ordering is erratic. If you don't then any term that appears in over
50% of the records is ignored. Think about searching the cookbook for
'model' and not getting any results...
Basically, after a good few attempts, I wrote off MySQL full text
search a while ago.
On Dec 8, 11:58 pm, the_woodsman <[EMAIL PROTECTED]> wrote:
> 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 cake-php@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
