SphinxSearch - 使用sql_attr_multi的setFilter

Question

It is possible to set a filter/condition in the sphinx api to add an 'AND' condition to the config select? (or to act like an 'AND' condition) ?

SetFilter doesnt work as I expected, because I'm using sql_attr_multi, it creates an array with found matches...and the sort defined attributes are overwritten by the newest ones.

If I set 'AND condition' in the config select it works properly, but I need to use it dinamically.

Or maybe to use the sql_query in php to set the conditions dinamically?

OR setSelect ("*, AND condition")

Edit

I have 3 tables

-companies table
-projects table
-companies_projects table (company id, project_id, company_package)

The same company could be on multiple projects.

The company package could be different on every project (for sorting).

sql_attr_multi = uint project from query; SELECT company_id, project_id FROM companies_projects

It finds the right companies but if the company is on multiple projects, the project attr becomes (8, 10).

On project no. 8 - the package is 2,
On project no. 10 - the package is 1

I apply the filter

$sphinxClient->setFilter('project', array(8));

The problem is that the company_package is overwritten by the last entry (in the database) and I don`t know why.

So the company_package for project no. 8 should be 2, I`m getting 1 instead, witch is the package for project no. 10.

If I add WHERE project_id = '8' in sql_query is working fine, but I need a dynamic solution so I don`t need to create a config file for every project

Edit:

sql_query = \
     SELECT \
     id, company, \
     company_package, UNIX_TIMESTAMP(date) AS date \
     FROM companies AS c \
     INNER JOIN companies_projects AS cp ON c.id = cp.company_id

companies table: id, company

companies_projects: project_id, company_id, company_package, date

Edit

 [1] => Array
            (
                [weight] => 1
                [attrs] => Array
                    (
                        [company_package] => 2
                        [date] => 1367224201
                        [project] => Array
                            (
                               [0] => 8
                            )
                    )
            )
 [2] => Array
            (
                [weight] => 1
                [attrs] => Array
                    (
                        **[company_package] => 1** it should be 2
                        [date] => 1367224202
                        [project] => Array
                            (
                                [0] => 8
                                [0] => 10
                            )
                    )
            )

I atached an example. It finds the right companies.

The first key is ok because the company no. 1 is just on project no.8

The second key is not good because the company no. 2 is on both no. 8 and 10 projects like this:

company 1, project 8, package = 2

company 2, project 8, package = 2

company 2, project 10, package = 1

So, the companies are ok, but the package is overwriten from the company 2, project 10, package = 1 If I delete this record...or if I add AND project_id=8 everything is ok

Answer 1

Ah, you getting just one document per company.

You can consider that the sql_query has GROUP BY id tacked on the end. (it doesnt really, but sphinx will only create one document per id)

The MVA works, because it has it doesnt have a GROUP BY on it.

I suspect the easiest would be to just have one sphinx document per company/project combo. Ie you in effect directly indexing the companies_projects, not the companies table itself.

sql_query_pre = SET @id=1
sql_query = SELECT @id:=@id+1, company_id, project_id, company, \
   company_package, UNIX_TIMESTAMP(date) AS date \
   FROM companies_projects AS cp \
   INNER JOIN companies AS c ON (c.id = cp.company_id)
sql_attr_unit = company_id
sql_attr_unit = project_id

(no need for the MVA)

Then you can setFilter on project_id (or company_id) and get the right company_package.

(This includes making a fake document ID - because you have no simple key on companies_projects you can use)