Answers

• ztevie Posts: 101Questions: 23Answers: 5

  December 2016

  Found this: https://datatables.net/forums/discussion/36355

  Seems it's not possible to do the count on the server side, but instead I have to send the full arrays to the client and do length function there? That's a lot of unnecessary data being sent, unless you've implemented some functionality since that thread?
• allan Posts: 63,089Questions: 1Answers: 10,387 Site admin

  December 2016 Answer ✓

  You are correct that the Editor PHP libraries do not provide an option to execute SQL functions (such as COUNT()). There are basically two options:

  1. Create a custom query that will get the data you require using plain SQL and direct DataTables to load from there rather than using the Editor PHP class.
  2. As you say, use the Mjoin class and the resulting array's length property.

  Regards,
  Allan
• ztevie Posts: 101Questions: 23Answers: 5

  December 2016 edited December 2016

  OK, I see... And there's no way to cheat Editor?
  Like doing Field::inst on an existing field in the DB I won't use, then before the Editor section getting the Count from a outside function and assign that to Field::inst with getValue?
  I assume it's not or you would have thought about it, but one can always ask...
• ztevie Posts: 101Questions: 23Answers: 5

  December 2016

  Well, I tried a simple function and it seems to work. I'll try to make a sql query in that function anad see where I end up...
  It loooks like this:

  //function outside of Editor section:
  function ret($x, $y){
      return $x+$y;
  }
  
  Field::inst( 'blasts.blast_finished_date' )
              ->getValue (ret(563, 562)),
  
• allan Posts: 63,089Questions: 1Answers: 10,387 Site admin

  December 2016

  And there's no way to cheat Editor?

  Sorry - no. The fact that it can't run functions is fairly base to its database abstraction. It is something I plan to address in future.

  The best I can really think of is that you use a VIEW which will perform the more complex query and the Editor libraries can just SELECT against that.

  Allan
• ztevie Posts: 101Questions: 23Answers: 5

  December 2016

  Ah, great idea! I'll look into that...
• emcxh35 Posts: 1Questions: 0Answers: 0

  February 2017

  Hi,

  Can I vote for this functionality as well?

  I want to have the table display two fields that are essentially counts of linked tables within the database.

  I would like Editor to simply ignore these fields and return the same data for them as was POST'd to it.
• allan Posts: 63,089Questions: 1Answers: 10,387 Site admin

  February 2017

  Thanks for the vote . Support for SQL functions is something I'm planning to introduce in a future version (I don't know when it will come yet), but at the moment a VIEW is probably the best workaround.

  Allan
• CraigJ Posts: 30Questions: 12Answers: 2

  January 2020 edited January 2020

  "Create a custom query that will get the data you require using plain SQL and direct DataTables to load from there rather than using the Editor PHP class."

  How?

  I see this as the answer to a number of forum questions, but no explanation of how to actually do that, and all my google searches lead back to these questions...
• allan Posts: 63,089Questions: 1Answers: 10,387 Site admin

  January 2020

  Assuming you are using the Editor PHP libraries you could just use it's select method:

  $result = $db->select( 'table', ['field1', 'field2', ...] );
  
  echo [
    "data" => $result->fetchAll()
  ];
  

  Or if you prefer, you can just query the database using whatever database interface you normally use (e.g. PDO).

  Allan