Editor multiple ->where clause with OR instead of AND

fabioberetta

Dear all,

I am using the ->where clause in the PHP library and works perfectly.

Now if you use multiple where it assumes and AND between them.

I need to use an OR. I do not find any examples or reference for that.

Is it possible to have an OR? How?

ty
f

allan

Hi,

Yes, this can be done - have a look at the conditions documentation for the PHP libraries which have examples of how an OR condition can be applied.

Regards,
Allan

fabioberetta

Thanks allan,

it worked! Great product and even better support.

I would need now an advice from you. I have this relatively complex mysql query.

select c.id, c.account_id, expected_dt_in, expected_dt_out, note, li.device_date_time as arrived_at, lo.device_date_time as departed_at
    from child c
        
        # Finds out if the child is forecasted to arrive
        left join (select   expected_dt_in, 
                            expected_dt_out, 
                            child_id, 
                            note 
                    from schedule_forecast sf1
                    where sf1.expected_dt_in between date_format(now(),'%Y-%m-%d 00:00:00') 
                                             and date_format(now(),'%Y-%m-%d 23:59:59')) sf
            on sf.child_id = c.id

        # Finds out if the child is arrived
        left join (select type_id, child_id, device_date_time
                        from log
                        where log.device_date_time  between date_format(now(),'%Y-%m-%d 00:00:00') 
                                                    and date_format(now(),'%Y-%m-%d 23:59:59')
                            and log.type_id = 'CHKIN') li
        on li.child_id = c.id

        # Finds out if the child is departed
        left join (select type_id, child_id, device_date_time
                        from log
                        where log.device_date_time  between date_format(now(),'%Y-%m-%d 00:00:00') 
                                                    and date_format(now(),'%Y-%m-%d 23:59:59')
                            and log.type_id = 'CHKOUT') lo
        on lo.child_id = c.id
    where c.account_id = 1

Shall I try to pass it to the server using your editor methods or do you have any passthrough function?

I am a bit reluctant due to the complexity of the query.

ty
f

fabioberetta

I forgot to mention that I wont need to use the editor. Just properly show the data into a datatable.

ty
f

fabioberetta

I found out in your manual a reference to

$db->sql()

that is supposed to allow execution of own query but I do not really understand how to use it. Do you have an example?

ty
f

allan

that is supposed to allow execution of own query

Yes - you can simply do:

$myResults = $db->sql('SELECT ...')->fetchAll();

to get the results from your own query.

The query above is a little bit beyond what the Editor libraries can do at the moment I'm afraid, but the sql() method will accept any SQL.

Allan

fabioberetta

Thanks Allan,

Since I just needed to have a joson to be diplayed in a datatable I ended up with doing the following:

• Created stored procedures doing the heavy lift job
• Call them using your ->sql() method.

I put an example below if someone needs to do the same.

<?php

/*
 * Collects KPI for Dashboard page
 */

// DataTables PHP library
include( "../vendor/Editor-1.5.1/php/DataTables.php" );

// start session to get session user data
session_start();

$_selected_date = new DateTime('now');
$_span_days = 14;

$_from_hour = 6;
$_to_hour = 20;

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

    // Collects data for the 15 days workload
    $_data = $db->sql( "CALL get_period_workload('" . $_SESSION['account_id'] . "', '" . $_selected_date->format('Y-m-d') . "', " . (string)$_span_days .");" )->fetchall();
    $_obj['data']['daily_workload'] = $_data;
    
    // Collects data for the 15 hours workload
    $_data = $db->sql( "CALL get_hourly_workload('" . $_SESSION['account_id'] . "', '" . $_selected_date->format('Y-m-d') . "', " . (string)$_from_hour .", " . (string)$_to_hour . ");" )->fetchall();
    $_obj['data']['hourly_workload'] = $_data;
    
    
    echo json_encode($_obj);

Thanks,
f

allan

Sounds good - thanks for posting your solution.

Allan