Answers

• rf1234 Posts: 3,177Questions: 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,177Questions: 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,045Questions: 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
• rf1234 Posts: 3,177Questions: 92Answers: 438

  2:53PM

  because I have no column named 'name' in the table.

  Client
  Clientnumber | Name | Gender | addressnumber
  1 | Joe | M | 1

  Well, you say that you do have a column called "name" in your table but anyway ... You can take any column from your "client" table and return it from the server so that it can be counted on the client side.