Query-Based Views (Q-Views) Drupal module intro and backstory

Skip to the video demo

Open the Drupal.org project page for Query-Based Views

One of my first Drupal modules ever was Ajax Table, which gave some rich tools to users who would would normally circumvent Views to create a report or feed by constructing it programmatically. The problem being solved was "what does one do when they need to build a report starting with a query?" The Views answer is to expose everything you need to Views through modular hooks. This is an awesome long-term solution because, once your tables and custom output functions are exposed to Views, they can be used by any other view, and you can combine stuff in really novel, nifty ways.

However, a lot of people don't have the time or expertise to do that. They need to start with a query, and they would normally create a custom function, run the query directly and then generate the output programmatically. I think that there will always be use cases for this, and even if the same report could be created in Views, some people will do it this way simply because it's faster for what they need. Ajax Table was a utility module for these use cases. By running a query through a helper function and passing a couple parameters, users could generate ajax-reloading, searchable, sortable reports with very minimal work. It was handy, and I used it all the time. I still use it for my internal time-keeping / invoicing / proposal generating system.

The functionality was useful enough that I built out a second iteration called Query-Based Views, this one using Drupal configuration instead of straight-up code. I demo-ed the module to my local Drupal User Group, and got some positive responses regarding of the ajax functionality, rapid development and in-line editing. However, the general consensus was that the functionality would make a much greater impact if contributed as a Views plug-in, or as patches to Views itself.

So I shelved the project, thinking that until I knew enough about Views to integrate this, it would be better not to fork efforts.

Then a few days ago it struck me that maybe I should ask Earl Miles (merlinofchaos), the developer of Views, if the idea of starting with a query was actually Views-compatible. I was thinking that maybe the Views query builder was just an element of the UI that could be replaced out with something supplying a raw query. He said that it really wasn't. Too much of the power of Views is derived from abstracting out the query building process into the UI that's there.

That means there's actually room for this type of module. It's true that a lot of functionality is duplicated, but Query-Based Views runs on a different paradigm that's incompatable with Views, so it's reasonable for it to do so. Some specific bits like in-line editing could be abstracted more to be able to work with Views and Q-Views, but as a first effort, I think this module should fit the bill.

So I spent a couple of days porting the Drupal 5 version of Q-Views to Drupal 6, fixed a bunch of bugs and am releasing it as an alpha. The bulk of the code is written by the Chris Shattuck of yesteryear, who was not as well versed in Drupal ways, so if the module is useful enough a code rewrite is in order for a non-alpha / beta release. But as it stands, it's functional and ready to roll.

Video Demo

Q-Views has a lot of functionality, so this is a longer video (20 min). Feel free to skip around, though.



Comments

I <3 modules that expose underlying structure. :) I saw that you haven't released it yet -- do you have a sense when you'll start doing so? And will you have a D7 version?

Hi Tom, I'm working on releasing it right now, the alpha D5 and D6 versions should be ready in an hour or so. I'd also like to do a D7 port, but that hasn't been worked on at all yet.

Cheers!

Hey, this looks awesome, thanks for all your hardwork
Looking forward to playing with this in my next project.
Only improvement would be not having to enable the phpfilter for the blocks stuff, if it created blocks like views does.
Thanks again
Chris

I agree, great idea. Feel free to add a feature request in the project issue queue for it.

Thank you. Thank you thank you thank you.

I've wanted this for a very long time!

The treshold to integrate your data with Views is indeed too high to justify in many cases. Not in the least because the documentation is lacking. But the most important obstacle for me is: I find it much harder to learn the extensive terminology and vast UI than to just code something.
Then I feel like *I* am in control instead of an extremely large and complex module. The only problem with doing it manually was that AJAX support and filtering and sorting were kind of a pain because you had to repeat the same code too much.
This solves that. Awesome.

I didn't know about Ajax Table, but I'll probably start using this for driverpacks.net. For that, I simply used a simple form + theme_table() + caching.

A quick glance through the feature list and the code shows me only one lacking feature: caching. But maybe, depending on how you use this module, I can do that myself. Which may actually be easier and less restricting than integrating that.

I may start using this for Mollom.com's statistics back-end. Which brings me to the inevitable question of why you are not yet using Mollom on this blog? :)

Excellent work. Thanks!

P.S.: if I like the module after I've done some testing, I'll definitely provide Hierarchical Select support for it!

Thanks for your thoughts, Wim!

Good thought about caching. I haven't run any performance tests on Q-Views or Ajax Table to see what would happen under a lot of stress. I definitely optimized it for quickly getting things done, rather than large-scale performance. I should take a look at how Views does its caching. Does it just cache the first page?

You know, I tried integrating Mollum but was running into some funky stuff where the image wasn't loading sometimes and there were occasions where it didn't matter what I put in the captcha, it just wouldn't let me through. So, I ditched it for the image captcha, loosing some cool factory but gaining a little sanity. I'd like to give it a try again at some point.

Hierarchical Select support sounds neat, did you have something in particular in mind there?

Cheers!

Wow! this module is just so cool because it opens drupal to a whole new world of posibilities. Now I can add just any functionality to my site and I don't have to create a module just to provide 'views support'..

With this module, I just have to create forms and then write a simple query and magic comes true.

One question.. I know views uses a lot of memory and resources, does qviews uses a lot of memory? or is it more of a light module?

