在 views_query_alter() 中向视图查询添加表连接、位置和排序依据 [英] Add Table Join, Where, and Order By to Views Query in views_query_alter()

问题描述

我正在尝试修改 Drupal 中的视图查询(视图版本 3，Drupal 版本 7).

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.

如果我用 SQL 编写我想要的查询，它看起来像这样:

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.

看来我需要使用一个MODULE_NAME_views_tables()函数来告诉Views我要加入的表和节点表之间的关系.

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.

我在 MODULE_NAME.views.inc 中添加了以下函数:

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;  
    }

这似乎确实有效，因为当我使用 Krumo 查看查询数组时，我在table_queue"元素中看到了我的node_weights"表.

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.

在 views_query_alter() 函数中，我希望它像这样工作:

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);
    }

这个功能非常糟糕.虽然我的连接表出现在 $view 对象中，但 add_relationship 方法对第三个参数抛出错误，但我没有在网上看到任何具有 3 个参数的示例，所以我不知道它缺少什么.

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.

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

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.

最重要的是，我想将节点表加入我的 node_weights 表，然后确保在查询中使用我的权重以降序对结果进行排序，其中用户 ID = 我表中的用户 ID, 并且表在 nid 字段上连接.

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.

提前致谢.

推荐答案

加入 JOIN 后，WHERE 很容易添加.您可以在查询中更改 (Drupal 7).

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',
        'operator' => '='
    );
}}

这篇关于在 views_query_alter() 中向视图查询添加表连接、位置和排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！