Challenging fulltext query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Challenging fulltext query

Post by voltrader »

What I'm trying to do is sort by rating but keep relevancy of what's searched for.

I'm finding it hard to articulate exactly what's happening. Please bear with me.

Say I have the following records in the "city" field:

city
San Francisco, CA
San Francisco
San Jose
San Dimas

Their corresponding ratings are:

ratings
3
2
5
4

User searches for "San Francisco" and wants to sort by rating. I use the following fulltext query to pull up the results:

Code: Select all

SELECT *, (MATCH(details) AGAINST ('$search_term')) AS score
WHERE (MATCH(details) AGAINST ('$search_term')) ORDER BY ratings DESC, score DESC
This returns:
San Jose 5
San Dimas 4
San Francisco, CA 3
San Francisco 2

When I would like:
San Francisco, CA 3
San Francisco 2

I want to somehow use the alias variable 'score' to order the results as well as take ORDER BY ratings into account.

How can I accomplish this?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Yo,

didnt i answer this question?

їcode]ODERBY Score Desc, rating Ascї/code]

since score is first it will take precedence in sorting then rating
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

In practice the sort is among tens of thousands of records, so almost every score will be unique.

As a result a secondary sort by rating will not do anything.

Perhaps I can "tune-out" score by making them 1 decimal place.
Post Reply