如何从一列不在另一列codeigniter中获取记录

hi i am using codeigniter , and i have a table like this

嗨,我正在使用codeigniter,我有一个像这样的表

i want to get all records where PreferenceID value is not in PreferenceParentID column

我想获得PreferenceID值不在PreferenceParentID列中的所有记录

in this case i am fitering table also with the EntityID . and PreferenceParentID shoul be != 0

在这种情况下,我也适合使用EntityID。和PreferenceParentID应该是!= 0

suppose i filter by entityID 53

假设我按实体ID 53过滤

my results shoul be

我的结果应该是

Couture , Denims

because PreferenceID is not in PreferenceParentID in both cases . i tried with where_not_in() but could not do . please help

因为在两种情况下PreferenceID都不在PreferenceParentID中。我尝试过where_not_in()但是做不到。请帮忙

this is my query

这是我的疑问

    $table = $this->mastables['shop_profile_preferences'];
    $this->db->select('a.ProfilePreferenceID');
    $this->db->from($table." as a");

    $where2 = "(SELECT a.PreferenceParentID FROM ".$table.")";
    $this->db->where_not_in('a.PreferenceID', $where2);

    $this->db->where("a.EntityID",$shop_id);
    $this->db->where('a.PreferenceParentID !=',0);

    $query=$this->db->get();

    if($query->num_rows()>0)
    {
        return $query->result_array();
    }
    else
    {
        return FALSE;
    }

the result of my query is

我的查询结果是

Array
(
    [0] => Array
        (
            [ProfilePreferenceID] => 274
        )

    [1] => Array
        (
            [ProfilePreferenceID] => 275
        )

    [2] => Array
        (
            [ProfilePreferenceID] => 276
        )

)

how to use where_not_in() properly . or ids there any other methods . please help ..... thanks in advance .

如何正确使用where_not_in()。或ids有任何其他方法。请帮助.....提前感谢。

UPDATE

    $table = $this->mastables['shop_profile_preferences'];
    $this->db->select('a.ProfilePreferenceID,a.ProfilePreferenceValue');
    $this->db->from($table." as a");

    $this->db->where('a.PreferenceParentID !=',0);
    $this->db->where('a.PreferenceID NOT IN (SELECT a.PreferenceParentID FROM '.$table.')', NULL, FALSE);
    $this->db->where("a.EntityID",$shop_id);

    $query=$this->db->get();

    if($query->num_rows()>0)
    {
        return $query->result_array();
    }
    else
    {
        return FALSE;
    }

1 个解决方案

#1

You can't do that with CodeIgniter's where_not_in(). Because the second argument is supposed to be an array and not a string like that.

你不能用CodeIgniter的where_not_in()来做到这一点。因为第二个参数应该是一个数组而不是像这样的字符串。

What you can do is instead use the usual where() method.

你可以做的是使用通常的where()方法。

$this->db->where('a.PreferenceID NOT IN (SELECT a.PreferenceParentID FROM `table_name`)', NULL, FALSE);

In case you're wondering, the third argument in the code above is to prevent CodeIgniter from trying to protect the field and table name with backticks.

如果您想知道,上面代码中的第三个参数是阻止CodeIgniter尝试用反引号保护字段和表名。