codeigniter活动记录选择，左连接，数 [英] Codeigniter active record select, left join, count

问题描述

我有一个表格，显示一个数据库查询的结果，这些结果可以具有ajoined到他们的许多其他资产和我想要找到表示多少资产每elemement具有的一种方式。例如我的表是英格兰地区的其它表有地方住用户目前我有这个code，

  $这个 - ＆GT; DB-＆GT;选择（*）;
$这个 - ＆GT; DB-肽从（'地方'）;
$这个 - ＆GT; DB-＆GT;加入（'用户，places.place_id = user.place_id，左'）;

$这个 - ＆GT; DB-＆GT;获得（）;
 

我有越来越查询返回的地名，生活在那个地方的用户数的问题，这有可能吗？

解决方案

 选择places.place_id，
       COUNT（*）作为UsersAtThisPlace
   从
       的地方，
       用户
   哪里
       places.place_id = users.place_id
   通过...分组
       places.place_id
 

不知道如何通过你的PHP实现，但它应该是简单，只要全部在上面的查询您的

$这个 - > DB->选择（上面的整个字符串的例子）;

此外，如果你有从地方台等描述性元素，你可以在数（只是为了清楚起见），然后添加这些为好，但也必须将其包含在GROUP BY ......这样的作为。

 选择places.place_id，
       places.description，
       places.otherfield，
       COUNT（*）作为UsersAtThisPlace
   从
       的地方，
       用户
   哪里
       places.place_id = users.place_id
   通过...分组
       places.place_id，
       places.description，
       places.otherfield
 

I have a form that shows results from a database query, these results can have many other assets ajoined to them and I wanting to find a way of showing how many assets each elemement has. For example my table is of areas of england an other table has where the users live I current have this code,

$this->db->select('*');
$this->db->from('places');
$this->db->join('users, places.place_id = user.place_id, left');

$this->db->get();

The issue I am having is getting the query to return the place name and the number of users living in that place, it is possible?

解决方案

select places.place_id,
       count(*) as UsersAtThisPlace
   from
       places,
       users
   where 
       places.place_id = users.place_id
   group by
       places.place_id

Don't know exactly how to implement via your PHP, but it should be as simple as the above query all within your

$this->db->select( "entire string example above" );

Additionally, if you had other descriptive elements from the places table, you could add those as well before the count (just for clarity), but would also have to include them in the group by... such as

select places.place_id,
       places.description,
       places.otherfield,
       count(*) as UsersAtThisPlace
   from
       places,
       users
   where 
       places.place_id = users.place_id
   group by
       places.place_id,
       places.description,
       places.otherfield

这篇关于codeigniter活动记录选择，左连接，数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！