Sphinx index with many relationships to many

Question

I am trying to tune the Sphinx index with a basic many-to-many relationship between works 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, in ambiguous attributes and sql_attr_multi

But, obviously, there is no mention of the connection table, and I cannot understand how this is introduced into the configuration. I just would like the search for "Impressionism" to lead to works belonging to this genre (weighted, if necessary, if this term is displayed in other fields)

Answer 1

. - "-" sql_query.

FROM SQL- . SELECT GROUP_CONCAT genres.name , Sphinx .

sql_query :

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;
}

sphinx "" @genre "".