欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

首页

An example of joining to an additional table 在视图处理程序中加入表

星期四, 2017-03-09 14:09 — adminshiping1
发布者（未验证）上
\ views_join
 
类
表示连接并创建实现连接所必需的SQL的函数类。

表示连接并创建实现连接所必需的SQL的函数类。

这是代理模式。如果我们只有PHP5，我们会声明这是一个接口。

这个类的扩展可以用来创建更有趣的连接。

连接定义

  • table：table to join（right table）
  • 字段：要加入的字段（右字段）
  • left_table：我们加入的表
  • left_field：我们加入的字段
  • 类型：LEFT（默认）或INNER
  • extra：连接上的额外条件数组。每个条件都是直接添加的字符串，或者是一个项目数组：
  • - 表：如果没有设置，当前表; 如果NULL，没有表。如果在缓存定义中指定表，视图将尝试从现有别名加载。如果你使用实时联接，它的工作更好。
  • - field：公式中的字段或公式，我们可以使用％alias引用正确的表

     

    • - operator：默认为=
  • - value：必须设置。如果数组，运算符将默认为IN。
  • - numeric：如果为true，该值将不会用引号括起来。
  • - 额外类型：如何将所有的额外组合。AND或OR。默认为AND。

注释

dahousecat的图片

dahousecat评论

额外数组的结构让我困惑了一会儿。

这是它需要看起来像：

function hook_views_query_alter(&$view, &$query) {
	$join = new views_join();
	$join->table = 'my_table';
	$join->field = 'my_field';
	$join->left_table = 'left_table';
	$join->left_field = 'left_field';
	$join->type = 'left';
	$join->extra = array(
		array(
			'field' => 'bundle',
			'value' => 'user',
		)
	);
	$query->add_relationship('relationship_name', $join, 'node');
}
phponwebsites的图片

phponwebsites评论

我使用下面的代码来改变视图查询。

function hook_views_query_alter(&$view, &$query) {
    $join = new views_join();
    $join->table = 'my_table';
    $join->field = 'my_field';
    $join->left_table = 'left_table';
    $join->left_field = 'left_field';
    $join->type = 'left';
    $join->extra = array(
        array(
            'field' => 'bundle',
            'value' => 'user',
        )
    );
    $query->add_relationship('relationship_name', $join, 'node');
}

现在我想在$ join-> extra中添加一个字段如何做到这一点？

sinasalek的头像

sinasalek评论道

如何写一个子查询联接？简单...

    $sub_query = db_select('workflow_node_history', 'sub_workflow_node_history_states');
    $sub_query->addField('sub_workflow_node_history_states', 'nid');
    $sub_query->addField('sub_workflow_states', 'weight', 'weight');
    $sub_query->addField('sub_workflow_states_old', 'weight' , 'old_weight');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states', 'sub_workflow_node_history_states.sid = sub_workflow_states.sid');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states_old', 'sub_workflow_node_history_states.old_sid = sub_workflow_states_old.sid');
    $sub_query->orderBy('sub_workflow_node_history_states.hid', 'DESC');

    $join = new views_join();
    $join->definition = array('table formula' => $sub_query, 'left_field' => 'nid', 'field' => 'nid', 'left_table' => 'node');
    $join->left_table = 'node';// or wathever table you want to join with
    $join->field = 'nid';
    $join->left_field = 'nid';// or wathever column you want to join with
    $join->type = 'LEFT';// if you want another join type play with this value

    // Do the actual join
    $this->query->table_queue['workflow_node_history_states'] = array (
      'alias' => 'workflow_node_history_states',// I believe this is optional
      'table' => $sub_query,
      'relationship' => 'node',
      'join' => $join,
    );
infinet的图片

infinet评论

这真的很有帮助...花了一段时间找到一个很好的解决方案。

infinet的图片

infinet评论

我最后使用这种技术来创建一个views_handler_field，用于列出产品的Drupal Commerce视图。自定义字段显示每个产品包含在任何活动购物车中的次数。

你可以在这里看到完整的解决方案http：//divingintodrupal8.com/#joining-a-views-query-to-a-derived-table-o ...

phponwebsites的图片

id.iot评论

如果你需要复杂的连接条件，你可以这样定义额外的参数：

$join = new views_join();
...
$join->extra = "table_1.field_1 = value_1 AND (table_2.field_2 = value_2 OR table_2.field_3 IS NOT NULL)";
phponwebsites的图片

RyanPrice评论

这是我能够成功添加一个连接：

<?php
  // Write the join conditions
  $join = new views_join();
  $join->table = 'field_data_field_blog_title';
  $join->left_table = 'blog';// or wathever table you want to join with
  $join->field = 'id';
  $join->left_field = 'id';// or wathever column you want to join with
  $join->type = 'LEFT';// if you want another join type play with this value

  // Do the actual join
  $query->table_queue['blog_title'] = array (
    'alias' => 'blog_title',// I believe this is optional
    'table' => 'field_data_field_blog_title',
    'relationship' => 'blog',
    'join' => $join,
  );
  $query->where[] = array(
    'conditions' => array(
      array(
        'field' => 'blog_title.language',
        'value' => $language,
        'operator' => '=',
      ),
      // add more conditions if you want to
    ),
    //'type' => 'OR' ,// I believe this is an operator for multiple conditions
  );
