Search code examples
phpmysqlsphinx

sphinx faceted search on non-integer MVA pairs

I migrate my site search to sphinx and I facet few problems. One of them was facets of MVA strings.

I have two tables:

TABLE_A
id|col1|col2
1 | rand1 | rand2

and

TABLE_B
id|idtableA|key|value
1 | 1      | A1 | V1
1 | 1      | A2 | V2

In mysql i use to left join with group_concat table B.

select 
t1.*, group_concat(concat(t2.key,'-',t2.value) 
from 
TABLE_A t1 left join TABLE_B t2 on t2.idtableA=t1.id 
where t1.id=1 
group by t1.id

And this returns '1', 'rand1', 'rand2', 'A1-V1,A2-V2' Then process with explode in php to return facets. In sphinx I noticed I cant use strings in MVA atributes.

Can someone point me in some direction with my old grup_concat example please?

Solution

  • MVAs for strings is not supported.

    The String Attribute support is being rounded out, so it may someday be supported by not yet.

    Need to work out someway of storing integers in the attribute. Maybe can use a unique id per key/value pair, depends on your exact db, how easy that is.

    Or can perhaps using hashing (eg CRC32) to convert the string to a number.

    Or figure out some way to avoid need for MVA. Maybe can just store in a complete string attribute (ie the comma seperated list), but also make it a field, so can do filtering via the full-text query.