Answers

• rf1234 Posts: 3,176Questions: 92Answers: 438

  September 9

  You could use an Mjoin for example and count the values client side. Or you embed SQL ( COUNT(*) ) and return the result from the server.

  Here is Mjoin solution.

  Editor::inst($db, 'address', 'addressnumber')
      ->field(
          Field::inst('address.street'),
          Field::inst('address.zipcode'),
          Field::inst('address.city')
      )
      ->join(
      Mjoin::inst( 'client' )
          ->link( 'address.addressnumber', 'client.addressnumber' )
          ->fields(
              Field::inst( 'client.name' )->set( false )               
          )
      )
      ->process($_POST)
      ->json();
  

  $('#table').DataTable({
    ajax: {
      url: '/crm/ajax/address-data.php',
      type: 'POST'
    },    
    columns: [
      { data: 'address.street' },
      { data: 'address.zipcode' },
      { data: 'address.city' },
      {
          data: 'client',
          render: function (data) {
              return data.length;
          }
      }
    ]
  });
  
• rf1234 Posts: 3,176Questions: 92Answers: 438

  September 9

  This is the same using Editor's raw() method and SQL:

  Editor::inst($db, 'address', 'addressnumber')
      ->field(
          Field::inst('address.street'),
          Field::inst('address.zipcode'),
          Field::inst('address.city'),
          Field::inst('address.addressnumber AS address.residentCount') ->set( false )
               ->getFormatter( function($val, $data, $opts) use ($db) {
                    $result = $db->raw()
                       ->bind( ':addressnumber', $val  )
                       ->exec( 'SELECT COUNT(*) AS residentCount
                                  FROM `client`
                                 WHERE addressnumber= :addressnumber' );
                     $row = $result->fetch(PDO::FETCH_ASSOC);
                     return $row["residentCount"];
                  })   
      )
      ->process($_POST)
      ->json();
  

  $('#table').DataTable({
    ajax: {
      url: '/crm/ajax/address-data.php',
      type: 'POST'
    },   
    columns: [
      { data: 'address.street' },
      { data: 'address.zipcode' },
      { data: 'address.city' },
      { data: 'address.residentCount' }
    ]
  });
  
• arie0512 Posts: 7Questions: 2Answers: 0

  12:24PM

  Hi rf1234,

  Tnx for getting back to me with a extended answer.

  I have try to get the Mjoin solution to work but I can't figure out what this exactly mean: Field::inst( 'client.name' )->set( false ) because I have no column named 'name' in the table. I have try to use another existing field from client like lastname but that didn't work also.

  Than I try the SQL solution and I get this workng only the load time is now very long ..... avout 2,5 minute for some 20.000 records.

  Any idea why the table loading is so long?
• allan Posts: 65,042Questions: 1Answers: 10,772 Site admin

  12:45PM

  client.name would be replaced with a field name in your m-joined table. The documentation here might help to explain things a bit.

  If you have 20k records, then yes, a separate query to get the count is going to take a long time! It would execute 20'001 queries to load the page! I'd suggest against that approach in this case. You could use server-side processing to help reduce the time, but Mjoin might be a better option for you in this case.

  There is an example of what you are trying to do here. Bit awkward that my demo data has 6 for all locations, but if you edit a person's site, you'll see the counts change.

  Allan