Will MVA work with text fields in Sphinx?

Question

I am indexing an id from a look-up-table in my sphinx configuration.

sql_attr_multi = uint customer_type_id from ranged-query; \
SELECT f.orderID AS ID, f.typeID \
FROM LUT_customerType f \
WHERE f.orderID BETWEEN \$start AND \$end \
ORDER BY f.orderID; \
SELECT MIN(orderID), MAX(orderID) FROM LUT_customerType

this returns, for example, in a given field:

customer_type_id: 3,5.15

As the LUT table simply stores orderID and typeID

However, I now want to index the Type Name (I was using MySql to query it but this will be more efficient I hope).

I tried doing this:

sql_joined_field = customer_type_name from ranged-query; \
SELECT f.orderID AS ID, n.Name \
FROM LUT_customerType f \
INNER JOIN customerType_Names n ON f.typeID= n.ID \
AND f.orderID BETWEEN \$start AND \$end \
ORDER BY f.orderID; \
SELECT MIN(orderID), MAX(orderID) FROM LUT_customerType

In other words, I replicated the working MVA Select with two minor changes:

1. I added an additional join to get the Type Name
2. I removed the unit since I didn't want this to be an integer

I tried the Select in MySql and it works as expected.

Yet neither the field nor values show up, not in individual records when selecting from SphinxQL

`Select * from idx_Table`

Nor when checking the Sphinx Index Structure

`Desc idx_Table`

Answer 1

Well sql_joined_field is making a Field ... fields are full-text searchable fields.

THey are NOT stored in the index, in such a way they can be 'retrieved' (only used in queries).

Attributes on the other hand, like MVA, are retrievable, as they stored by document-id.

Sphinx doesn't have a Multi-Value String Attribute, or even scalar string Attribute that can be built with a 'query' (like mva/joined-field).

if want a joined string attribute use GROUP_CONCAT mysql aggregation function, in the main sql_query - along with a GROUP BY

(sql_attr_multi can also parse a comma seperated string, eg built with GROUP_CONCAT from a field in the main sql_query. So can build both the mva and joined-string-attribute, in the main query, without needing seperate query)

---

For example...

sql_query = SELECT o.orderID, description, \
    GROUP_CONCAT(f.typeID) as customer_type_id, \
    GROUP_CONCAT(n.Name SEPERATOR ' ' ORDER BY n.ID) AS customer_type_name \
    FROM orders o \
    LEFT JOIN LUT_customerType f ON (f.orderID = o.orderID) \
    LEFT JOIN customerType_Names n ON (f.typeID= n.ID) \
    GROUP BY o.orderID \
    ORDER BY NULL 

sql_attr_multi = uint customer_type_id from field;

sql_field_string = customer_type_name 

(could use sql_attr_string instead, if only want attribute, dont actully want it as a field too)

... in practice, possibly make it a ranged query too, like your small query was.

Also note the ORDER BY inside the GROUP_CONCAT. As sphinx will internall reorder the MVA into numerical order, this ensures that the text attribute has the same ordering.