title: How to use database query with JOIN in server-side processing mode
body: <p>I am using jQuery DataTables for my view list. I used server-side processing mode which works perfectly especially for huge datasets. But my problem is that I am only able to do this using single database table. </p>
  
  <p>What about using custom query using multiple tables with <code>JOIN</code> without changing too much is my code?</p>
  
  <p>So I have this:</p>
  
  <p><strong>HTML</strong></p>
  
  <pre><code>&lt;table id="CustomerList" class="table table-striped table-bordered" cellspacing="0" width="100%"&gt;
      &lt;thead&gt;
          &lt;tr&gt;
              &lt;th colspan="7"&gt; &lt;center&gt;Customer Information&lt;center&gt;&lt;/th&gt;
              &lt;th colspan="1"&gt; &lt;center&gt;Actions&lt;center&gt;&lt;/th&gt;
          &lt;/tr&gt;
          &lt;tr&gt;
              &lt;th&gt;ID&lt;/th&gt;
              &lt;th&gt;First Name&lt;/th&gt;
              &lt;th&gt;Last Name&lt;/th&gt;
              &lt;th&gt;Gender&lt;/th&gt;
              &lt;th&gt;Phone Number&lt;/th&gt;
              &lt;th&gt;Country&lt;/th&gt;
              &lt;th&gt;Postcode&lt;/th&gt;
              &lt;th&gt;Edit&lt;/th&gt;
          &lt;!--     &lt;th&gt;Edit&lt;/th&gt;
              &lt;th&gt;Delete&lt;/th&gt; --&gt;
          &lt;/tr&gt;
      &lt;/thead&gt;
      &lt;tbody&gt;
      &lt;/tbody&gt;
  &lt;/table&gt;
  </code></pre>
  
  <p>Ajax</p>
  
  <pre><code>&lt;script type="text/javascript"&gt;
  $(document).ready(function() {
      $.fn.dataTable.ext.legacy.ajax = true;
      var table = $('#CustomerList').DataTable( {
         "processing": true,
         "serverSide": true,
         "ajax": "api/customer/all",
         "columnDefs": [
              { 
                  "targets": 7,
                  "render": function(data, type, row, meta){
                     // return '&lt;a href="/qms/public/customer/' + row[0] + '/edit"&gt;Edit&lt;/a&gt;';  
                     return "&lt;a class='btn btn-small btn-info' href='&lt;?php echo URL::to('customer').'/';?&gt;"+row[0]+"/edit'&gt;&lt;span class='glyphicon glyphicon glyphicon-edit' aria-hidden='true'&gt;&lt;/span&gt;&lt;/a&gt;";  
                  }
              }            
          ]        
      });
      var tt = new $.fn.dataTable.TableTools( $('#CustomerList').DataTable() );
      $( tt.fnContainer() ).insertBefore('div.dataTables_wrapper');
  });
  </code></pre>
  
  <p></p>
  
  <p><strong>Controller</strong></p>
  
  <pre><code>public function apiGetCustomers()
  {
      /*=================================================================*/
      /*
       * Script:    DataTables server-side script for PHP and PostgreSQL
       * Copyright: 2010 - Allan Jardine
       * License:   GPL v2 or BSD (3-point)
       */
  
      /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
       * Easy set variables
       */
  
      /* Array of database columns which should be read and sent back to DataTables. Use a space where
       * you want to insert a non-database field (for example a counter or static image)
       */
      $aColumns = array('id', 'firstname', 'lastname', 'gender', 'phone_num', 'country', 'postcode' );
  
      /* Indexed column (used for fast and accurate table cardinality) */
      $sIndexColumn = "phone_num";
  
      /* DB table to use */
      $sTable = "customers";
  
      /* Database connection information */
      $gaSql['user']       = "postgres";
      $gaSql['password']   = "postgres";
      $gaSql['db']         = "qms";
      $gaSql['server']     = "localhost";
  
  
  
      /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
       * If you just want to use the basic configuration for DataTables with PHP server-side, there is
       * no need to edit below this line
       */
  
      /*
       * DB connection
       */
      $gaSql['link'] = pg_connect(
          " host=".$gaSql['server'].
          " dbname=".$gaSql['db'].
          " user=".$gaSql['user'].
          " password=".$gaSql['password']
      ) or die('Could not connect: ' . pg_last_error());
  
  
      /*
       * Paging
       */
      $sLimit = "";
      if ( isset( $_GET['iDisplayStart'] ) &amp;&amp; $_GET['iDisplayLength'] != '-1' )
      {
          $sLimit = "LIMIT ".intval( $_GET['iDisplayLength'] )." OFFSET ".
              intval( $_GET['iDisplayStart'] );
      }
  
  
      /*
       * Ordering
       */
      if ( isset( $_GET['iSortCol_0'] ) )
      {
          $sOrder = "ORDER BY  ";
          for ( $i=0 ; $i&lt;intval( $_GET['iSortingCols'] ) ; $i++ )
          {
              if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
              {
                  $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                      ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc').", ";
              }
          }
  
          $sOrder = substr_replace( $sOrder, "", -2 );
          if ( $sOrder == "ORDER BY" )
          {
              $sOrder = "";
          }
      }
  
      /*
       * Filtering
       * NOTE This assumes that the field that is being searched on is a string typed field (ie. one
       * on which ILIKE can be used). Boolean fields etc will need a modification here.
       */
      $sWhere = "";
      if ( $_GET['sSearch'] != "" )
      {
          $sWhere = "WHERE (";
          for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
          {
              if ( $_GET['bSearchable_'.$i] == "true" )
              {
                  if($aColumns[$i] != 'id') // Exclude ID for filtering
                  {
                      $sWhere .= $aColumns[$i]." ILIKE '%".pg_escape_string( $_GET['sSearch'] )."%' OR ";
                  }
              }
          }
          $sWhere = substr_replace( $sWhere, "", -3 );
          $sWhere .= ")";
      }
  
      /* Individual column filtering */
      for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
      {
          if ( $_GET['bSearchable_'.$i] == "true" &amp;&amp; $_GET['sSearch_'.$i] != '' )
          {
              if ( $sWhere == "" )
              {
                  $sWhere = "WHERE ";
              }
              else
              {
                  $sWhere .= " AND ";
              }
              $sWhere .= $aColumns[$i]." ILIKE '%".pg_escape_string($_GET['sSearch_'.$i])."%' ";
          }
      }
  
  
      $sQuery = "
          SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
          FROM   $sTable
          $sWhere
          $sOrder
          $sLimit
      ";
  
      $rResult = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
  
      $sQuery = "
          SELECT $sIndexColumn
          FROM   $sTable
      ";
      $rResultTotal = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
      $iTotal = pg_num_rows($rResultTotal);
      pg_free_result( $rResultTotal );
  
      if ( $sWhere != "" )
      {
          $sQuery = "
              SELECT $sIndexColumn
              FROM   $sTable
              $sWhere
          ";
          $rResultFilterTotal = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
          $iFilteredTotal = pg_num_rows($rResultFilterTotal);
          pg_free_result( $rResultFilterTotal );
      }
      else
      {
          $iFilteredTotal = $iTotal;
      }
  
      /*
       * Output
       */
      $output = array(
          "sEcho" =&gt; intval($_GET['sEcho']),
          "iTotalRecords" =&gt; $iTotal,
          "iTotalDisplayRecords" =&gt; $iFilteredTotal,
          "aaData" =&gt; array()
      );
  
      while ( $aRow = pg_fetch_array($rResult, null, PGSQL_ASSOC) )
      {
          $row = array();
          for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
          {
              if ( $aColumns[$i] == "version" )
              {
                  /* Special output formatting for 'version' column */
                  $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
              }
              else if ( $aColumns[$i] != ' ' )
              {
                  /* General output */
                  $row[] = $aRow[ $aColumns[$i] ];
              }
          }
          $output['aaData'][] = $row;
      }
  
      echo json_encode( $output );
  
      // Free resultset
      pg_free_result( $rResult );
  
      // Closing connection
      pg_close( $gaSql['link'] );
  
  
  }
  </code></pre>
  
  <p>In my controller you can see the <code>$aColumns</code> which contains the table column that I want to get in the table <code>customers</code></p>
  
  <p>What if I want a custom query to fetch data like:</p>
  
  <pre><code>$query = "SELECT a.id as crmid, b.name, a.title, a.firstname, a.surname, a.disposition, a.gross, a.created_at, a.phone_num FROM forms a INNER JOIN users b ON a.agent_id = b.id;";
  </code></pre>
  
  <p>So I have inner joins instead of one table only.</p>