codeigniter在同一查询中从子表中获取多个值

问题描述：

我在尝试从同一查询中的子表中获取多个值时遇到了一些问题。我有dealdetails和dealImages表，其中dealImages可以包含来自dealdetails表的多个值和deal-id表。编写查询一样，codeigniter在同一查询中从子表中获取多个值

$dealDetailsArray  = array(); 
     $this->db->select('d.dealId,d.dealTitle,d.slug,d.dealDetails,d.extraDetails,d.aditionalDetails,d.status,d.dateAdded,d.categoryId,d.dealSubCategory,d.siteId,d.dealBrandId,d.isPinned,d.priceId,d.price,d.startDate,d.startTime,d.endDate,d.endTime,d.addedTime,d.dealUrl,d.adminAffiliatePrice,d.cashbackType,d.cashbackAmountType,d.cashbackAmount,d.shippingType,d.NumberOfClicked,d.priceType,d.discountPrice,d.discountPercentage,d.deal_location,d.howtousethisoffer,d.cancellationpolicy,d.deal_submittedby,d.dealType,d.totalavailabledeals,d.numberofdealused,d.showinhomescreen,d.showinmenu,d.isHomeScreenBigDeal,di.imageId,di.imageUrl,di.thumbImage,di.imageOrder,di.status,di.dealId,di.addedOn,di.imageobjId,di.normalimageurl,di.imgobjext,di.imgobject,di.imgisdefault'); 
     $this->db->from('dealdetails as d'); 
     $this->db->join('dealImages di', 'di.dealId = d.dealId','left'); 
     $this->db->where('d.endTime >= ',date('Y-m-d H:i:s')); 
     $this->db->group_by('d.dealId'); 
     $this->db->order_by("d.dealId", "desc"); 
     $query = $this->db->get(); 

     if($query->num_rows()>0) { 
      $dealDetailsArray = $query->result_array(); 
      $query->free_result(); 
     } 
     return $dealDetailsArray;

这始终是从dealImages返回一个值一样

 [deal_submittedby] => 0 
     [dealType] => 1 
     [totalavailabledeals] => 0 
     [numberofdealused] => 0 
     [showinhomescreen] => 1 
     [showinmenu] => 0 
     [isHomeScreenBigDeal] => 1 
     [imageId] => 22 
     [imageUrl] => http://localhost/codeIgniter/uploads/cover_image/storeimages/general/general_1493562480.momo.jpg 
     [thumbImage] => http://localhost/codeIgniter/uploads/cover_image/storeimages/thumb/thumb_1493562480.momo.jpg 
     [imageOrder] => 1 
     [addedOn] => 2017-04-30 14:28:43 
     [imageobjId] => 1493562480 
     [normalimageurl] => http://localhost/codeIgniter/uploads/cover_image/storeimages/normal/normal_1493562480.momo.jpg 
     [imgobjext] => momo.jpg 
     [imgobject] => 1493562480.momo.jpg 
     [imgisdefault] => 1

请出示与伪记录你的表模式.. – Nidhi

查询返回来自dealImages的单个值，因为您在用户组中查询。 –

@NashirUddin但如果不会groupby dealid并且在dealimage表中有10个图像，那么它将在该父数组中返回10个值...如果我没有错 – santanu

答

然后添加上选择查询GROUP_CONCAT返回用逗号值（，）分隔

这样的代码：

$dealDetailsArray  = array(); 
     $this->db->select('d.dealId,d.dealTitle,d.slug,d.dealDetails,d.extraDetails,d.aditionalDetails,d.status,d.dateAdded,d.categoryId,d.dealSubCategory,d.siteId,d.dealBrandId,d.isPinned,d.priceId,d.price,d.startDate,d.startTime,d.endDate,d.endTime,d.addedTime,d.dealUrl,d.adminAffiliatePrice,d.cashbackType,d.cashbackAmountType,d.cashbackAmount,d.shippingType,d.NumberOfClicked,d.priceType,d.discountPrice,d.discountPercentage,d.deal_location,d.howtousethisoffer,d.cancellationpolicy,d.deal_submittedby,d.dealType,d.totalavailabledeals,d.numberofdealused,d.showinhomescreen,d.showinmenu,d.isHomeScreenBigDeal,di.imageId,di.GROUP_CONCAT(di.imageUrl) AS imageUrl,di.thumbImage,di.imageOrder,di.status,di.dealId,di.addedOn,di.imageobjId,di.normalimageurl,di.imgobjext,di.imgobject,di.imgisdefault'); 
     $this->db->from('dealdetails as d'); 
     $this->db->join('dealImages di', 'di.dealId = d.dealId','left'); 
     $this->db->where('d.endTime >= ',date('Y-m-d H:i:s')); 
     $this->db->group_by('d.dealId'); 
     $this->db->order_by("d.dealId", "desc"); 
     $query = $this->db->get(); 

     if($query->num_rows()>0) { 
      $dealDetailsArray = $query->result_array(); 
      $query->free_result(); 
     } 
     return $dealDetailsArray;

codeigniter在同一查询中从子表中获取多个值

相关推荐