How do I add an SQL query with hook_views_query_alter()?

Question

I have the following SQL query:

db_query("SELECT content_id, COUNT(*) as count FROM {votingapi_vote}
          WHERE value_type = '%s'  AND value = 1
          GROUP BY content_id;", "option");

If I want to override the view, which code should I put in mymodule_views_query_alter()?

The content of $query is the following:

    views_query Object(
      [table_queue] => Array(
        [node] => Array(
          [alias] => node
          [table] => node
          [relationship] => node
          [join] => 
        )
      )
      [tables] => Array(
        [node] => Array(
          [node] => Array(
            [count] => 1
            [alias] => node
          )
        )
      )
      [relationships] => Array(
        [node] => Array(
          [link] => 
          [table] => node
          [alias] => node
          [base] => node
        )
      )
      [where] => Array(
        [0] => Array(
          [clauses] => Array(
            [0] => node.status  0
            [1] => node.type in ('%s')
          )
          [args] => Array(
            [0] => provider
          )
          [type] => AND
        )
      )
      [having] => Array()
      [group_operator] => AND
      [orderby] => Array(
        [0] => node_created DESC
      )
      [groupby] => Array()
      [header] => Array()
      [distinct] => 
      [base_table] => node
      [base_field] => nid
      [fields] => Array(
        [nid] => Array(
          [table] => node
          [field] => nid
          [alias] => nid
        )
        [node_title] => Array(
          [field] => title
          [table] => node
          [alias] => node_title
        )
        [node_created] => Array(
          [field] => created
          [table] => node
          [alias] => node_created
        )
        [content_id_] => Array(
          [field] => 
          [table] => content_id
          [alias] => content_id_
        )
      )
      [count_field] => Array(
        [table] => node
        [field] => nid
        [alias] => nid
        [count] => 1
      )
    )

Answer 1

Using hook_query_views_alter is something you should do with care as it can very easily come back and bite you when you least expect it. Trust me, I have tried this on some occasions.

hook_query_views_alter is powerful as you can create any query you want, but it should be used like the name suggests, as an alteration. It's not meant to be used by creating a view with an arbitrary query and then completely create the actual query from scratch in the alter hook. This will lead to many confusing moments, like why does the view display something different than what I set up, and why doesn't it change when I change it.

The query from the View looks something like this:

SELECT nid, title, created FROM {node}
WHERE type IN ('provider') AND status = 0

Compared to

 SELECT content_id, COUNT(*) as count FROM {votingapi_vote}
 WHERE value_type = '%s'  AND value = 1
 GROUP BY content_id;

There are two ways to solve your problem, without having to sort to nasty hacks in the hook_views_query_alter function that will be hard to maintain, and probably will give you or the next developer problems down the road.

1. Use the node table and create a JOIN

   The voting api has Views integration but doesn't support that you query the table with views, using it as the base table. It does however support that you create a join (relationship). Most likely you are voting on nodes. If that is the case you can create a base view from nodes and add in the votes as a relationship. See below SS of how to add the relationship and the fields needed.

   SS http://tardis1.tinygrab.com/grabs/99f4a5fef7b5f963fa6a64f0be8ad451af21915caf.jpg

2. Add info to views about voting tables

   With hook_views_data_alter, you can add the needed info about the voting tables to be able to query them directly. Views documentation has great examples of how this is done. Then you could create a view that queries the votes and via the Views interface create a query similar to what you posted.

Answer 2

Try this, First Go with Views as googletorp. It will also helps you

   function custom_views_query_alter(&$view, &$query){

    if($view -> name == "SomeView"){

      $sample_query = db_query("SELECT content_id FROM {votingapi_vote}
          WHERE value_type = '%s'  AND value = 1
          GROUP BY content_id;", "option")->fetchField();

      array_push($query->where[2]['conditions'],array('field'=>"node.nid","value"=>array_unique($sample_query),"operator"=>"IN"));

    }

   }