Filtering dependent data table, returns results from main table

Can I search in dependent data tables but returns results from main table?

This problem occurs where we have N x N relation in database like in example below: every user can have multiple locations but even if user has many location it is still one physical person.

I want to query sphinx with condition in table locations and return set should be from table users.

Query results will be filter by geo coordinates GEODIST() but its only information because its not the main subject of this question. Goal is for example: find persons who have location in 20 kilometers radius from some explicit point.

SQL structure

TABLE users
id PRIMARY KEY
name TEXT
etc...

TABLE locations
id PRIMARY KEY
name TEXT
coord_x FLOAT
coord_y FLOAT
etc...

TABLE user_location
user_id INTEGER FK
location_id INTEGER FK

Of course I can simply JOIN this 3 tables in Sphinx sql_query and filter this set but then I got duplicated persons when person have more than one location.

Any tips how to achieve this goal with Sphinx Search?

Solution

Of course I can simply JOIN this 3 tables in Sphinx sql_query and filter this set but then I got duplicated persons when person have more than one location.

Just add a GROUP BY to the sphinx query, then will only ever get own row per user.

You will need to make the users.id a sphinx attribute (so can group on it) and use a primary key from user_location as the sphinx document-id (so its unique)

(gets more complicated if have users that don't have locations, and still want to be able to search then - without the location filter. But it can still be done. Perhaps use a second source on the index, to find the unlocationed users)