多个加入代码点火器不要 wortk

Question

你能帮我修复我的错误吗？

我尝试加入三个表：用户、粉碎和 Disrush。

但是使用此代码找不到任何结果。

数据库：

• 用户：id和东西
• 暗恋：id、user_id、user_id_to
• Discrush： id， user_id， user_id_to

查询：

SELECT
*
FROM
`users`
RIGHT JOIN `discrush` ON `users`.`id` = `discrush`.`user_id_to`
RIGHT JOIN `crush` ON `users`.`id` = `discrush`.`user_id_to`
WHERE
`users`.`sexe` = 'female'
AND `users`.`valid_admin` = '1'
AND `users`.`id` NOT IN('81')
AND `crush`.`user_id_to` is NULL
OR `users`.`sexe` = 'female'
AND `users`.`valid_admin` = '1'
AND `users`.`id` NOT IN('81')
AND `discrush`.`user_id_to` is NULL
ORDER BY
RAND()

型：

function GetUserForCrushRoom($my_user_id, $sexe)
{
$this->db->select('*');
$this->db->from('users');
$this->db->join('discrush', 'users.id = discrush.user_id_to', 'right');
$this->db->join('crush', 'users.id = discrush.user_id_to', 'right');
$this->db->where('users.sexe =', $sexe);
$this->db->where('users.valid_admin =', '1');
$this->db->where_not_in('users.id', $my_user_id);
$this->db->where('crush.user_id_to is NULL');
$this->db->or_where('users.sexe =', $sexe);
$this->db->where('users.valid_admin =', '1');
$this->db->where_not_in('users.id', $my_user_id);
$this->db->where('discrush.user_id_to is NULL');
$this->db->order_by('users.id', 'RANDOM');
$query = $this->db->get();
return $query->row();
}

控制器：

public function crush_room()
{
$my_user_id = $this->session->userdata('user_id');
if($my_user_id != '')
{
$data['my_user_id'] = $my_user_id;
$my_user = $this->home->get_user($my_user_id);
$sexe = $my_user->sexe;
if($my_user->sexe == 'male')
{
$sexe = 'female';
}else{
$sexe = 'male';
}
$data['user'] = $this->home->GetUserForCrushRoom($my_user_id, $sexe);
if($data['user']){
$data['user_crush'] = $this->home->CheckCrush($my_user_id, $data['user']->id);
$data['user_block'] = $this->home->CheckBlockUser($my_user_id, $data['user']->id);
}
$data['users'] = $this->home->get_all_user_crush($my_user_id);
$this->load->view('crush_room', $data);
}
else{
redirect('login');
}
}

Answer 1

我得到了修复！

将查询替换为此...

$sql = "SELECT * FROM users U WHERE U.sexe = ? AND U.valid_admin = '1' AND U.id NOT IN (SELECT user_id_to FROM discrush UNION SELECT user_id_to FROM crush)";
$query = $this->db->query($sql, array($sexe));
return $query->row();

Answer 2

尝试加入用户暗恋。

右加入crushusers。id=crush.user_id_to