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)
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".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With