Zend 选择不在

2021-12-29 00:00:00 php mysql zend-framework zend-db

我有两个相关数据的表，我想从一个表中选择另一个表中不存在的所有记录，加上相关表上的一些其他条件，如下(123仅用于说明目的):

I have two tables with related data, and I want to select all the records from one table which do not exist in the other table, plus some other criteria on the related table, as follows (123 is just for illustration purposes):

TABLE A
    ID
    SOMETHING

TABLE B
    TABLE_A_ID
    TABLE_C_ID
    SOMETHING

我的查询，直接针对数据运行，如下

My query, run directly against the data, would be as follows

SELECT A.SOMETHING 
    FROM A 
    WHERE A.ID NOT IN (
        SELECT 
            B.TABLE_A_ID AS ID 
            FROM B 
            WHERE TABLE_C_ID = 123
    );

我如何在 Zend 中运行它?

How can I run this in Zend?

推荐答案

可以直接运行sql，使用$db->query();你的将是:

You can run direct sql, using $db->query(); yours would simply be:

$results = $db->query("SELECT A.SOMETHING 
FROM A 
WHERE A.ID NOT IN (
    SELECT 
        B.TABLE_A_ID AS ID 
        FROM B 
        WHERE TABLE_C_ID = ?
)", $id);

编辑:要回答这是否可以用对象表示法来完成，是:

EDIT: To answer whether this can be done with the object notation, yes:

$sub_select = $zdb->select()
                  ->from("b", array("table_a_id AS id"))
                  ->where("table_c_id = ?", 'a');
$select = $zdb->select()
              ->from("a", array("something"))
              ->where("id NOT IN ?", $sub_select);
print $select->__toString();

给予

SELECT `a`.`something` FROM `a`
WHERE (id NOT IN
    (SELECT `b`.`table_a_id` AS `id` FROM `b` WHERE (table_c_id = 'a')))

相关文章