title: CodeIgniter, Order by number of rows in a separate joined table?
body: <p>I have 4 tables that link together...</p>
  
  <p>Firstly the <strong>hotels</strong> table</p>
  
  <ul>
  <li>hotel_id</li>
  <li>town_id</li>
  <li>hotel_name</li>
  </ul>
  
  <p>Then the <strong>towns</strong> table:</p>
  
  <ul>
  <li>town_id</li>
  <li>region_id</li>
  <li>town_name</li>
  </ul>
  
  <p>Then the <strong>regions</strong> table:</p>
  
  <ul>
  <li>region_id</li>
  <li>country_id</li>
  <li>region_name</li>
  </ul>
  
  <p>Finally the <strong>countries</strong> table</p>
  
  <ul>
  <li>country_id</li>
  <li>country_name</li>
  </ul>
  
  <p>What I need to do is list the towns in order of how many hotels there are within that town.</p>
  
  <p>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..</p>
  
  <p>Therefore using the active records in CodeIgniter I have done this so far:</p>
  
  <pre><code>$this-&gt;db-&gt;join('regions','towns.town_region_id = regions.region_id');
  $this-&gt;db-&gt;join('countries','regions.region_country_id = countries.country_id');
  $query = $this-&gt;db-&gt;get('towns');
  
  foreach ($query-&gt;result() as $row) {
       echo "&lt;li&gt;";
       echo "$row-&gt;town_name, $row-&gt;country_name";
       echo "&lt;/li&gt;";
  }
  </code></pre>
  
  <p>This outputs:</p>
  
  <ul>
  <li>London, United Kingdom</li>
  <li>Washington, United States</li>
  <li>New York, United States</li>
  <li>Moscow, Russia</li>
  <li>etc, etc</li>
  </ul>
  
  <p>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..</p>
  
  <p>Any help would be much appreciated! Thanks.</p>