mysql - Codeigniter - 将 mysql 查询转换为 CodeIgniter 的事件记录查询

Question

我正在尝试将此 SQL 查询转换为 codeigniter 查询

SELECT 
  uploads.EMAIL
FROM
  uploads 
  JOIN (

    SELECT EMAIL, COUNT(*) as num FROM uploads GROUP BY EMAIL
  ) c ON uploads.EMAIL = c.EMAIL
ORDER BY 
  c.num DESC, 
  EMAIL ASC

感谢帮助亲切的问候

Answer 1

我不确定为什么您无法使用 active record documentation 自己解决这个问题，但是:

$this->db->select('uploads.EMAIL');
$this->db->from('uploads');
$this->db->join('(SELECT EMAIL, COUNT(*) as num FROM uploads GROUP BY EMAIL) c','uploads.EMAIL = c.EMAIL','',FALSE);
$this->db->order_by('c.num desc, uploads.EMAIL asc');

然后

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

仅供引用，将 FALSE 作为第四个参数传递给 db->join() 方法将导致它不会对语句进行转义，因此如果要采用外部变量，则应小心.在 CodeIgniter 3 之前，这是在不扩展事件记录类以添加它们的情况下对事件记录进行子查询的唯一方法。