?>

感谢absoludo为此，取自：https：//www.drupal.org/node/2049051

anumathew的头像

anumathew评论

这对我有用

mchar的图片

mchar评论

对于那些试过上面的代码，并得到这个错误


注意：未定义的索引：键入views_plugin_query_default-> build_condition（）（E：\ drupal.projects \ edn.dev \ website \ sites \ all \ modules \ contrib \ views \ plugins \ views_plugin_query_default.inc的第1130行）。

只需在查询构造中添加这两行代码即可。


'args'=> array（），
'type'=>'AND'，

phponwebsites的图片

mjsilverman评论

这里的代码我用来添加一个JOIN到分类索引表直接传递给视图不是一个选项。

$ join_obj = new views_join（）; 
$ join_obj-> table ='taxonomy_index'; 
$ join_obj-> left_table ='node'; 
$ join_obj-> left_field ='nid'; 
$ join_obj-> field ='nid'; 
$ join_obj-> extra [] = array（
'field'=>'tid'，
'value'=> $ tid，
'numeric'=> TRUE，
）; 
$ join_obj-> type ='INNER'; 
$ join_obj-> definition = array（
'left_field'=>'nid'，
'field'=>'nid'，
'table'=>'taxonomy_index 
' 
left_table'=>'node'， ）; 
$ join_obj-> extra_type ='AND';

$ query-> table_queue ['taxonomy_index_value_0'] = array（
'table'=>“taxonomy_index”，
'num'=> 1，
'alias'=>'taxonomy_index_value_0'，
'join'=> $ join_obj，
'relationship' >'node'，
）;

generalredneck的头像

generalredneck评论

只是为了清楚（因为它不是显而易见的我看着这个页面），如上面的RyanPrice所示，运算符与字段，值和数字在同一级别。有一点funkiness与doc块被解析为这个页面的方式。这显然是当你看看源码包括/ handlers.inc，

phponwebsites的图片

gslexie评论

同样，文档似乎关闭'extra类型'。这是在顶层，并且连接对象的属性称为“extra_type”。使用像：$ join_object-> extra_type。

如果直接填充对象，而不是使用构造函数，则记录的默认值“AND”将不起作用; 如果有多个条件，extra_type必须手动填充，否则会出现查询错误。

来自 https://api.drupal.org/comment/54213#comment-54213



Posted by (not verified) on 
\views_join
 
class
A function class to represent a join and create the SQL necessary to implement the join.

A function class to represent a join and create the SQL necessary to implement the join.

This is the Delegation pattern. If we had PHP5 exclusively, we would declare this an interface.

Extensions of this class can be used to create more interesting joins.

join definition

  • table: table to join (right table)
  • field: field to join on (right field)
  • left_table: The table we join to
  • left_field: The field we join to
  • type: either LEFT (default) or INNER
  • extra: An array of extra conditions on the join. Each condition is either a string that's directly added, or an array of items:
  • - table: If not set, current table; if NULL, no table. If you specify a table in cached definition, Views will try to load from an existing alias. If you use realtime joins, it works better.
  • - field: Field or formula in formulas we can reference the right table by using %alias

     

    • - operator: defaults to =
  • - value: Must be set. If an array, operator will be defaulted to IN.
  • - numeric: If true, the value will not be surrounded in quotes.
  • - extra type: How all the extras will be combined. Either AND or OR. Defaults to AND.

Comments

dahousecat的图片

dahousecat commented 

The structure of the extra array confused me for a bit.

This is what it needs to look like:

function hook_views_query_alter(&$view, &$query) {
	$join = new views_join();
	$join->table = 'my_table';
	$join->field = 'my_field';
	$join->left_table = 'left_table';
	$join->left_field = 'left_field';
	$join->type = 'left';
	$join->extra = array(
		array(
			'field' => 'bundle',
			'value' => 'user',
		)
	);
	$query->add_relationship('relationship_name', $join, 'node');
}
phponwebsites的图片

phponwebsites commented 

I used below codes to alter views query.

function hook_views_query_alter(&$view, &$query) {
    $join = new views_join();
    $join->table = 'my_table';
    $join->field = 'my_field';
    $join->left_table = 'left_table';
    $join->left_field = 'left_field';
    $join->type = 'left';
    $join->extra = array(
        array(
            'field' => 'bundle',
            'value' => 'user',
        )
    );
    $query->add_relationship('relationship_name', $join, 'node');
}

Now i want to add one more field in $join->extra How to do this?

sinasalek的头像

sinasalek commented 

