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

merlinofchaos’s picture

Category: bug » support
Status: Active » Fixed

Because 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.

WillHall’s picture

Ah, thanks Earl.

For anyone else having similar issues:

 	$view->build_info['query']->groupBy('field_data_field_category_field_category_value');

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

PeggyOCO’s picture

Why 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)

PeggyOCO’s picture

Title: Call to a member function addMetaData() on a non-object after $view->build_info['query'] » Correction

I 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))

PeggyOCO’s picture

Title: Correction » $view->build_info['query'] causes error in Drupal 7 Views 3
Status: Closed (fixed) » Needs work

Changed the title to reflect the content of this thread

dawehner’s picture

Status: Needs work » Fixed

Change 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.

PeggyOCO’s picture

You 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.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

EmmyS’s picture

Version: 7.x-3.x-dev » 7.x-3.3
Status: Closed (fixed) » Active

I'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:

function group_speaker_carousel_views_pre_execute(&$view){
    if ($view->name == 'speakers_carousel')  {
        $view->build_info['query'] = "SELECT DISTINCT node.title AS node_title, node.nid AS nid, field_speaker_node.title AS field_speaker_node_title, field_speaker_node.nid AS field_speaker_node_nid,  'node' AS field_data_field_job_title_node_entity_type,  'node' AS field_data_field_company_node_entity_type
FROM {node} node
LEFT JOIN {field_data_field_speaker} field_data_field_speaker ON node.nid = field_data_field_speaker.field_speaker_target_id
LEFT JOIN {node} field_speaker_node ON field_data_field_speaker.entity_id = field_speaker_node.nid
WHERE (((node.status =  '1') AND (node.type IN ('speaker'))))
ORDER BY node_title DESC";
       
    }
}

(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.

dawehner’s picture

Status: Active » Fixed

Well 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 ....

EmmyS’s picture

Thank 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.

merlinofchaos’s picture

Sorry 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.

dawehner’s picture

Status: Fixed » Active

I'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.

MustangGB’s picture

Issue summary: View changes
Status: Active » Closed (outdated)