\i common/snippet_position_advanced_setup.sql

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE b.content @@@ 'test' OR a.name @@@ 'Rowling'
ORDER BY b.id, a.id;

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE b.content @@@ 'test' OR NOT(a.name @@@ 'Rowling')
ORDER BY b.id, a.id;

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE NOT(b.content @@@ 'test') OR a.name @@@ 'Rowling'
ORDER BY b.id, a.id;

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE NOT(b.content @@@ 'test') OR NOT(a.name @@@ 'Rowling')
ORDER BY b.id, a.id;

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE b.content @@@ 'test' AND a.name @@@ 'Rowling'
ORDER BY b.id, a.id;

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE b.content @@@ 'test' AND NOT(a.name @@@ 'Rowling')
ORDER BY b.id, a.id;

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE NOT(b.content @@@ 'test') AND a.name @@@ 'Rowling'
ORDER BY b.id, a.id;

SELECT
    b.id as book_id,
    a.id as author_id,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.score(a.id) as author_score,
    pdb.score(b.id) as book_score
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE NOT(b.content @@@ 'test') AND NOT(a.name @@@ 'Rowling')
ORDER BY b.id, a.id;

WITH book_snippets AS (
    SELECT
        b.id as book_id,
        pdb.snippet(a.name) as author_snippet,
        pdb.snippet_positions(a.name) as author_positions,
        pdb.score(a.id) as author_score,
        pdb.score(b.id) as book_score
    FROM books b
    JOIN authors a ON b.author_id = a.id
    WHERE b.content @@@ 'test' AND a.name @@@ 'Rowling'
)
SELECT
    bs.*,
    r.review,
    pdb.snippet(r.review) as review_snippet,
    pdb.snippet_positions(r.review) as review_positions,
    pdb.score(r.id) as review_score
FROM book_snippets bs
LEFT JOIN reviews r ON r.book_id = bs.book_id
WHERE r.review @@@ 'test' AND r.review @@@ 'snippet'
ORDER BY bs.book_id, r.id;

SELECT
    b.id as book_id,
    pdb.snippet(b.content) as book_snippet,
    pdb.snippet_positions(b.content) as book_positions,
    pdb.snippet(a.name) as author_snippet,
    pdb.snippet_positions(a.name) as author_positions,
    pdb.snippet(r.review) as review_snippet,
    pdb.snippet_positions(r.review) as review_positions,
    pdb.score(b.id) as book_score,
    pdb.score(a.id) as author_score,
    pdb.score(r.id) as review_score
FROM books b
JOIN authors a ON b.author_id = a.id
LEFT JOIN reviews r ON r.book_id = b.id
WHERE b.content @@@ 'test'
    OR a.name @@@ 'Rowling'
    OR r.review @@@ 'test'
    OR r.review @@@ 'snippet'
ORDER BY b.id, r.id;

\i common/snippet_position_advanced_cleanup.sql
