1

I have some very tricky reports to develop (with multiple subqueries) and it occurred to me that the Data module would be perfect: I can create my complex queries as SQL Views (I know how to do this), then adopt them with Data and use them as ordinary tables in Views. Perfect!

Except that although you can adopt a SQL View, it won't show up in Drupal Views (apparently because no primary key) and of course you can't add a primary key to a SQL View.

Is there any way round this by using a module to replace the View SQL query? I have searched a lot on the web and cannot get around the problem without coding object PHP which to be honest I find intimidating.

  • It might be a bit tricky, but it sounds like perhaps patching the data module to allow you to add a primary key might help? Have you checked the issue queue if anyone has already tried? – Letharion Oct 31 '14 at 18:14
  • Looks like someone else has made an attempt in this direction but to be honest I was hoping to avoid this because I don't understand at all how Views works from the inside, and I was a bit daunted by the idea of trying to understand the Views API. I don't even know where to begin on this. – Joel Guesclin Oct 31 '14 at 20:45
  • I no little knowledge of how Views works in the inside, but the api for telling Views about tables is fairly straightforward in my opinion. There's also plenty of examples of it around. I'd probably try going down that route. – Letharion Oct 31 '14 at 21:33
  • 1
    Fiddling around, I managed through a very simple modification in data_ui to "trick" it into thinking that the View was a data table. Then somehow adding a "real" table caused my SQL View to show up in the content list of Views, and i could list content! But I haven't been able to reproduce the "trick" - still I think you're right this is probably the way to go. And it could be a real addition to Views too - the possibilities are practically endless if you could make really complex queries be treated as if they were simple tables! – Joel Guesclin Oct 31 '14 at 22:29
1

I dont know your use-case. But look at new Raw SQL Query module, and see its attached screenshost(s) for how you define the SELECT queries and the report pages it generates:

It builds "tables" (mysql views using "CREATE VIEW" statement) based on raw sql queries. I would say it is like a lightweight version of a materialized views approach. It uses Ctools to manage data storage and PHPExcel to export data into a Excel file :) After installation, go to admin/config/development/raw_sql_query Do not forget clean cache after you store a query.

It sounds like this module is caching the returned data (I'm not 100% sure). But that would be helpful since a mysql view is literally a SELECT query upon every access to the data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.