在view_query_alter（）中添加表加入，在哪里和排序到视图查询 [英] Add Table Join, Where, and Order By to Views Query in views_query_alter()

问题描述

我正在尝试修改Drupal中的视图查询（视图版本3，Drupal版本7）。

我想要做的是将查询更改为运行，使它LEFT加注一个表，其中我有权重分配给节点。

如果我要在SQL中编写我想要的查询，它将看起来像这样：

  SELECT a.nid，a.title，a.description 
 FROM node a 
 LEFT OUTER JOIN node_weights b 
 ON a.nid = b.nid 
 WHERE b.uid = $ uid 
 ORDER BY b.weight DESC 
  

当我在查询分析器中运行这个查询时，这个查询就像一个冠军。所以，现在我需要在我的模块中工作。

我已经看到多种方法在各种博客上详细介绍了不同的修改View查询的方法，但是他们似乎要解决不同版本的Views。所以尝试确定我看的任何东西甚至可能适用于我的应用程序是非常困惑的。

似乎我需要使用MODULE_NAME_views_tables（）功能来告诉Views我要加入的表和节点表之间的关系是什么。

我已经将以下函数添加到MODULE_NAME.views.inc： / p>

  function MODULE_NAME_views_tables（）{
 $ tables ['node_weights'] = array（
name= >node_weights，
join=> array（
left=> array（
table=>node，
 =nid
），
right=> array（
field=>nid
），
） 
）; 
 return $ table; 
} 
  

这似乎工作正常，因为当我使用Krumo查看查询数组，我在table_queue元素中看到我的node_weights表。

在views_query_alter（）函数中，我想要这样工作： / p>

  function MODULE_NAME_views_query_alter（& $ view，& $ query）{
 $ uid = $ _COOKIE ['uid' ]。 
 $ view-> query-> add_relationship（'node_weights'，new views_join（'node_weights'，'nid'，'node'，'nid'，'LEFT'）） 
 $ view-> query-> add_where（'node_weights'，node_weights.uid，$ uid）; 
 krumo（$ query）; 
} 
  

这个功能barfs非常糟糕。虽然我的连接表出现在$ view对象中，但是add_relationship方法会为第三个参数引发错误，但是我没有看到任何在线示例有3个参数，所以我不知道它丢失了什么。 >

另外，我很确定我的add_where方法是不正确的，但我不知道输入实际上是什么。这只是一个盲目的猜测。

底线是我要将节点表加入到我的node_weights表中，然后确保我的权重在查询中使用以降序的方式对结果进行排序，其中用户id =表中的用户ID，表格在nid字段上连接。

提前感谢。

解决方案

一旦你拥有JOIN，哪里可以很容易添加。你可以在一个查询中改变（Drupal 7 ）

 函数MODULE_NAME_views_query_alter（& $ view，& $ query）{
 
 // （$ view-> name =='VIEW NAME'&& $ view-> current_display =='DISPLAY'）{
 
 //创建连接
 $ join = new views_join（）; 
 $ join-> table ='table_name'; 
 $ join-> field ='entity_id'; 
 $ join-> left_table ='node'; 
 $ join-> left_field ='nid'; 
 $ join-> type ='left'; 
 //添加视图查询
 $ view-> query-> add_relationship（'table_name'，$ join，'node'）; 
 
 //添加where 
 $ view-> query->其中[1] ['conditions'] [] = array（
'field'=> 'table_name.collumn_name'，
'value'=>'value'，
'opperator'=>'='
）; 
}} 
  

I am trying to modify the query for Views in Drupal (Views version 3, Drupal version 7).

What I want to do is change the query prior to running such that it LEFT JOINs a table in which I have weights assigned to the nodes.

If I was to write the query I want in SQL, it would look like this:

    SELECT a.nid, a.title, a.description
    FROM node a
    LEFT OUTER JOIN node_weights b
    ON a.nid = b.nid
    WHERE b.uid = $uid
    ORDER BY b.weight DESC

This query works like a champ when I run it in the query analyzer. So, now I need to make it work in my module.

I've seen multiple approaches detailed on various blogs for different ways to modify View queries, but they seem to be addressing different versions of Views. So it is very confusing to try to determine whether anything I'm looking at could even possibly work for my application.

It seems that I need to use a MODULE_NAME_views_tables() function to tell Views what the relationship is between the table I want to join and the node table.

I've added the following functions to MODULE_NAME.views.inc:

    function MODULE_NAME_views_tables() {
      $tables['node_weights'] = array(
        "name" => "node_weights",
        "join" => array(
          "left" => array(
            "table" => "node",
            "field" => "nid"
          ),
          "right" => array(
            "field" => "nid"
          ),
        ),
      );
      return $table;  
    }

This does seem to be working because when I use Krumo to look at the query array, I see my "node_weights" table in the "table_queue" element.

In the views_query_alter() function, I'd like it to work something like this:

    function MODULE_NAME_views_query_alter(&$view, &$query) {
      $uid = $_COOKIE['uid']; 
      $view->query->add_relationship('node_weights', new views_join('node_weights', 'nid', 'node', 'nid','LEFT'));
      $view->query->add_where('node_weights', "node_weights.uid", $uid);
      krumo($query);
    }

This function barfs pretty badly. Although my join table is appearing in the $view object, the add_relationship method is throwing an error for a 3rd argument, but I don't see any examples online that have 3 arguments so I don't know what it's missing.

Also, I'm pretty sure my add_where method isn't correct, but I don't know what the inputs should actually be. This is just a blind guess.

The bottom line is that I want to join the node table to my node_weights table, and then make sure my weights are used in the query to sort the results in a descending fashion where the user id = the user id in my table, and the tables are joined on the nid field.

Thanks in advance.

解决方案

WHEREs are pretty easy to add once you've got the JOIN in. You can both in a query alter (Drupal 7).

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

//only alter the view you mean to
if($view->name == 'VIEW NAME' && $view->current_display == 'DISPLAY'){

    //create the join
    $join = new views_join();
    $join->table = 'table_name';
    $join->field = 'entity_id';
    $join->left_table = 'node';
    $join->left_field = 'nid';
    $join->type = 'left';
    //add the join the the view query
    $view->query->add_relationship('table_name',$join,'node');

    //add the where
    $view->query->where[1]['conditions'][] = array(
        'field' => 'table_name.collumn_name',
        'value' => 'value',
        'opperator' => '='
    );
}}

这篇关于在view_query_alter（）中添加表加入，在哪里和排序到视图查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！