Greetings, local Drupal expert friends. I need some help figuring out how I should solve a problem. I've been struggling with it for weeks! It sounds like it should be so easy, but a variety of different approaches (including a number that I culled from the net) have failed. Let me first carefully explain the issue. Then I'll set up an example. Finally, I'll list (some) of the approaches I've tried.
The problem: My site has a set of nodes that are tagged with two different vocabularies. I need to set up a page that displays a term from one of these categories, and lists the terms from the OTHER vocabulary that are tagged on nodes selected from the first vocabulary. Each of these terms will link to a page that lists nodes that are tagged with both vocabularies.
Ok, I know that sounds confusing; the following example should indicate what I mean.
Let's say my nodes were of type Species, and my vocabularies were "Zoo" and "Family." Here is the data:
Zoo - Family - Species
SF - Ape - Gorilla
SF - Ape - Baboon
SF - Ape - Monkey
SF - Cat - Tiger
SF - Cat - Lion
Oakland - Ape - Gorilla
Oakland - Ape - Chimpanzee
Oakland - Reptile - Turtle
Oakland - Reptile - Alligator
Oakland - Reptile - Snake
I want to give my view the zoo as a parameter, and get a list of the species:
zooanimals/SF
Types of animals at the SF Zoo:
- Ape (3)
- Cat (2)
zooanimals/Oakland
Types of animals at the Oakland Zoo:
- Ape (2)
- Reptile (2)
Each of these will link to a page of nodes that match both parameters. For example, the last item (Reptile (2) ) will link to this page:
showzooanimals/Oakland/Reptile
Reptiles at the Oakland Zoo:
- Turtle
- Aligator
What I've tried. (I actually built this example; the output below is from a running Drupal site.)
First, I think I need to have an argument to choose the zoo, so I define an argument of type "Taxonomy: Term ID," confine the argument to my "zoo" vocabulary, and have it convert the term name to term ID. When I feed it "SF" as the parameter, I get this:
Cat
Cat
Ape
Ape
Ape
But...I don't want to have a separate result for each type, I just want one (ideally with a count of the nodes, but that's optional.) I have Distinct set to "yes" -- the generated SQL looks like this:
SELECT DISTINCT(node.nid) AS nid, node.vid AS node_vid
FROM node node
INNER JOIN term_node term_node ON node.vid = term_node.vid
WHERE (node.status <> 0)
AND (node.type in ('animals'))
AND (term_node.tid = 2) GROUP BY nid
I can see why the DISTINCT directive isn't working; it's selecting the nid, not the term, so every node needs to show up. Also, I don't know why it's throwing in the "GROUP BY" directive -- what's that for? Is there any way to get rid of it? I certainly don't want to group by nid! Finally, I don't see how it gets the taxonomy term name (which is what it ultimately displays) at all. Why isn't that in the SQL? How does views find it?
I've tried for a LONG time to figure out how to do this with views -- actually, views inside of panels, but the logic I'm having trouble with is just with views. I've just about given up, and tried to write a module to do it, based on this posting:
http://himerus.com/blog/himerus/fixing-duplicate-taxonomy-terms-node-vie...
This code, however, seems to assume that I am have one taxonomy in play. I tried to modify it for my needs, and I've made some progress, but I'm really in over my head with the API-level stuff; I'd like to solve it in the GUI, if it's possible.
So...any suggestions? I didn't expect this to be so hard to do, I feel like what I want is pretty basic functionality -- I have routinely implemented this sort of thing in SQL-driven sites for years. What am I doing wrong?
One theory: should I not be using two vocabularies? Should I make my "Family" category a CCK-type instead? Do you Drupal vets routinely use multiple vocabularies at once? (If it matters, the "real" site I'm working on has about a dozen categories, 20-30 classification terms, and about a thousand nodes.)
Thanks for any help!
Luke
Edit: Drupal 6.20, Views 2.12
Comments
It seems that you've created
It seems that you've created a Node/Content view, when really you want the view to display taxonomy terms -- i.e., a Term view.
That said, I don't know to fix your problem.
Thanks. I experimented
Thanks. I experimented pretty extensively with term views. They don't really work for this, since I'm trying to display only those terms which are used in the subset of nodes chosen by the view's argument. So we have to inevitably intersect with the node system, which I don't think a term view can do. (At least, I don't see a way to do it with them.)
Quick thoughts
My first instinct would be to recommend trying the Views Group By module - http://drupal.org/project/views_groupby. See if that gets you where you want to go, or at least farther along.
Thanks, Michelle. I, too,
Thanks, Michelle. I, too, thought Views GroupBy would get it done for me -- it sure sounded like it would! Unfortunately, it doesn't seem to be able to group by taxonomy terms. When I tried it, it always insisted on putting the nid in the GROUP BY clause, thus producing groups of one node each -- not very useful, unfortunately.
Even without the Group By module, Views uses the GROUP BY clause for something -- note that in my example SQL above, there's a GROUP BY clause. The only way I've found to get access to it, however, is to create a dummy argument, then not supply it! When you create an argument, one of the options you can choose for "Action to take if argument is not present" is "Summary, sorted ascending". If you select this, you are given a further option (on the next screen) to "Display record count with link." This is exactly what I want! I wish I knew a way to get it without (it seems to me) intentionally generating an error.
My current thinking is that I have to abandon my intended information architecture, which was based on multiple intersecting vocabularies. Instead, I'll use CCK fields to select my categories, then count the topics ("Families" in my zoo example) using either Views Group by or just straight views.
three untested brainstorms
It seems sad to restructure the data, which makes sense as-is, just to meet the needs of views. Would content_taxonomy let you keep your current architecture and use Views Group-by?
It also seems like a term view using a relationship to the node, where the nodes are set by the argument to the first (zoo) vocabulary, should work?
Or if views really doesn't have an option, you can force one. See http://echodittolabs.org/blog/2010/06/group-views
Search and replace the DISTINCT(node.nid) part of the sql statement to make the term DISTINCT instead of the node?
(Is this D6 or D7?)
Good suggestions
Thanks, Stephen, these are some good ideas.
1) I don't know what Content_taxonomy does; I looked at http://drupal.org/project/content_taxonomy and it wasn't clear to me how that would help me. I'll come back to it later and see if I can understand what you are proposing
2) A relationship to the node? Can you explain what you mean?
3) Intriguing. I love the idea of modifying the SQL before it gets run -- I'm quite comfortable getting SQL to do the things I need it to do. Do you know where I should put the hook_views_pre_execute call? Do I need to put it in a custom module? Or can I put it in my theme's template.php file?
This is in D6, Views 2; I updated the OP to reflect.
You probably figured out
that you'd want to use that hook in a module. If your module was called cellar.module, you'd do:
cellar_views_pre_execute(&$view){//edit sql statement
...
}
http://api.drupal.org/api/drupal/includes--module.inc/group/hooks/6