Filter many-to-many int value with Sphinx

Question

Using the SphinxAPI in PHP

I have 2 tables with a many to many relation via a third one. Gamers can attend multiple events.

Gamers
- id (UINT)
- username (STRING)
- category (UINT)
- lat (FLOAT)
- lng (FLOAT)

Events
- id (UINT)
- name (STRING)

gamers_events
- gamer_id (UINT)
- event_id (UINT)

I saw about MVA with Sphinx, but I am not sure how this is supposed to work.

This is what I have in the gamer index

sql_query           = SELECT g.id\
                            , g.username\
                            , g.category\
                        FROM gamer g\
                        /* some LEFT JOIN with other tables */
                        GROUP BY g.id

sql_attr_multi      = uint event from query; SELECT id, name FROM event

I am not sure to see how to join the events table to the gamers table.

I would like to SetFilter by the id of events when searching for gamers. Not by name of events, just the id.

Answer 1

Either..

sql_query = SELECT g.id, \
      GROUP_CONCAT(ge.event_id) AS event, \
       ...
      FROM gamer g\
          LEFT JOIN gamers_events ge ON (ge.gamer_id = g.id) \
          /* some LEFT JOIN with other tables */
      GROUP BY g.id \
      ORDER BY NULL
sql_attr_multi      = uint event from field; 

(sphinx can extract it from the column)

OR (sql_query unchanged)...

sql_attr_multi      = uint event from query; SELECT gamer_id, event_id FROM gamers_events ORDER BY gamer_id

You dont need the events table in either case, just the gamers_events table.