Thanks a lot.

Hi Luis,

Glad to hear your enthusiasm. Even though this module opens up some new possibilities, I'd encourage anyone to use Views when they can. It's just a more mature product. This module is there to fill use cases where Views just isn't right for the job, or when the overhead of learning and constructing views is too high.

As far as performance goes, I haven't done any benchmarking yet. I think Q-Views has the capacity to make a lighter footprint, simply because there's less that has to happen to construct the query, and there's fewer hooks being run. Whether it is right now or not would have to be seen. If Q-Views gets enough usage, a code-rewrite would be imminent where scaling issues could be addressed.

Cheers!

This is great! I have some questions / suggestions about terminology.

"Feed": you use the term "feed" alot. In Drupal, people usually associate this with RSS feeds. However, here I think you mean "data feed", or "query result", i.e., the data returned as a result from a specific SQL query. Am I correct about this? Or do you mean something else.

Thanks for creating this module. It is really interesting.

I agree. I could probably change several instances of 'feed' with Q-View, and the others could be, perhaps 'reports.' I settled on 'feed' a year ago, but now I agree that it's a little confusing. Feel free to make this request in the Q-Views issue queue. Thanks!

Very Cool and Great Stuff!!! Been trying it the whole day today ['Fat Boy Day' of Creative Destruction]. Ability to change data right there is a Big Man-On-The-Moon Usability Boost for all normal human beings that I've looked for for some time. [Feature Request: 1. If "Allowed Values" for Select Lists can be used in Select List 2. If it could display the "display" value rather than the "key" for Select List?]. Thanks and Kudos to you! [if you come to TH be sure to contact me]

Thanks, Kuson, I'm loving your terminology!

I'll look into the 'allowed values' bit. It would be helpful to see a quick Jing screencast of what you're describing, and it might be a good thing to add to the Q-Views issue queue.

You're welcomed Chris!

Sorry I wasn't clear: my "Allowed Values" = the "Allowed Values" in Select List of a CCK Text Field :) To take it even further, also the "AutoComplete" for CCK Node References Field.

Its great you put in the ability to populate the Select List in Q-Views (sounds like Kill Bill fast haha), and definitely be re-using existing work who's using CCK, if the Select List can come from other sources, such as CCK Allowed List, CCK Auto-Complete, or ["user custom, pre-done, SQL query populated into a variable" and variable assigned to the select list ] I can do screenshots and send to you if you'd like :) - but do tell me if this feature already exists!

Thanks Again, I'm enjoying your work every bit! :) From the Other Side of the World,

Kuson

Hi

I installed the module in D5 but I do not see the content browser or Q views under Admin page. What's missing ?

Regard

Sorry to see the public reaming you got over the security issues, that was awkward to read let alone be the target.

You've got a great start and huge potential with this, don't let the issues discourage you.

I hope to see you dive into fixing the security issues with as much gusto as you did designing this, and it will become a rockstar top 5 module. I'll look at it again in a month.

Hi Kirk, thanks for the kind words.

I'm already drilling on security best practices, which will probably lead to a rewrite of the module to make sure I've got all the bases covered. Lots of work I didn't anticipate, but from the feedback I'm getting it sounds like this could be a useful module in the community.

Cheers,
Chris

Hello all,
i like the option to build view from query and it si working greate.
I am familiar wilth SQL but new to PHP and Drupal.
Can some one answer me how i can filter query by curent loged user "%username" in drupal or hi ID in example to show just his content or other user related data.

I konow that it is probably can be done via usnig PHP in q-view (please post simple exmple).
Is it posible to include this parametar in WHERE clasue of select or in filters of q-view

Thanks in advance

This module is awesome!
Will continue developments of this module?
I can help you fix some problems.

Woowiii!! This module is the bomb. Keep up the good work.

When you are ready, I promise to do the documentation for this module. Let me know when!

Nice module!
We (russian developers from drupal.ru) can help to you improve and fix something for this module.

This is a really great idea so stick with it and get it up to production quality as soon as you can!

Many thanks for your work!

Hi,

interesting module!

is anywhere help if is possible to use and how to use Group by, count and other sql things?

I have a problem. My initial sql query with this select part like this
SELECT COUNT(node.nid) AS nid,
term_data.name AS term_data_name,
vocabulary.name AS vocabulary_name
.....
GROUP BY term_data_name, vocabulary_name

is modified into SELECT COUNT(*) ... and I recieve this error lather then Unknown column 'term_data_name' in 'group statement' query

Thx

please change your captcha it is almost impossible to read it!

WOW. When can we look forward to a secure version?

fantastic! Can't wait for this to become stable and secure. It's just what I've been looking for. Thanks so much for all your hard work!

if you not going to fix, i can take over the project and can complete.

Wow... this module shows REAL POWER! Thanks.
Hope this will be ready for production soon :)

Did you just stopped working on this?

I think this is really good and required module. Hope we get a production ready version soon... :)

Hi!

I have been reading through some Drupal blogs as of late as I begin preparation to begin my blog. I have been using a lot of Lynda.com tutorials in preparation and while Drupal seems like it may be the most powerful CMS, it also seems like it may be the most difficult to learn. My primary objective is going to be creating a secondary e-commerce option with efficient order fulfillment capabilities. I was just wondering what CMS you would recommend to a noobie looking to get his feet wet. Thank you.