CODEWITHSUNDEEP
phpmysqldatabase
Bipul Roy
Bipul Roy

Reputation: 566

Mysql query for ( full text)searching with multiple order by conditions

I have following tables:

    user
user_id | name
1          W
2          x
3          Y
4          Z

    author
auth_id | name
1          M
2          N
3          O
4          P

    preferred_author
user_id | auth_id
1          2
1          3
1          4
2          1

    book
book_id | title
1          Hello world 01
2          Hello world 02
3          Hello world 03
4          Hello world 04
5          Hello world 

  book_author
book_id | auth_id
1          2
2          3
3          4
4          1

Now when a user W searches for a book named hello world i want show the books which are by user's preferred author, first and then the other results according to relevance. What I tried was selecting all the ids of preferred authors and saved in a php array

PHP:
Say, $p_auth_ids = (2, 3, 4);

Then i ran a query for searching:

$query = "SELECT b.book_id, b.title, 
MATCH(b.title) AGAINST("hello world" IN BOOLEAN MODE) AS relevance 
FROM book AS b 
INNER JOIN book_author AS ba ON b.book_id=ba.book_id
WHERE ba.auth_id IN ('".$p_auth_ids."') 
AND 
MATCH(b.title) AGAINST("hello world" IN BOOLEAN MODE)
ORDER BY relevance DESC"

Output should be:

book_id | title

1          Hello world 01
2          Hello world 02
3          Hello world 03
5          Hello world 
4          Hello world 04

Did not work. So what should be the query for what i want? Thanks.

Upvotes: 1

Views: 97

Answers (1)

Nick
Nick

Reputation: 147166

I think this query will give you what you want. It JOINs the book table to the preferred_author (via book_author) and then to the user table. In the JOIN to user, we add the user.name = 'W' condition so that only that user's preferred authors have a non-null user_id in the result set. Then we can order on user_id IS NULL (which will evaluate to 0 for books by the user's preferred authors and 1 otherwise) followed by relevance DESC:

SELECT b.book_id, b.title, MATCH(b.title) AGAINST("hello world" IN BOOLEAN MODE) AS relevance 
FROM book b
LEFT JOIN book_author ba ON ba.book_id = b.book_id
LEFT JOIN preferred_author pa ON pa.auth_id = ba.auth_id
LEFT JOIN user u ON pa.user_id = u.user_id AND u.name = 'W'
ORDER BY u.user_id IS NULL, relevance DESC

Output (note that all your titles are too similar to generate different relevance values so it doesn't sort exactly as you specified, but if you change the titles in the demo you will see it working):

book_id     title           relevance
1           Hello world 01  0.000000003771856604828372
2           Hello world 02  0.000000003771856604828372
3           Hello world 03  0.000000003771856604828372
4           Hello world 04  0.000000003771856604828372
5           Hello world     0.000000003771856604828372

Demo on dbfiddle

Upvotes: 1

Related Questions