Take the 2-minute tour ×

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:


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


| 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


share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

sql_query = 
    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.

share|improve this answer
I got it to work that way. But had to create 2 indexes. The first index stayed how it was and the 2nd indexes on the user_id to get all orders related to a specified user. Thanks! –  Jonas Wouters Jan 21 '10 at 23:08
that's really the beauty of Sphinx... you can index your data many different ways to get speedy and convenient access to many different facets of it. –  Ty W Jan 22 '10 at 13:59
add comment

Your Answer


