Spinx search query with filtering

Question

I need to add additional filtering in Spinx query by post_category_name(String value) column, my current index:

  source min
    {
        type = mysql
        sql_host = localhost
        sql_user = root
        sql_pass =
        sql_db = test
        sql_query = select  p.id,  p.title, p.description, l.Latitude, l.Longitude FROM post p join location l on l.id = p.location_id
       // here I need filter by category name with post_id the same as 
       // p.id in table post
        sql_attr_float = Latitude
        sql_attr_float = Longitude
    }

I have 3 tables: post, location and post_category My DB relation:1) post with location one to one, 2) post with post_category is one to many.

post_category table, this table has the only two column: post_id and, post_category_name columns and I need search by this post_category_name when post_id in this table mutch by distance.

I filter by location with such query and it's works fine:

select *, geodist(48.712002, 2.677411, latitude, longitude) dist from serv1  where match('searchText*') and dist < 20 ;

after my select query, I want to have such columns in the result :

| id | latitude | longitude | post_category_name | dist

and filter by post_category_name.

so when I search I need something like this :

 select *, geodist(48.712002, 2.677411, latitude, longitude) dist from serv1  where match('searchText*') and dist < 20 and post_category_name in ("All", "Shop");

Plese, help me.

Answer 1

As you've got (potentially) multiple categories per post, have to choose how to index. 1) Could keep it like now, and have sphinx document per post, and then post_category_name would actully contain multiple values, if post in multiuple categories.

... or 2) could instead have one document per post&category. So there could be multiple results if a document is in multiple categories.

---

Option 1 is simpler, but 2 would ultimately be more flexible (as can search combined or not, but your sphinx query might need an GROUP BY to, to get one result per post)

But for now option 1...

sql_query = SELECT p.id, p.title, p.description, l.Latitude, l.Longitude, 
     GROUP_CONCAT(c.category_name) AS post_category_name \
  FROM post p  \
  INNER JOIN location l ON (l.id = p.location_id) \
  LEFT JOIN category c ON (c.post_id = p.id) \
  GROUP BY p.id \
  ORDER BY NULL

sql_field_string = post_category_name 

... puts the category as BOTH a string attribute (for retrieval) and a field (for matching)

select id, post_category_name , geodist(48.712002, 2.677411, latitude, longitude) dist 
  from serv1 
  where match('searchText* @post_category_name All|Shop') and dist < 20;

While you might be able to use post_category_name attribute in the WHERE, its generally better if possible to filter using full-text query (field).