1

I am currently struggling against a problem I can't fix, probably because of my scarce knowledge of the Drupal API.

I have a taxonomy tree that looks like the following one:

    SHOE BRANDS 143     
         KIDS   151 
         MEN    152 
            STIVALE/BOOT    164
            SANDAL  165
            PANTOFOLA/LOAFER    198
            MOCASSIN    199
            DERBY   200
            FRANCESINA/OXFORD   201
            POLACCHINO/ANKLE BOOT   202
            SNEAKER 203
        OUTDOOR/SPORT   153 
        WOMEN   154 
            STIVALE/BOOT    166
            SANDAL  167
            POLACCHINO/ANKLE BOOT   189
            SNEAKER 190
            DERBY   191
            FRANCESINA/OXFORD   192
            PANTOFOLA/LOAFER    193
            SABOT   194
            MOCASSIN    195
            BALLERINA   196
            DECOLLETEE/PUMP 197

What I need to do is to populate a select box with the child elements of the "SHOE BRANDS" parent (which should be "KIDS" "MEN" "OUTDOOR" and "WOMEN"). After that I will populate a second select box with an Ajax call passing the tid, to get all the different shoe types.

The query I'm using to populate the first select is this one (and the problem rises here):

SELECT *
FROM node 
INNER JOIN term_node AS tn 
ON node.vid = tn.vid 
LEFT JOIN content_type_extra_content AS xc 
ON node.vid = xc.vid 
WHERE tn.tid IN (SELECT th.tid FROM term_hierarchy AS th WHERE th.parent = '143')
GROUP BY tn.tid

Note the '143' which is the tid of the "SHOE BRANDS" term.

Don't know why, but I only get as output "KIDS" and "OUTDOOR" (which are the only terms with no child elements.

Am I missing something in my syntax?

Improve this question

2 Answers 2

Reset to default
1

have you reviewed taxonomy_get_tree() documentation? http://api.drupal.org/api/drupal/modules--taxonomy--taxonomy.module/function/taxonomy_get_tree/6 I guess that taxonomy_get_tree() documentation and code function could be a guidance.

taxonomy_get_tree ...

Return value

An array of all term objects in the tree. Each term object is extended to have "depth" and "parents" attributes in addition to its normal ones.

Improve this answer
2
  • Yeah I've read it but I can't figure out how that should help. I already know the tree structure and all ids of the single terms. My problem is that my query only returns TWO entries instead of four, and the thing that makes me wonder that maybe is the query itself wrong is that the returned values are the ones without child elements. So my question could be read as: how do I fetch all the childs of the first parent in a three depth taxonomy tree?
    – Matteo Pirelli
    Oct 4, 2011 at 9:03
  • I suggested taxonomy_get_tree() because I saw that taxonomy_get_tree() with $max_depth return terms acording some level. then was suggested like an alternative to test your issue (to try to return four entries).
    – cigotete
    Oct 5, 2011 at 14:46
0

I would suggest you remove the none-relevant LEFT join, and use only joins, rather then subselects.

SELECT td.name, td.tid
FROM node n
INNER JOIN term_node AS tn ON node.vid = tn.vid
INNER JOIN term_data AS td ON tn.tid = td.tid
INNER JOIN term_hierarchy AS th ON tn.tid = th.tid
WHERE th.parent = '143'
GROUP BY tn.tid

The GROUP BY might provide a problem to, so try removing that to see if you get more results. You will probably see carthesian products then, which you can reduce with a SELECT DISTINCT() instead of the select all.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.