Codeigniter：具有连接和限制的复杂查询

my query in codeigniter is like below,

$select = 'msg.*';
 $this->db->select($select, FALSE)
   ->from(TBL_MESSAGES . ' as msg')
   ->join(TBL_PRODUCT . ' as p', 'msg.ProductId=p.Id');        
   $query = $this->db->order_by('msg.Id', 'DESC')->get();
  if ($query->num_rows() > 0) 
  {
      return $query->result_array();
  }

Here I want in result ,all messages of first 10 products , but in above query if I place limit then It shows total 10 messages

Can any one help me to how to place limit for above query ?

To get you the desired result set there are 2 ways

1) First get ids of first 10 products and in your query just add a filter where p.id IN(ids....). This will involve 2 queries.

2) Would be join messages table with a sub select which selects 10 products like

select msg.*
from messages msg
join (
    select *
    from products
    order by id
    limit 10
) p
on msg.ProductId=p.Id

Not sure about how would you transform this query in active record format.

Hope this will help you :

Either do like this :

First collect your 10 products id and use them into your above query with where_in

$product_ids = $this->db->select('Id')->limit(10)->get(TBL_PRODUCT)->result_array();

/* to select most recent msg with product id and name do like this, correct
if product_name column is wrong*/

$select = 'msg.*,p.Id,p.product_name';

/*$select = 'msg.*';*/

$query = $this->db->select($select, FALSE)
   ->from(TBL_MESSAGES . ' as msg')
   ->join(TBL_PRODUCT . ' as p', 'msg.ProductId=p.Id')
   ->where_in('p.Id',$product_ids)   
   ->order_by('msg.Id', 'DESC')
   ->get();
  if ($query->num_rows() > 0) 
  {
      return $query->result_array();
  }

Or you can get all the product ids and loop through it with where clause to get the desired result

/* to select most recent msg with product id and name do like this, correct
if product_name column is wrong*/

$select = 'msg.*,p.Id,p.product_name';

$product_ids = $this->db->select('Id')->get(TBL_PRODUCT)->result_array();
if ( !empty($product_ids))
{
   foreach($product_ids as $pid)
   {
        $query = $this->db->select($select, FALSE)
                 ->from(TBL_MESSAGES . ' as msg')
                 ->join(TBL_PRODUCT . ' as p', 'msg.ProductId=p.Id')
                 ->where('p.Id',$pid)   
                 ->order_by('msg.Id', 'DESC')
                 ->get();
        if ($query->num_rows() > 0) 
        {
            $data[] = $query->result_array();
        }   
   }
return $data;
}

For more : https://www.codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data