CodeIgniter，按单独的连接表中的行数排序？

I have 4 tables that link together...

我有4个表链接在一起......

Firstly the hotels table

首先是酒店表

• hotel_id
• town_id
• hotel_name

Then the towns table:

然后是城镇表:

• town_id
• region_id
• town_name

Then the regions table:

那么区域表:

• region_id
• country_id
• region_name

Finally the countries table

最后是国家表

• country_id
• country_name

What I need to do is list the towns in order of how many hotels there are within that town.

我需要做的是按照该镇内有多少家酒店的顺序列出城镇。

The reason I have included the regions table and the countries table is because, when displaying that town, I need to display the country it's from. This can only be obtained via the regions table..

我包含区域表和国家/地区表的原因是,在显示该城镇时,我需要显示它所在的国家/地区。这只能通过区域表获得。

Therefore using the active records in CodeIgniter I have done this so far:

因此,使用CodeIgniter中的活动记录到目前为止我已经这样做了:

$this->db->join('regions','towns.town_region_id = regions.region_id');
$this->db->join('countries','regions.region_country_id = countries.country_id');
$query = $this->db->get('towns');

foreach ($query->result() as $row) {
     echo "<li>";
     echo "$row->town_name, $row->country_name";
     echo "</li>";
}

This outputs:

• London, United Kingdom

伦敦,英国

• Washington, United States

华盛顿,美国

• New York, United States

美国纽约

• Moscow, Russia
• etc, etc

Each one of these cities have hotels in them. All I need now is to order them by how many hotels there are in each town..

这些城市中的每一个都有酒店。我现在需要的只是按每个城镇的酒店订购它们。

Any help would be much appreciated! Thanks.

任何帮助将非常感激!谢谢。

2 个解决方案

#1

$this->db->select('t.*,c.*,COUNT(h.hotel_id) AS nhotels');
$this->db->from('towns t');
$this->db->join('hotels h','h.town_id = t.town_id');
$this->db->join('regions r','t.town_region_id = r.region_id');
$this->db->join('countries c','r.region_country_id = c.country_id');
$this->db->group_by('t.town_id');
$this->db->order_by("nhotels",'DESC');
$query = $this->db->get();

which will produce the following query:

这将产生以下查询:

SELECT `t`.*, `c`.*, COUNT(h.hotel_id) AS nhotels
FROM (`towns` t)
   JOIN `hotels` h
      ON `h`.`town_id` = `t`.`town_id`
   JOIN `regions` r
      ON `t`.`town_region_id` = `r`.`region_id`
   JOIN `countries` c
      ON `r`.`region_country_id` = `c`.`country_id`
GROUP BY `t`.`town_id`
ORDER BY `nhotels` DESC

#2

SELECT 
   hotels.town_id, 
   count(hotels.hotel_id) from hotels AS hotels_count,
   towns.town_name
FROM
    hotels,
LEFT JOIN
    towns ON hotels.town_id = towns.town_id
GROUP BY hotels.town_id
ORDER BY hotels_count DESC;