duanlang0025
2016-05-03 14:00
浏览 54
已采纳

如何在Codeigniter Active记录中实现“WHERE NOT IN”查询?

I have a MySQL query which is this:

SELECT * FROM tbl_post WHERE tbl_post.post_id NOT IN 
(SELECT tbl_readsave.post_id FROM tbl_readsave)

I want to convert it into Codeigniter Active records, so I used the following code segment:

    $this->db->select('tbl_readsave.post_id');
    $queryReadSave = $this->db->get('readsave');
    $this->db->where_not_in('post_id', $queryReadSave->result_array());
    $queryNewPost = $this->db->get('readsave');
    if($queryNewPost->num_rows()>0)
    {
        return $queryNewPost->result_array();
    }
    else
        return false;

However, the code throws me an error, which is like the following:-

Error Number: 1054

Unknown column 'Array' in 'where clause'

SELECT * FROM (`tbl_readsave`) WHERE `post_id` NOT IN (Array)

Filename: /var/www/html/teamF/tharjumal/models/webservice_model.php

Line Number: 28

How can I convert the above stated query into Codeigniter Active Records format?

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • douwenpin0428 2016-05-03 14:08
    最佳回答

    $queryReadSave->result_array() returns an array of arrays. You can't use that in where_not_in.

    You need to loop over that and create a flat array of the IDs you (don't) want.

    $this->db->select('post_id');
    $queryReadSave = $this->db->get('readsave');
    
    $postIDs = $queryReadSave->result_array();
    
    $this->db->where_not_in('post_id', array_column($postIDs, 'post_id'));
    $queryNewPost = $this->db->get('post');
    

    array_column() only exists in PHP 5.5+. If you are on a lower version, you'll need to do something like this:

    $this->db->select('post_id');
    $queryReadSave = $this->db->get('readsave');
    
    $postIDs = array_map(function($a){
        return $a['post_id'];
    }, $queryReadSave->result_array());
    
    $this->db->where_not_in('post_id', $postIDs);
    $queryNewPost = $this->db->get('post');
    

    P.S. Your second table is called post, right? You'll need to update the query to use the right table.

    Update: Your original query uses a subquery which is not natively supported by CodeIgniter. If you want to try this all as one query, you can use the Subquery library I created (https://github.com/NTICompass/CodeIgniter-Subqueries).

    $this->db->select('*');
    $this->db->from('post');
    
    $sub = $this->subquery->start_subquery('where_in');
    $sub->select('post_id')->from('readsave');
    
    $this->subquery->end_subquery('post_id', FALSE);
    $queryNewPost = $this->db->get();
    
    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题