Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sphinx index with many-to-many relation

Tags:

mysql

sphinx

I am trying to set up a Sphinx index with a basic many-to-many relation between artworks and genres:

artworks
---------------
id
title
description

genres
---------------
id
name

artwork_genres
---------------
artworks_id
genres_id

In my sphinx config file I have something like

source src_artwork {
    ...
    sql_query    = SELECT id, title, description FROM artworks
    sql_attr_multi = uint tag from query; SELECT id,name FROM genres
}

This is from the docs, as far as I can understand, on multi-valued attributes and sql_attr_multi

But obviously there is no mention of the tie table in there and I can't understand how that is brought into the config. I'd simply like for a search on "Impressionism" to result in artworks belonging to that genre (weighted as appropriate if the term is seen in the other fields)

like image 696
sbeam Avatar asked Jan 15 '11 05:01

sbeam


1 Answers

I would consider ignoring the attributes feature in this case. The simplest way to create a genre field by which to search artworks is to "de-normalise" the genres table into the sql_query.

In the FROM clause of your SQL query, you would JOIN the genres table to the artworks via the linking table. In the SELECT clause, you can then GROUP_CONCAT genres.name into a column, which becomes a Sphinx field to search on.

Your sql_query might look like this:

source src_artwork {
        ...
    sql_query    = SELECT a.id, a.title, a.description, GROUP_CONCAT( DISTINCT g.name SEPARATOR ' ') AS genre \
        FROM artworks AS a \
        LEFT JOIN artwork_genres AS ag ON ag.artworks_id = a.id \ 
        LEFT JOIN genres AS g ON g.id = ag.genres_id
        GROUP BY a.id;
}

Then a sphinx search for artworks looking for "impressionism" in the @genre field will return the "row".

like image 71
6 revs Avatar answered Nov 03 '22 23:11

6 revs