What I want to do is dead simple in SQL and PHP and seemingly above my head in Drupal. In Views, I need to construct a table that is populated from the following SQL query
SELECT animal.field_name_value as name, terms.name as breed
FROM content_type_animal_information animal
LEFT JOIN term_data terms
ON terms.tid = animal.field_breed_value
WHERE animal.field_animal_value = "dog"
An example output of the above query when executed in PHPMyAdmin on the Drupal database is
name | breed
Pampy | Labrador Retriever
Porky | Chinese crested
However when I try to create a table View using these Context: Name (field_name) and Context: Breed (field_breed), I get
name | breed
Pampy | 212
Porky | 200
which are the terms.tid numbers instead of the names. Due to poor (or incredibly well hidden) documentation on the Views module, I cannot for the life of me figure out how to get Views to reference the terms.name using the terms.tid supplied.
I have tried Relationships but there are no relationships listed that even remotely have anything to do with the taxonomy id and name. I've also tried adding Arguments and using the Rewrite the Output of This Field function but I can't see how that works properly either.
It's really depressing how much time I spend fighting Drupal and desperately searching for documentation rather than developing.
Thanks for any help
- Roland
Comments
How about trying taxonomy
How about trying taxonomy term instead of tid ?
The field Taxonomy: Term -- it's right there , above the tid option
Pasting from field options in Views:
If I just add Taxonomy: Term
If I just add Taxonomy: Term to the fields with no other changes to the options, I just get a blank field/column in my table for Term.
Do I need to check the Rewrite Output of Field and add some code there to make it display the term name for the given term id? If so, what would I write?
- Roland
This is the SQL outputted in
This is the SQL outputted in the Views admin page...
SELECT node.nid AS nid,
node_data_field_name.field_name_value AS node_data_field_name_field_name_value,
node.type AS node_type,
node.vid AS node_vid,
node_data_field_name.field_breed_value AS node_data_field_name_field_breed_value,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid
FROM node node
LEFT JOIN content_type_animal_information node_data_field_name ON node.vid = node_data_field_name.vid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
WHERE node.type in ('animal_information')
The problem lies in the last two LEFT JOINs especially "LEFT JOIN term_node term_node ON node.vid = term_node.vid". I don't want it to JOIN with term_node as there is no connection between term_node and term_data. Term_node is currently an empty table. LEFT JOINing to it breaks the connection between term_data and the rest of the query.
I want to eliminate the second to last LEFT JOIN and change the last LEFT JOIN to "LEFT JOIN term_data term_data ON node_data_field_name_field_breed_value = term_data.tid".
Is this possible or is Drupal not going to let allow me this degree of control of the query?
- Roland
Try starting with a fresh new
Try starting with a fresh new view and keep it simple.
And if you haven't yet already, install the advanced help module.
http://drupal.org/handbook/modules/views