Get N redords from DB for each ID in array (Codeigniter)

Question

Hopefully someone here can help as I've spent 2 days searching. Basically I am trying to send an array of user ids to a CI model and retrieve the last 10 transactions for each id.

I can limit the total number of records, but I want to limit to each id instead, and cant figure out how this is done?

I have read that ROW_NUMBER() may be what I'm looking for? But I'm unsure how to turn that from SQL into something suitable for using in a CI model. Below is the code I have at the moment:

function getTrans($cData, $date){

    $this->db->select('id, userId, date');
    $this->db->from('trans');
    $this->db->where_in('userId', $cData);
    if($date != 0){
        $this->db->where('date >=', $date);
    }
    
    $this->db->order_by('id','asc');
    
    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

As you can see I'm using WHERE_IN to cycle the array of ids, but is there a way to add a limit to each id called instead of just a limit on the whole amount?

I did try adding a loop in the model but it would just keep throwing me error 500s (I think its to do with the way the query is generated??)

Any help is much appreciated

Answer 1

You can use $this->db->query() with the following query:

$query = $this->db->query("
SELECT *
    FROM 
    (
        SELECT
            trans.*,
            IF(@sameClass = userId, @rn := @rn + 1,
                 IF(@sameClass := userId, @rn := 1, @rn := 1)
            ) AS rank
        FROM trans
        CROSS JOIN (SELECT @sameClass := 0, @rn := 1 ) AS var
        WHERE userId IN (" . implode(', ', $cData) . ")  
        AND date >= {$date}
        ORDER BY userId, id
    ) AS t
    WHERE t.rank <= 10
    ORDER BY t.userId, t.rank
");