Sphinx: Filtering MVA on multiple fields

Question

I would like to be able to filter my orders on an amount range AND a specific user_id The expected result would be to get only the orders where the specified user did a bid on in a certain amount range.

For example there is an order with id 1. User 5 bid on it for an amount of 200. Now if I want to filter I set the user_id filter to 5 and set a range filter on amount for 150-250.

How do I configure sphinx to be able to filter in that way?

I have the following tables:

Order:

+-------------+-----------------------+
| Field       | Type                  |
+-------------+-----------------------+
| id          | mediumint(8) unsigned |
| title       | varchar(100)          |
| description | text                  |
+-------------+-----------------------+

Bid:

+--------------+-----------------------+
| Field        | Type                  |
+--------------+-----------------------+
| id           | mediumint(8) unsigned |
| order_id     | mediumint(8) unsigned |
| user_id      | mediumint(8) unsigned |
| amount       | mediumint(9)          |
+--------------+-----------------------+

I've tried the following in the sphinx configuration. But I cannot set the user_id as leading. The result is that I get all the orders where there are bids on in that amount range (from all the users), and I get all the orders where the user did a bid on.

sql_attr_multi  = uint amount from query; SELECT order_id as id, amount FROM bids
sql_attr_multi  = uint user_id from query; SELECT order_id as id, user_id FROM bids

Thanks

Answer 1

It might be better to index individual bids to use for this functionality.

sql_query = 
  SELECT
    b.id
    b.order_id
    b.user_id
    b.amount
    o.title
    o.description
  FROM
    bid b JOIN
    order o ON b.order_id=o.id;
sql_attr_uint = order_id
sql_attr_uint = user_id
sql_attr_uint = amount

to filter based on a particular user:

SetFilter("user_id", array(5));

to filter based on a bid range from 150 to 250:

SetFilterRange("amount", 150, 250);

to filter based on the order id:

SetFilter("order_id", array(1));

these can be mixed and matched as needed to select the bids you want to see. Now that the index is using the bid ids Sphinx will return them to you as the result set and you can use them as needed within your subsequent MySQL queries.