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

I have 4 tables that link together...

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:

$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.

$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

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;