- Modify the file
/usr/local/sphinx/etc/sphinx-blog.confto add the code as highlighted next:source blog { type = mysql sql_host = localhost sql_user = root sql_pass = sql_db = myblog sql_query = SELECT id, title, content, UNIX_TIMESTAMP(publish_date) AS publish_date, author_id FROM posts sql_attr_uint = author_id sql_attr_timestamp = publish_date sql_query_info = SELECT id, title FROM posts WHERE ID=$id } index posts { source = blog path = /usr/local/sphinx/var/data/blog docinfo = extern charset_type = sbcs } indexer { mem_limit = 32M } - Run the
indexeragain to re-index the data:$ /usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/sphinx-blog.conf --all
- Search for all posts containing the term php, written by Aditya Mooley (author_id
= 2):$ /usr/local/sphinx/bin/ search --config /usr/local/sphinx/etc/sphinx-blog.conf -f author_id 2 php
We modified the sphinx-blog.conf file, and changed the sql_query to fetch the author_id and publish_date along with other fields. We also added two new options; sql_attr_unit and sql_attr_timestamp to specify the two attributes. author_id is an unsigned integer, while publish_date is TIMESTAMP.
After that we re-indexed using the indexer command and this overwrote the previously created index file at /usr/local/sphinx/var/data/blog.
Now to filter our results by author_id, we specified the -f option to the search command that stands for filter. We filtered the results so that only those documents whose author_id attribute is 2 are returned.
Note
Filtering on timestamp attributes will be covered in Chapter 4, Searching, as that cannot be done using command line search utility.
Similarly if you want to filter the search results so that all documents, which contain the term "programming" but written by Amit Badkas (author_id 1) are returned—issue the following command:
$ /usr/local/sphinx/bin/search --config /usr/local/sphinx/etc/sphinx-blog.conf -f author_id 1 programming
We have already discussed filtering our results by authors. It was straight-forward since each post has only one author. However, what if we want to filter our results by categories? Remember, each post can be in one or more categories and we can only assign one value to an attribute. MVA comes to our rescue in these scenarios. As discussed earlier, an MVA can hold multiple values and we can filter our posts based on any of the categories they belong to.