codeigniter活动记录group_by返回联接表记录ID的随机顺序 [英] codeigniter active records group_by returns random ordering of joined table record id

问题描述

我正在使用Codeigniter，我想做的是一个返回联接表（item_gallery）gallery_id最低的查询。而主查询按desc排序的项目post_date

I'm using Codeigniter, what I want to do is a query that returns joined table (item_gallery) gallery_id is lowest value. while main query ordered by desc of items post_date

下面的代码group_by选择item_gallery gallery_id的随机值。但是我想要item_gallery gallery_id的最低值。

the below code group_by select random value of item_gallery gallery_id. but i want the lowest value of item_gallery gallery_id.

  public function shopItems($id) {

    $this->db->select("*");
    $this->db->from('items');
    $this->db->join('item_gallery', 'items.id = item_gallery.item_id', 'left');
    $this->db->where('items.user_id', $id);     
    $this->db->order_by('items.post_date', 'Desc');
    $this->db->group_by("item_gallery.item_id");
    $query = $this->db->get();
    return $query->result();
}

数据库结构

项目表

|    id    |  slug  |  user_id  |  post_date |

|    12    |  test  |    111    |  12/5/2017 |

物品图库

| gallery_id |  item_id  |     image     | 

|     121    |    12     |  profile.png  |   -- i want this record selected
|     122    |    12     |  gallery.png  | 

推荐答案

public function shopItems($id, $limit, $start) {

date_default_timezone_set('Asia/Colombo');
$expire = date('Y-m-d');
$this->db->select("*,(SELECT image FROM item_gallery WHERE item_id = 
items.id ORDER BY gallery_id ASC LIMIT 1) AS profile_img");
$this->db->from('items');
$this->db->join('item_gallery', 'items.id = item_gallery.item_id', 'left');
$this->db->where('items.exp_date >', $expire);
$this->db->group_start();
$this->db->where("items.status = 'yes'");
$this->db->or_where("items.status = 'edit'");
$this->db->group_end();
$this->db->where('items.user_id', $id);
$this->db->limit($limit, $start);
$this->db->order_by('items.post_date', 'Desc');
$this->db->group_by("item_gallery.item_id");
$query = $this->db->get();
return $query->result();
}

这篇关于codeigniter活动记录group_by返回联接表记录ID的随机顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！