Bad query in Sphinx MVA

0
votes

I have mysql database with applications table. Each application can have one or more developers, so in Sphinx i want to create multi value attribute to search apps by developers ids, i try to crate attribute in such way:

sql_attr_multi = uint developers from query; \ SELECT id, name FROM apps_developers WHERE app_id = $id

but when i try to build index sphinx server told me about error:

ERROR: index 'apps': multi-valued attr 'developers' query failed: Unknown column '$id' in 'where clause'.

in my mind $id must contain id value of each application from source query? as i'm right? why this error is happend?

1
sphinx

1 Answers

2
votes

The MVA query should return the results for ALL apps (ie the first column in the resultset, matches the document_id)

sql_attr_multi = uint developers from query; \
   SELECT app_id, id FROM apps_developers ORDER BY app_id