You have to include a the fields array in your query, and add a new field
for the score column. The only drawback is that once you supply a fields
array to your query cake no longer builds it for you automatically, so, in
order to achieve the SELECT * effect you'd have to include all the fields
from your movie table. Naturaly you can do this looping the Model:;_schema
hash. Here's a method I use (place it in your model):
function fulltextsearch($query) {
$fields = array();
$conditions = "MATCH(Movie.movie_name) AGAINST('$query')";
foreach (array_keys($this->_schema) as $field) {
$fields[] = sprintf("%s.%s",$this->alias,$field);
}
$fields[] = sprintf("%s AS score",$conditions);
$order = array('score DESC','Movie.year DESC');
return $this->find('all', compact($fields,$conditions,$order));
}
On Fri, Aug 21, 2009 at 9:36 AM, Shaun <[email protected]> wrote:
>
> I would like to edit the following MySQL statement to use CakePHP
> conventions:
>
> SELECT *,
> MATCH(movie_title) AGAINST('$keywords') AS score
> FROM movies
> WHERE MATCH(movie_title) AGAINST('$keywords')
> ORDER BY score DESC, movie_year DESC
>
> The idea is to order the movies by search relevancy, but when there is
> a Hollywood remake of an old movie (same title, same relevancy), the
> newer movie should appear above the older movie, sorted by movie_year
> DESC.
>
> I have a model called Movie. As of now, the database is being queried
> like this:
>
> $conditions = array("MATCH(Movie.movie_name) AGAINST('$keywords')");
> $matches = $this->Movie->find('all', array('conditions' =>
> $conditions));
> $this->set('movies', $matches);
>
> The results are ordered by the fulltext relevancy, but how can I order
> by both fulltext relevancy and movie year? I'm not sure how to assign
> the fulltext results to a fake field called score. Thanks.
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
