Drupal 7 WHERE使用OR和AND

Writing a custom module trying to look up comments. I have a SQL statement as follows:

编写自定义模块试图查找注释。我有一个SQL语句如下:

    $client_comment = db_query("SELECT comment.subject AS comment_subject, 
comment.cid AS cid, 
comment.nid AS comment_nid, 
comment.created AS comment_created
FROM 
{comment} comment
INNER JOIN {node} node_comment ON comment.nid = node_comment.nid
INNER JOIN {field_data_field_associated_client} associated_client ON comment.cid = associated_client.entity_id
WHERE (( (associated_client.field_associated_client_nid = '$nid') 
OR (comment.nid = '$nid') ) 
AND (comment.status <> '0') 
AND (node_comment.status = '1') ) 
ORDER BY comment_created DESC
LIMIT 10 OFFSET 0");

Ideally, this is supposed to return the following:

理想情况下,这应该返回以下内容:

However, I get the following:

但是,我得到以下内容:

For some reason, it is only returning the "associated_client" result instead of the "comment.nid" result. Here is the full statement I'd like to get working. I know it's a SMH moment, but any help would be great.

出于某种原因,它只返回“associated_client”结果而不是“comment.nid”结果。以下是我想要完成的完整陈述。我知道这是一个SMH时刻,但任何帮助都会很棒。

---

Per request, here is the code I'm using to cycle through the returning array:

每个请求,这是我用来遍历返回数组的代码:

foreach($client_comment as $item) {
        print_r($item);
        print '<br /><br />';
    }

---

Here are 2 queries that print all of the data to the screen separately. I want to combine these two queries in to 1 query:

以下是2个查询,分别将所有数据打印到屏幕上。我想将这两个查询组合成1个查询:

    $client_comment = db_query("SELECT comment.subject AS comment_subject, comment.cid AS cid, comment.nid AS comment_nid, comment.created AS comment_created
FROM 
{comment} comment
INNER JOIN {node} node_comment ON comment.nid = node_comment.nid
WHERE (( (comment.status <> '0') AND (node_comment.status = '1') AND (comment.nid = '$nid')  ))
ORDER BY comment_created DESC
LIMIT 10 OFFSET 0");
    // Get the comments associated TO the client from Caregiver node.
    $client_associated_comment = db_query("SELECT comment.subject AS comment_subject, comment.cid AS cid, comment.nid AS comment_nid, comment.created AS comment_created
FROM 
{comment} comment
INNER JOIN {node} node_comment ON comment.nid = node_comment.nid
INNER JOIN {field_data_field_associated_client} associated_client ON comment.cid = associated_client.entity_id
WHERE (( (comment.status <> '0') AND (node_comment.status = '1') AND (associated_client.field_associated_client_nid = '$nid')  ))
ORDER BY comment_created DESC
LIMIT 10 OFFSET 0");
    foreach($client_comment as $item) {
        $client_comment_array[] = $item;
    }
    foreach ($client_associated_comment as $item) {
        $client_comment_array[] = $item;
    }
    foreach($client_comment_array as $item) {
        print_r($item);
        print '<br /><br />';
    }

1 个解决方案

#1

Alright, I solved it by using LEFT JOIN instead of INNER JOIN. Resulting query:

好吧,我通过使用LEFT JOIN而不是INNER JOIN来解决它。结果查询:

$client_comment = db_query("SELECT comment.subject AS comment_subject, 
comment.cid AS cid, 
comment.nid AS comment_nid, 
comment.created AS comment_created
FROM 
{comment} comment
INNER JOIN {node} node_comment ON comment.nid = node_comment.nid
LEFT JOIN {field_data_field_associated_client} associated_client ON comment.cid = associated_client.entity_id
WHERE (( (associated_client.field_associated_client_nid = '$nid') 
OR (comment.nid = '$nid') ) 
AND (comment.status <> '0') 
AND (node_comment.status = '1') ) 
ORDER BY comment_created DESC
LIMIT 10 OFFSET 0");
    foreach($client_comment as $item) {
        print_r($item);
        print '<br /><br />';
    }