Codeigniter - 将mysql查询转换为CodeIgniter的活动记录查询

I'm trying to convert this SQL query into a codeigniter query

我正在尝试将此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

Thanks for the help kind regards

感谢您的亲切问候

1 个解决方案

#1

I am not sure why you can't figure this out yourself using the active record documentation, but:

我不确定为什么你不能使用活动记录文档来解决这个问题,但是:

$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');

and then

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

FYI, passing FALSE as the fourth parameter to the db->join() method will cause it not to escape the statement, so you should be careful if you're going to take external variables. This is, until CodeIgniter 3, the only way to do subqueries with active record without extending the active record class to add them.

FYI,将FALSE作为第四个参数传递给db-> join()方法将导致它不能转义语句,所以如果你要接受外部变量,你应该小心。直到CodeIgniter 3,这是使用活动记录进行子查询而不扩展活动记录类以添加它们的唯一方法。