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