1

I try to figure out the meaning of View's sql statement.

This following is Views auto-generate sql statement:

SELECT taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name
FROM 
{taxonomy_term_data} taxonomy_term_data
LEFT JOIN {taxonomy_term_hierarchy} taxonomy_term_hierarchy ON taxonomy_term_data.tid = taxonomy_term_hierarchy.tid
INNER JOIN {taxonomy_term_data} taxonomy_term_data_taxonomy_term_hierarchy ON taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE (( (taxonomy_vocabulary.machine_name IN  ('book_category')) ))

What's the meaning of {...}?, It seems a table name, I try to copy above sql to mysql, But mysql alert me:

SQL Syntax error.

So I try to remove {...}, Like this :

SELECT taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name
FROM 
 taxonomy_term_data
LEFT JOIN  taxonomy_term_hierarchy ON taxonomy_term_data.tid = taxonomy_term_hierarchy.tid
INNER JOIN taxonomy_term_data ON taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid
LEFT JOIN  taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid

But Mysql alert me:

Not unique table/alias: 'taxonomy_term_data'

What's the problem of Views's SQL?

4

Only remove the curly brackets, not what's inside the them. The brackets contain the actual table name, and directly after is the table alias which is used later.

This should work (only brackets removed):

SELECT taxonomy_term_data.name AS taxonomy_term_data_name,
       taxonomy_term_data.vid AS taxonomy_term_data_vid,
       taxonomy_term_data.tid AS tid,
       taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name
FROM   taxonomy_term_data taxonomy_term_data
       LEFT JOIN taxonomy_term_hierarchy taxonomy_term_hierarchy
              ON taxonomy_term_data.tid = taxonomy_term_hierarchy.tid
       INNER JOIN taxonomy_term_data taxonomy_term_data_taxonomy_term_hierarchy
               ON taxonomy_term_hierarchy.parent =
                  taxonomy_term_data_taxonomy_term_hierarchy.tid
       LEFT JOIN taxonomy_vocabulary taxonomy_vocabulary
              ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE  ((( taxonomy_vocabulary.machine_name IN ( 'book_category' ) ))) 

Note that if your tables has a prefix, i.e. drupal_ then you need to add that to the table name, because that's the reason the brackets are used, in case of table prefix.

Cheers,

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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