douzhou7037 2014-05-25 17:53
浏览 70
已采纳

Codeigniter - 限制查询“where_in”中的项目

I would like limiting the results for every id (only 3 results for array id).

If I add:

$this->db->limit(3);

I receive three records in total; but I want 3 record for every id.

How can I do?

<?php

$array = array('id' => 1, 'id' => 2, 'id' => 3);

class Your_model extends CI_Model
{
    public $db;
    public function __construct()
    {
       parent::__construct();
       $this->db = $this->load->database('default',true);     
    }

    public function function_name()
    {
        $this->db->select('file_name');
        $this->db->from('images_table');
        $this->db->where_in('id', $array);            
        $query = $this->db->get();                  

        $result = "";
        if($query->num_rows() > 0)
            $result = $query->result_array();
        else
            $result = "No result";

        print_r($result);
    }
}
  • 写回答

1条回答 默认 最新

  • drgbpq5930 2014-05-25 18:28
    关注

    There are two ways to get n records per group i.e each id in your array is a group which can have many file_names associated to it.

    First approach

    Using mysql's GROUP_CONCAT with a combination of SUBSTRING_INDEX.What it does GROUP_CONCAT will concatenate all the values in your column i.e file_name with a separator which you can define as you wish to, in below query i have used double pipe ||,second SUBSTRING_INDEX with limit of 3 will get the 3 places of provided separator from the string provided by GROUP_CONCAT

    But beaware of that fact GROUP_CONCAT has a default limit of 1024 characters to concat so if there many file_names per id they will be truncated,but default limit of GROUP_CONCAT can be increased by setting a session variable which is defined in GROUP_CONCAT's manual

    SELECT 
    SUBSTRING_INDEX(
    GROUP_CONCAT( file_name SEPARATOR '||'),
    '||',3) images
    FROM images_table p
    GROUP BY p.id
    

    You can view the sample results in below provided demo

    Now at your application level you just have to loop through the products and split the images by separator

    Active record query

    $results=$this->db->select('SUBSTRING_INDEX(
    GROUP_CONCAT( file_name SEPARATOR '||'),
    '||',3) images',FALSE)
         ->from('images_table p')
         ->group_by('p.id')
         ->get()
         ->result_array();
    
    foreach($results as $row):
    $images =explode('||',$row['images ']);
        foreach($images as $m):
        ....
        endforeach;
    endforeach;
    

    Demo

    Second Approach

    Other method you can use user defined variables in your query to give rank to the items of each group

    SELECT file_name,rank FROM(
    SELECT file_name,
    @r:= CASE WHEN id = @g THEN @r+1  ELSE @r:=1 END rank 
    ,@g:=id
    FROM images_table,(SELECT @r:=0,@g:=0) t
    ORDER BY id
      ) t
     WHERE rank < 4
    

    Now this query can't be written using active record so you have to use simple query() function

        $results=$this->db
               ->query('SELECT file_name,rank FROM(
                        SELECT file_name,
                        @r:= CASE WHEN id = @g THEN @r+1  ELSE @r:=1 END rank 
                        ,@g:=id
                        FROM images_table,(SELECT @r:=0,@g:=0) t
                        ORDER BY id
                        ) t
                        WHERE rank < 4');
    

    You can view the sample results in below provided demo

    Demo

    Edit from comments

    To pass your array of ids just use active record's where_in()

    $results=$this->db->select('SUBSTRING_INDEX(
    GROUP_CONCAT( file_name SEPARATOR '||'),
    '||',3) images',FALSE)
         ->from('images_table p')
         ->where_in('p.id', $array)
         ->group_by('p.id')
         ->get()
         ->result_array();
    

    And for raw query you can do so

        $results=$this->db
               ->query('SELECT file_name,rank FROM(
                        SELECT file_name,
                        @r:= CASE WHEN id = @g THEN @r+1  ELSE @r:=1 END rank 
                        ,@g:=id
                        FROM images_table,(SELECT @r:=0,@g:=0) t
                        WHERE id IN('.join(",",$array).')
                        ORDER BY id
                        ) t
                        WHERE rank < 4');
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题