多个表的drupal sql条件子句? [英] drupal sql conditional clauses for multiple tables?

问题描述

我对下面的FAQ搜索模块进行了以下查询，该查询模块在Drupal 7中可以正常使用 会在两个表格中进行搜索:1)标题2)正文，但再没有包含一个.

I have the below query for FAQ search module modified that works in Drupal 7, which searches in two tables: 1) title 2) body but don't manage to include one more.

$term = strip_tags(drupal_substr($_POST['keyword'], 0, 100));

$query = "SELECT DISTINCT fq.title, fq.nid
 FROM {node} AS fq, {field_data_body} AS f
 WHERE fq.title LIKE :term
 OR fq.type LIKE :term
 OR f.body_value LIKE :term
 AND f.entity_id = fq.nid";

$result = db_query($query, array(':term' => '%%' . $term . '%%',':term' => '%%' . $term . '%%',':term' => '%%' . $term . '%%'));

我想再添加一个以在搜索中包括详细问题字段，但是我认为nid的链接是问题所在吗?我试图将两者都放在一个子句中，但似乎是错误的.请帮助:)

I'd like to add one more to include detailed questions field in the search, but I think the linking for nid is the issue? I tried putting both into a clause but seems to be wrong. Help Please :)

AND(fd.entity_id = fq.nid或fb.entity_id = fq.nid);

AND (fd.entity_id = fq.nid OR fb.entity_id = fq.nid)";

$term = strip_tags(drupal_substr($_POST['keyword'], 0, 100));
$query = "SELECT DISTINCT fq.title, fq.nid
    FROM {node} AS fq, {field_data_field_detailed_question} AS fd, {field_data_body} AS fb
    WHERE fq.title LIKE :term
    OR fd.field_detailed_question_value LIKE :term
    OR fb.body_value LIKE :term
    AND (fd.entity_id = fq.nid OR fb.entity_id = fq.nid)";
$result = db_query($query, array(':term' => '%%' . $term . '%%',':term' => '%%' . $term . '%%',':term' => '%%' . $term . '%%'));

$string = "";
while ($row = $result->fetchObject()) {
    $string .= "<a href='/" . drupal_get_path_alias('node/' . $row->nid) . "'>" . $row->title . "</a>"; 
}
echo $string;

更新:感谢Syscall协助我使他的方法可行(请参见聊天).

UPDATE: Thanks to Syscall who helped me make his approach work (see chat).

我还设法删除了空白(通过添加一个新变量并使用array_filter>，我猜问题是在array_map仍创建空字符串的同时拆分空白和修整)，并添加了一个条件以排除未发布的节点通过db_and()引用n.status = 1.

I also managed to remove empty whitespace (by adding a new variable and using array_filter > I guess the issue was splitting whitespace and trim at the same time that array_map still created empty strings.) and adding a condition to exclude nodes not published via db_and() referencing n.status = 1.

$terms = explode(' ', $term); // split using ' '
$terms = array_map('trim', $terms); // remove unwanted spaces
$termsfiltered = array_filter($terms);
$or = db_or();
foreach ($termsfiltered as $term) {
    $or->condition('fd.field_detailed_question_value', '%'.db_like($term).'%', 'LIKE');
    $or->condition('fb.body_value','%'.db_like($term).'%' , 'LIKE');
    $or->condition('n.title','%'.db_like($term).'%' , 'LIKE');
}
$and = db_and()->condition('n.status','1' , 'LIKE');

$query = db_select('node', 'n');
$query->fields('n');
$query->leftJoin('field_data_body' , 'fb', 'fb.entity_id=n.nid');
$query->leftJoin('field_data_field_detailed_question' ,'fd', 'fd.entity_id=n.nid');
$query->condition($or);
$query->condition($and);
$stmt = $query->execute(); // execute the query (returns the "statement" to fetch).

p.s.插入 var_dump($ variabletodump); 在更改代码和查看数组效果以解决问题时确实很有帮助.

p.s. inserting var_dump($variabletodump); was really helpful while changing the code and viewing the effects of the arrays to solve issues.

推荐答案

您不能在Drupal查询中链接leftJoin(或任何join)，因为 execute() 来运行"查询.

You cannot chain leftJoin (or any join) in Drupal queries, because leftJoin() returns the alias, not the query. Then you should use execute() to "run" the query.

$or = db_or()
  ->condition('fd.field_detailed_question_value', '%'.db_like($term ).'%', 'LIKE')
  ->condition('fb.body_value','%'.db_like($term ).'%' , 'LIKE');

$query = db_select('node', 'n');
$query->fields('n');
$query->leftJoin('field_data_body' , 'fb', 'fb.entity_id=n.nid');
$query->leftJoin('field_data_field_detailed_question' ,'fd', 'fd.entity_id=n.nid');
$query->condition($or);
$stmt = $query->execute(); // execute the query (returns the "statement" to fetch).

while ($row = $stmt->fetchObject()) {
    //..
}

您必须添加字段:

$query = db_select('node', 'n')->fields('n');

或

$query = db_select('node', 'n')
         ->addField('n','title')
         ->addField('n','nid');

这篇关于多个表的drupal sql条件子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！