I want to add a field to a view that is the count of rows from a database table. (I would use a calculated field but for views to use calculated fields they need to be calculated and stored in the database before hand and I want this calculated on the fly.)

E.g.: Say I have a view of cities, and assume there's a content type where city is a field, and for each city in this view I want a count of how many nodes reference that city.

So how can I add a count from a sql query to each row of the results a view? [edited]

Is there a way to add a dummy field and then hack the sql query to return count(rows) as that_field?

Or should I add a dummy field to the content type and use hook views_pre_render to modify the result by running queries for each row of $view->result?

Or instead of adding a dummy field can I add to $view->field to display the value added to $view->result?

Comments

nevets’s picture

See http://drupal.org/node/444906 for one approach.

tomotomo’s picture

Sorry, to be clear, for each row in results I want a field that shows the count of rows in a mysql table that has a certain value (another one of the fields). I don't care about the # of rows in the results of the view.

My blog on Drupal/Vietnam: Saigonist
Twitter: @tomosaigon / @tomodrupal

jim0203’s picture

Maybe http://drupal.org/project/noderelationships will help? It mentions "Back references count: displays the count of back references. "

jim0203’s picture

tomotomo’s picture

I amended the post, I don't care about the count of results in the view.

Say I have a view of cities, and assume there's a content type where city is a field, and for each city in this view I want a count of how many nodes reference that city.

My blog on Drupal/Vietnam: Saigonist
Twitter: @tomosaigon / @tomodrupal

tomotomo’s picture

Ok, instead of adding a dummy field maybe I can use "Global: Custom Text"

My blog on Drupal/Vietnam: Saigonist
Twitter: @tomosaigon / @tomodrupal

tomotomo’s picture

Unfortunately, it looks like using global: custom text is not an option because the field doesn't have anything to modify in the results array. So back to using a dummy cck field in the node.

My blog on Drupal/Vietnam: Saigonist
Twitter: @tomosaigon / @tomodrupal

tomotomo’s picture

Perhaps the general way to do this is by aiming the view is by theming the view?

My blog on Drupal/Vietnam: Saigonist
Twitter: @tomosaigon / @tomodrupal

scottprive’s picture

1) views theming.
The only downside to this approach is it does not "feel right" -- you're putting application logic in the template, and you just know it would be useful to do this "in" views. If you feel that way, get over it and just try the template method anyways :-)

2) _views_pre_render
_views_pre_render can intercept the output of the view and from there you can inject other rows and columns.
It's sad, really - a few people know how to do this, and a great many people post similar questions on this issue and never solve it. This approach is difficult because of the lack of how-tos - the API docs do what they are supposed to (it's all there except how to pull the pieces together). There is some taxonomy views stuff that implements prerender but it's not easy to follow if you do't know what's going on behind the scenes. No one's taken it to the next level and made a blog post showing how _views_pre_render can be used to change the view result. That said, if you figure it out...

jbiechele’s picture

I would highly appreciated if you ever find the time to write this missing documentaton.
subscribe