CODEWITHSUNDEEP
mysqlselectmany-to-manysphinx
Rémi Barbalat
Rémi Barbalat

Reputation: 27

MySql SELECT many to (many to many)

How can I join two cascading many-to-many relationships ?

I have 5 tables :

ITEMS            : id /     name

ITEMS_TAXONOMIES : id /    item_id    / taxonomy_id

TAXONOMIES       : id /  attribute_id / adjective_id

ATTRIBUTES       : id /     name

ADJECTIVES       : id /     name

I need a single SELECT to retrieve all associated attribute-adjective pairs for a specific item.

I'm working with Sphinx search engine and I need to look for WORDS associated with the items.

Thank you for your hints...

Upvotes: 0

Views: 259

Answers (1)

barryhunter
barryhunter

Reputation: 21091

(Copy of my answer already made on the sphinx forum)

The simplest solution I think would be

sql_query = SELECT i.id, i.name , \
                    GROUP_CONCAT(CONCAT(at.name,' ',ad.name)) as taxonomies \
                    FROM items i \
                          LEFT JOIN items_taxonomies it ON (i.id = it.item_id) \
                          LEFT JOIN taxonomies t ON (t.id = it.taxonomy_id) \
                          LEFT JOIN attributes at ON (at.id = t.attribute_id) \
                          LEFT JOIN adjectives ad ON (ad.id = t.adjective_id) \
                    GROUP BY i.id \
                    ORDER BY NULL

Just to search the words you dont need to use sql_attr_multi. You might get a slight performance boost doing the indexing via sql_joined_field, but the above is simpler so try that first. Or you could put the attributes and adjectives in seperate fields. But the above has the benefit can search "attribute bar" to get a specific taxonomy.

You could stick taxonomy_id into a MVA if you want to group or get back the list of relevent taxonomies for the document in the result.

Upvotes: 1

Related Questions