Table Joins: Does Editor have support for "Or" statements and "Sum Of"?

pansengtat

I am trying to extract data from a datatable with a SQL query like this:

select *
from myrequests
where (status = 1 OR assigned_group = 4)

So far in Editor, I have been able to only write this in the backend:

$data = Editor::inst($db, 'myrequests', 'request_id')
            ->where('myrequests.status', 1)
                        ->where('myrequests.assigned_group', 4)
            ->field(
                Field::inst('myrequests.remarks')->validator('Validate::notEmpty')
            )
            ->leftJoin('myrequestdata', 'myrequests.request_id', '=', 'myrequestdata.request_id')
            ->process($_POST)
            ->data();

However, the above code is reading the "where" clause as "and".

Another question: "Sum of" statements

Is there a way to edit the core Editor/Datatables files, eg) Join.php to include "sum of" SQL functions?

allan

Hi,

In Editor 1.4 you can use an anonymous function for the where() method to get access to the underlying query and its more complete where operations. For example:

->where( function ( $q ) {
  $q->where( 'myrequests.status', 1 );
  $q->or_where( myrequests.assigned_group', 4 );
} )

Regarding sums - currently it is not possible to use SQL functions in Editor fields. You would need to calculate the sum externally to the libraries I'm afraid.

Allan

pansengtat

I attempted to use the query method in your reply, but I myself get an invalid JSON error. Would appreciate if this can be fixed:

$data = Editor::inst($db, 'myrequests', 'ID')
        ->where(
            function ( $q ) {
                $q->where( 'myrequests.status', 1 );
                $q->or_where( 'myrequests.assigned_group', 4 );
            })
        ->field(
            Field::inst('myrequests.QueueNum')->set(false),
            Field::inst('myrequests.assigned_group'),
                Field::inst('mygroups.groupname')->set(false),
            Field::inst('myrequests.status'),
                Field::inst('mystatus.statusstring')->set(false),
            Field::inst('myrequests.requeststring')
        )
        ->leftJoin('mygroups', 'myrequests.assigned_group', '=', 'mygroups.ID')
        ->leftJoin('mystatus', 'myrequests.status', '=', 'mystatus.ID')
        ->process($_POST)
        ->data();

allan

$q->or_where( myrequests.assigned_group', 4 );

Missing quote mark - sorry. Should be:

 $q->or_where( 'myrequests.assigned_group', 4 );

Did the error in the Ajax return give an error message that agrees with that?

Allan

pansengtat

Apparently, inside the function($q) could not recognize constants or variables which are declared in external files. But if a magic number is used, it just fits in well. Weird.

include 'constants.php';
include("lib/DataTables.php"); // DataTables PHP library

//--- Alias Editor classes, so that they are easy to use ---//
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

data = Editor::inst($db, 'myrequests', 'ID')
        ->where( function ( $q ) {
              $q->where( 'myrequests.status', $CONST_STATUS_START );
              $q->or_where( 'myrequests.assigned_group', $CONST_GROUP_EMPTY, "!=" );
        } )
//... add more stuff here

allan

could not recognize constants or variables which are declared in external files.

They can, but you need to use the use() statement:

->where( function ( $q ) use ( $CONST_STATUS_START, $CONST_GROUP_EMPTY ) {

This is all part of PHP's awful handling of globals and variable scope... I quite like PHP, but it does have issues such as this - particularly if you are used to Javascript.

Allan