How to use a custom table in Views?

Posted by bartl on June 30, 2011 at 10:39am

I'm developing an auction site where people can bid on an item, which is implemented as a custom node type. So there should be at most a single value (the current bid) per user, per item.

I've tried out the Flag module, in particular with the "fieldable" patch, but it's not working very well and it's not going to get in a real release before the next major version (and the current major version is still in beta). So that's not really an option.

So instead I simply created a plain SQL table in my database, with (at least) 2 fields: a node id (nid) for the item, a user id (uid) for the user, and a floating point value (amount) for the amount of the bid.

SQL is no problem, but... what do I have to do to be able to see this table in Views?

What I'd like to be able to do is:

create a grid of all bids made by one user, linked to the node contents (join with nodes)
create a grid of all bids for one item (join with users
extract the maximum amount for each item and the first user that made this bid (there's also a timestamp field, to sort on)

The problem is I can not even make this table show up in the Views interface.

Based on the tutorial I tried to do something with hook_views_data, but I can't get anything working, I can see nothing. Other forum posts point to the Table Wizard module but that module is for Drupal 6 and currently without a maintainer. I even glanced at how Flag does it, but the sheer amount of Views related include files is just overwhelming.

I don't think I can do this. Help?

Comments

Try Table Wizard or Data

Posted by databoy on June 30, 2011 at 11:48am

You can make your non-drupal controlled tables available in Views by using the Data module.

Or, Table Wizard, because, well, the name is cool!

Good luck!

No you can't. Because the

Posted by bartl on June 30, 2011 at 11:57am

No you can't. Because the Data module is only available for Drupal 6 (and even there it's still alpha), and I am struggling with Drupal 7.

Right.

Posted by databoy on June 30, 2011 at 12:15pm

Version number. Check.

This is the Views Developers group. Maybe you plan on programming a custom module? Then, hook_schema in your custom_module.install (replacing "custom_module" with your module's actual name) and defining a custom entity should give Views programmatic access to your table. I'm currently investigating starting to program with D7's "Entities" and I like them a lot!

Best of luck!

Drupal 7 answer with one caveat

Posted by timebinder on August 22, 2011 at 10:21pm

Assuming you are using Drupal 7 (I know you mention 6 above but I figured this is good FYI anyway) you have to describe your table using the hook_views_data

Basically this is a full definition of the table, mapping it to a name (of your choosing) which will represent it in Views, and whether it is sortable or filterable.

The caveat (Which I could really use help on) is that I don't yet know of a nice clean way to show a compound view of multiple tables, nor do I know how to put a link to another record (such as a related node) in my view.

I know you mention it doesn't work, but as with most things Drupal my guess is that some esoteric piece of configuration is missing from your function

HTH