How to write a subquery join?! easy...

    $sub_query = db_select('workflow_node_history', 'sub_workflow_node_history_states');
    $sub_query->addField('sub_workflow_node_history_states', 'nid');
    $sub_query->addField('sub_workflow_states', 'weight', 'weight');
    $sub_query->addField('sub_workflow_states_old', 'weight' , 'old_weight');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states', 'sub_workflow_node_history_states.sid = sub_workflow_states.sid');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states_old', 'sub_workflow_node_history_states.old_sid = sub_workflow_states_old.sid');
    $sub_query->orderBy('sub_workflow_node_history_states.hid', 'DESC');

    $join = new views_join();
    $join->definition = array('table formula' => $sub_query, 'left_field' => 'nid', 'field' => 'nid', 'left_table' => 'node');
    $join->left_table = 'node';// or wathever table you want to join with
    $join->field = 'nid';
    $join->left_field = 'nid';// or wathever column you want to join with
    $join->type = 'LEFT';// if you want another join type play with this value

    // Do the actual join
    $this->query->table_queue['workflow_node_history_states'] = array (
      'alias' => 'workflow_node_history_states',// I believe this is optional
      'table' => $sub_query,
      'relationship' => 'node',
      'join' => $join,
    );
infinet的图片

infinet commented 

That was really helpful... took a while to find a good solution for this.

infinet的图片

infinet commented 

I ended up using this technique to create a views_handler_field for a Drupal Commerce View that lists products. The custom field displays the number of times each product is included in any active carts.

You can see the full solution here http://divingintodrupal8.com/#joining-a-views-query-to-a-derived-table-o...

phponwebsites的图片

id.iot commented 

If you have the need for complex join conditions, you can define the extra parameter like so:

$join = new views_join();
...
$join->extra = "table_1.field_1 = value_1 AND (table_2.field_2 = value_2 OR table_2.field_3 IS NOT NULL)";
phponwebsites的图片

RyanPrice commented 

This is how I was able to add a join successfully:

<?php
  // Write the join conditions
  $join = new views_join();
  $join->table = 'field_data_field_blog_title';
  $join->left_table = 'blog';// or wathever table you want to join with
  $join->field = 'id';
  $join->left_field = 'id';// or wathever column you want to join with
  $join->type = 'LEFT';// if you want another join type play with this value

  // Do the actual join
  $query->table_queue['blog_title'] = array (
    'alias' => 'blog_title',// I believe this is optional
    'table' => 'field_data_field_blog_title',
    'relationship' => 'blog',
    'join' => $join,
  );
  $query->where[] = array(
    'conditions' => array(
      array(
        'field' => 'blog_title.language',
        'value' => $language,
        'operator' => '=',
      ),
      // add more conditions if you want to
    ),
    //'type' => 'OR' ,// I believe this is an operator for multiple conditions
  );
?>

Credit to absoludo for this, taken from: https://www.drupal.org/node/2049051

anumathew的头像

anumathew commented 

This worked for me

mchar的图片

mchar commented 

For those who tried the above code and got this error


Notice: Undefined index: type in views_plugin_query_default->build_condition() (line 1130 of E:\drupal.projects\edn.dev\website\sites\all\modules\contrib\views\plugins\views_plugin_query_default.inc).

simply add those two lines of code at your query construction.


'args' => array(),
'type' => 'AND',

phponwebsites的图片

mjsilverman commented 

Here's the code I used to add a JOIN to the taxonomy index table when passing it directly to the view was not an option.

$join_obj = new views_join();
$join_obj->table = 'taxonomy_index';
$join_obj->left_table = 'node';
$join_obj->left_field = 'nid';
$join_obj->field = 'nid';
$join_obj->extra[] = array(
'field'=>'tid',
'value' => $tid,
'numeric'=> TRUE,
);
$join_obj->type = 'INNER';
$join_obj->definition = array(
'left_field'=>'nid',
'field'=>'nid',
'table'=>'taxonomy_index',
'left_table'=>'node',
);
$join_obj->extra_type = 'AND';

$query->table_queue['taxonomy_index_value_0'] = array(
'table' => "taxonomy_index",
'num' => 1,
'alias' => 'taxonomy_index_value_0',
'join' => $join_obj,
'relationship' => 'node',
);

generalredneck的头像

generalredneck commented 

Just to be clear (as it wasn't obvious to me looking at this page), as shown by RyanPrice above, Operator goes on the same level as field, value, and numeric. There is a bit of funkiness with the way the doc block was parsed to make this page. This is apparently when you look at the source includes/handlers.inc,

phponwebsites的图片

gslexie commented 

Likewise, the documentation seems to be off for 'extra type'. This is on the top level, and the property of the join object is called 'extra_type'. Use like: $join_object->extra_type.

If populating the object directly, instead of using the constructor, the documented default value of 'AND' will not work; extra_type must be populated manually if there are multiple conditions, else there will be query errors.


来自  https://api.drupal.org/comment/54213#comment-54213
普通分类: 
drupal
Powered by Drupal

友情链接

校园好文网

 www.shipingzhong.cn  个人技术网_前端_后台    备案号:苏ICP备18010659号-2