How to sort data by value from attached table in sphinx search?

Question

MySQL

Good day.
There is an index (sphinx) with a sufficiently large query (many attached tables). I need to sort data by value from an attached table. In the query, I add ORDER BY table.field DESC, but unsorted data comes to PHP. I have read that it is necessary to create an attribute, but I can't create an attribute by value from an attached table. Can you tell me how to sort the data?
Request example:

SELECT c.id, c.icof, c.firma, a.textadr, \
r.textz, cc.value, cc.title, cai.description \
FROM companies as c \
LEFT JOIN addresses as a ON c.icof = a.icof \
LEFT JOIN rosformaf r ON c.rosformaf = r.kodzaz \
LEFT JOIN company_contacts as cc ON c.id = cc.company_id \
LEFT JOIN company_add_info as cai ON c.id = cai.company_id \
LEFT JOIN subscriptions as s ON c.id = s.company_id \
ORDER BY s.active DESC

Thank you.

Answer 1

Good afternoon
What you threw off is a query with the help of which Sphinx receives data to build an index, and it does not matter whether the data was sorted or not.
You can create an attribute for attached tables (it is added in the same way as not for attached tables)
In the source description, add the line:
sql_attr_uint = subscriptionActive (or any other type, depending on your needs)
And when indexing, tell the sphinx what it should put in the subscriptionActive attribute:

SELECT c.id, c.icof, c.firma, a.textadr, \
r.textz, cc.value, cc.title, cai.description \
,s.active AS 'subscriptionActive'\
FROM companies as c \
LEFT JOIN addresses as a ON c.icof = a.icof \
LEFT JOIN rosformaf r ON c.rosformaf = r.kodzaz \
LEFT JOIN company_contacts as cc ON c.id = cc.company_id \
LEFT JOIN company_add_info as cai ON c.id = cai.company_id \
LEFT JOIN subscriptions as s ON c.id = s.company_id \
ORDER BY s.active DESC

Now when you form a request to the sphinx, tell it what to sort by the subscriptionActive attribute and happiness will come =)
In general, everything is quite well described in the documentation:
sphinxsearch.com/docs/current.html#attributes
I also recommend Igor Chakrygin's blog for review :
chakrygin.ru