MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE user
    (`user_id` int, `name` varchar(1))
INSERT INTO user
    (`user_id`, `name`)
VALUES
    (1, 'W'),
    (2, 'x'),
    (3, 'Y'),
    (4, 'Z')

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE author
    (`auth_id` int, `name` varchar(1))
INSERT INTO author
    (`auth_id`, `name`)
VALUES
    (1, 'M'),
    (2, 'N'),
    (3, 'O'),
    (4, 'P')

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE preferred_author
    (`user_id` int, `auth_id` int)
INSERT INTO preferred_author
    (`user_id`, `auth_id`)
VALUES
    (1, 2),
    (1, 3),
    (1, 4),
    (2, 1)

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE book
    (`book_id` int, `title` varchar(14),
    fulltext index (`title`))
INSERT INTO book
    (`book_id`, `title`)
VALUES
    (1, 'Hello world 01'),
    (2, 'Hello world 02'),
    (3, 'Hello world 03'),
    (4, 'Hello world 04'),
    (5, 'Hello world')

Records: 5  Duplicates: 0  Warnings: 0


CREATE TABLE book_author
    (`book_id` int, `auth_id` int)
INSERT INTO book_author
    (`book_id`, `auth_id`)
VALUES
    (1, 2),
    (2, 3),
    (3, 4),
    (4, 1)

Records: 4  Duplicates: 0  Warnings: 0


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 u.user_id = pa.user_id AND u.name = 'W'
ORDER BY u.user_id IS NULL, relevance DESC


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

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