SQL活动记录查询与where空子句联接 - SQL active record query join with where empty clause

SQL active record query join with where empty clause

我正在使用CodeIgniter和Active Record（mysql数据库）编写一个特殊的查询。以下是工作查询：

$this->db->select('moo_aauth_users.*', false);
    $this->db->select('moo_tasks.id AS task_id, moo_tasks.user_id, moo_tasks.task_status, moo_tasks.task_type, moo_tasks.skill_category, moo_tasks.tasker_hired, moo_tasks.task_city_id, moo_tasks.completed, moo_tasks.hours_spend, moo_tasks.additional_fees, moo_tasks.total_price, moo_tasks.paid, moo_tasks.tasker_asked_id, moo_tasks.tasker_asked_response, moo_tasks.tasker_asked_rate, moo_tasks.title, moo_tasks.description, moo_tasks.expiration_date, moo_tasks.telephone, moo_tasks.zipcode, moo_tasks.address, moo_tasks.created_stamp, moo_tasks.updated_stamp, moo_tasks.assigned_stamp, moo_tasks.completed_stamp, moo_tasks.provider, moo_tasks.country, moo_tasks.task_zone', false);
    $this->db->from('moo_tasks');
    $this->db->join('moo_aauth_users', 'moo_aauth_users.id = moo_tasks.user_id');
    $this->db->where('moo_tasks.task_status', TASK_STATUS_PENDING);

但现在，我想在另一个表的基础上添加一个特殊的where子句。

我有一个moo_offers表，其中包含与任务相对应的不同offer。在我的查询中，我想检查我正在检索的任务是否在moo_offers表中不包含相应的报价。

示例：

moo_tasks:id 1/。。。moo_tasks:id 2/。。。

moo_offers:task_id 1/。。

对于查询，将只检索moo_tasks.id=2，而不是id 1，因为它在第二个表中有报价。

我在查询中尝试了多次思考，但都不起作用：

$this->db->join('moo_offers', 'moo_offers.id_task = moo_tasks.id');
$this->db->where('moo_offers.id_tasker NOT IN (SELECT `id_tasker` FROM `moo_offers` WHERE id_task = moo_offers.id_task && id_tasker = '.$filter['tasker_id'].')', NULL, FALSE);

你有什么想法吗？谢谢

 $this->db->select('*');
            $this->db->from('tbl_user');
            $this->db->join('tbl_userinfo', 'tbl_user.user_id = tbl_userinfo.user_id');
            $this->db->where('tbl_user.user_id', $data);
            $query = $this->db->get();
            $result = $query->result();
            return $result;

这就是我所做的，而且效果很好。