Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
In 6.x we could use this to modify the query, however the same method in 7.x returns this non-object warning
function MYMODULE_views_pre_execute(&$view){
if ($view->name == 'videos')
{
if($view->current_display == 'block_1') {
//drupal_set_message($view->current_display);
$search = array('ORDER BY');
$replace = array('GROUP BY field_data_field_category_field_category_value ORDER BY');
$view->build_info['query'] = str_replace($search, $replace, $view->build_info['query']);
//drupal_set_message($view->build_info['query']);
}
}
}
There is no documentation for this branch of Views, so I am not sure if this is by design, or if it is actually a bug. For now I will set to bug.
Comments
Comment #1
merlinofchaos CreditAttribution: merlinofchaos commentedBecause we're now using Drupal's new query generator, the query is a SelectQuery object which you will have to modify or replace. Look up Drupal 7's new database layer for more information.
Comment #2
WillHall CreditAttribution: WillHall commentedAh, thanks Earl.
For anyone else having similar issues:
Comment #4
PeggyOCO CreditAttribution: PeggyOCO commentedWhy does this not work in Drupal 7? Using mymodule_views_pre_execute(&$view), I ran $view->build_info[query]=$newquery. And it failed.
I built $newquery using DBTNG. When I run it outside of Views it works fine. When I try to replace the views query it fails with the same message about Call to a member function addMetaData() on a non-object as mentioned earlier in this thread.
I tried using $view->build_info['query']->$newquery but that also gave the same error.
Is there any way to intercept and replace the SQL query for Views? I am brand new to Drupal, but it looks to me like this was once upon a time functional.
Here is my code for $newquery:
$newquery = db_select('field_data_field_cmcategory', 'fr');
$newquery->Join('node', 'n', 'fr.entity_id=n.nid');
$newquery->fields('fr', array('field_cmcategory_value'))
->fields('n', array('title'))
->condition('fr.field_cmcategory_value', array('Agency Management'), 'IN');
This works if I run $result = $newquery->execute();
And loop through result:
foreach ($result as $record) {
echo $record->title .'
';
}
The titles are the correct ones I was querying for. What is the approved method that works to alter the Views query using mymodule_views_pre_execute(&$view)
Comment #5
PeggyOCO CreditAttribution: PeggyOCO commentedI was wrong about $view->build_info['query']->$newquery giving the same error. It gives this error:
Undefined property: SelectQuery::$SELECT fr.field_cmcategory_value AS field_cmcategory_value, n.title AS title FROM {field_data_field_cmcategory} fr INNER JOIN {node} n ON fr.entity_id=n.nid WHERE (fr.field_cmcategory_value IN (:db_condition_placeholder_0))
Comment #6
PeggyOCO CreditAttribution: PeggyOCO commentedChanged the title to reflect the content of this thread
Comment #7
dawehnerChange status as this isn't a bug/feature/task.
So in general you should try to avoid to alter the query of a view, because views does a lot of stuff in the background
which you would to know to certain extent to be able to alter it.
The best hook to alter a query is hook_views_query_alter and works similar to d6,
not your code but other code.
Comment #8
PeggyOCO CreditAttribution: PeggyOCO commentedYou are so right about Views handling most things -- in fact, after I realized that Views makes added fields available for inclusion into conditions and there was no need to join the content type table and the field_data table, I used filters to solve this problem.
That said, there will be some queries that Views won't be able to handle -- and that is the purpose of these two functions, hook_views_pre_execute and hook_views_query_alter, and I would like to know how to use them, and the rest of the Views api. But documentation seems pretty sparse for Views 3 -- the advanced help is there and fairly extensive on how to use Views, there are a variety of books and videos that cover some of the workings of Views, but I haven't found a thorough treatise on exactly how to expose a module to Views in order to use the hooks, or how to use the hook to do things. If anyone knows of a good resource, please do tell.
As far as writing the query, I understand that Views attends to safe query writing methods, but surely the writers used DBTNG, where the query is an object with methods that take arguments that are put together like building blocks to build even the most hideous SQL query you can imagine, using leftJoin rightJoin, and innerJoin, subselects, you name it, all in a completely escaped and injection proof way. So it seems to me that writing queries using DBTNG is pretty darn foolproof.
Comment #10
EmmyS CreditAttribution: EmmyS commentedI'm getting the same error. Yes, I know views handles a lot of stuff, but I need to use GROUP_CONCAT as well as GROUP BY, and can't find a way to do it via the UI. I'm trying to completely rewrite the query like so:
(I haven't yet added any of my custom code here; this is exactly the query that the Views UI shows in preview. I just wanted to see if it would work at all.)
It gives me "Fatal error: Call to a member function addMetaData() on a non-object in /home/www/expo/sites/all/modules/contrib/views/plugins/views_plugin_query_default.inc on line 1342"
Any ideas? I did see in another thread that this isn't a bug; it's expecting a SelectQuery object. The problem is, I can't seem to find much documentation on how to rewrite my query into a SelectQuery object.
Comment #11
dawehnerWell if you don't know how to use SelectQuery i recommend you to read http://drupal.org/node/310069 which should explain the basic stuff.
To be honest, this is not a damn complicated query which you have there ....
Comment #12
EmmyS CreditAttribution: EmmyS commentedThank you for the link. As to the snotty remark about how simple my query is, did you happen to notice this in my post?
"(I haven't yet added any of my custom code here; this is exactly the query that the Views UI shows in preview. I just wanted to see if it would work at all.)"
The actual query I need to run is quite complicated, and it seems silly to have to use the SelectQuery format given that there's nothing dynamic about it. It's a single query that will never change, never needs to take params. It just needs to run.
According to Lullabot, who I think know what they're talking about, it's perfectly OK to use db_query with raw SQL for queries that don't have dynamic components. I'd really like to be able to do that, but can't seem to get it to work.
Comment #13
merlinofchaos CreditAttribution: merlinofchaos commentedSorry EmmyS, I think dawehner actually misunderstood what you were asking, and thought you already understood the basic premise.
The short answer is: In Drupal 7, $build_info['query'] is now a drupal query object, not a raw SQL query. In order to modify it, you have to use Drupal's Query API (also known as DBTNG) rather than simply trying to overwrite the raw query. The link dawehner provided should help you figure out how to use this API.
Comment #14
dawehnerI'm sorry, i should not post answers if i'm in a hurry.
Make views working with pure string queries will be probably quite some fun, and was never planned to do.
Well views is not simply a raw sql query, because it is dynamic and uses the knowledge how to generate sql structure looks like internally.
Comment #15
MustangGB CreditAttribution: MustangGB commented