mysql - Codeigniter:无法按降序显示结果和计数

Question

我有两张 table ，

instructions 
-------------
instruction_id  int(11)   Primary key (Auto_increment)
instruction      text
user_id          int(11)    Foreign key


instrunctionlike
-----------------
instrunction_likeid  int(11)  Primary key (Auto_increment)
instrunction_id      int(11)  Foreign Key

在指令和类指令表中有很多行，我想要的是，从类似指令的表中按降序顺序获取喜欢计数。 例如。 select*from instructionlike order by count(instructionlike.instruction_likeid)...

下面是我尝试过的，但我很困惑如何使用 desc 顺序实现行计数。请帮我解决这个问题

//-----------------------------------------------------------------------------------
public function fetch_agreed_desc_order_instruction($limit, $start) { 
     $this->load->database(); 
     $this->db->limit($limit, $start);
     $this->db->join('userdetails', 'userdetails.user_id = instructions.user_id'); 
      $this->db->join('instrunctionlike', 'instrunctionlike.instrunction_id = instructions.instruction_id');
     $this->db->order_by('instrunctionlike.instrunction_id', 'DESC');
     $this->db->group_by("instrunctionlike.instrunction_id"); 
     $query = $this->db->get("instructions"); 
    if ($query->num_rows() > 0) {
        foreach ($query->result() as $row) {
            $data[] = $row;
        }
        return $data;
    }
    return false; 
 }

示例输出:

             instructions                Likes
             .............                78
             .............                66
             .............                56
             .............                34
             .............                12
             .............                 1
             .............                 1
            .............                  0 

Answer 1

虽然您确实想按 instruction_id 进行分组，但您不想按该顺序进行排序。您想按 COUNT(instruction_id) 订购:

$this->db->order_by('COUNT(instrunctionlike.instrunction_id)', 'DESC');
$this->db->group_by("instrunctionlike.instrunction_id");