Filter by column

[dbrgn]

Problem

When filtering by a specific column, the column is ignored.

>>> CompanyIndex.objects.filter(company_name__search='my company')
SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@* my company') LIMIT 21

In this case, the correct SphinxQL query would be:

SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@company_name my company') LIMIT 21

This default @* at the beginning of the query causes problems when using the @-Syntax in the filtering query itself.

>>> CompanyIndex.objects.filter(company_name__search='@company_name my company')
SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@* @company_name my company') LIMIT 21

This fails of course.

Suggestion

I'd suggest that the standard query filtering accepts only basic syntax, no field names. It should also limit the filtering to the specified column name.

>>> CompanyIndex.objects.filter(company_name__search='my company')
SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@company_name my company') LIMIT 21

Additionally, you could add an unfiltered matching function, e.g.

>>> CompanyIndex.objects.match('@* software @city new york')

I guess this could be done by adding the function to the custom manager, but I'm not 100% sure about that.

[btimby]

This is a known issue. I reached a point where I had to decide between two options.

1. Only allow searching a single field.
2. Allow searching only ALL fields.

https://github.com/btimby/django-sphinx-db/blob/master/django_sphinx_db/backend/sphinx/compiler.py#L41

I chose the later, since that is my use-case.

The ultimate solution will be to extend the search handling so that it can be used multiple times. For example, this is illegal:

MyModel.objects.filter(column_one__search='foo', column_two__search='bar')

Since search can only be used once. This means that searching more than one full-text field is not possible. I opted to default to searching @* (and ignoring the given field name) so that searching more than one field was not only possible but the default.

The ultimate goal will be allowing that syntax as well as:

MyModel.objects.filter(all__search='foo bar')

So that you can search by individual full-text fields or all of them. My use-case dictates that I be able to search all of them. I have no need for searching individual columns. When I have time, I will implement this, but until then, as always, patches are gratefully accepted.