sphinx and one-to-many associations

Question

The examples I've seen for sphinx search don't really use joins. If I had a schema like this (one book has multiple reviews),

create table book ( id int auto_increment, title varchar(200), summary longtext );
create table reviews ( id int auto_increment, book_id int, review longtext );

What sort of query should I give to sphinx so that I can search for the words in the review and it will return the corresponding book. If I use a regular inner join each review will be a single document and that doesn't correspond to what's visible on the site (single page contains book info and all reviews).

What do you suggest? Should I create a denormalized table for the search documents and use the rendered page as input e.g.:

create table sphinx_input ( 
   id int, -- corresponds 1..1 to book.id
   contents longtext -- everything on the page
);

and modify this for every change to books and reviews?

Answer 1

you could just allow it to index reviews as usual and then perform the join from review ids to book ids in mysql after the results come back.

Answer 2

You'll want to use GROUP_CONCAT, to collect all your data for reviews into the single result in Sphinx's sql_query. Perhaps something like the following:

SELECT books.id, books.title, books.summary,
  GROUP_CONCAT(reviews.review SEPARATOR ' ') AS reviews
FROM books LEFT OUTER JOIN reviews ON reviews.book_id = books.id
WHERE books.id >= $start AND books.id <= $end
GROUP BY books.id, books.title, books.summary

I'd recommend using an outer join so books without reviews are still returned as well.