Query Datagrid Issues

Grocery CRUD Enterprise
1

I am getting deeper into using GG and running into limitations. I am trying to generate a datagrid for this query:

select `c`.`ID_Contact` AS `ID_Contact`,
    if(
        (`c`.`ID_Contact` = ''),
        '',
        concat(`c`.`Name_First`, ' ', `c`.`Name_Last`)
    ) AS `full_name`,
(
        select `appt_Appts`.`Appt_Date`
        from `appt_Appts`
        where (`appt_Appts`.`fk_taskID` = `t`.`id_Task`)
        limit 1
    ) AS `appt_date`,
    `t`.`Item` AS `Item`,
    `t`.`Service Type Requested` AS `service_type_requested`,
    `t`.`lead_tech_dsp` AS `lead_tech_dsp`,
    `t`.`lead_labor_crew_dsp` AS `lead_labor_crew_dsp`,
    `c`.`Primary_Street1` AS `Primary_Street1`,
    `c`.`Primary_Postal_Code1` AS `Primary_Postal_Code1`,
    `t`.`Date_Created` AS `Date_Created`,
    `t`.`AccountName_Created` AS `AccountName_Created`,
    `t`.`id_Task` AS `id_Task`
from (
        `fsc_TSK_Tasks` `t`
        left join `fsc_CON_Contacts` `c` on((`t`.`id_contact` = `c`.`ID_Contact`))
    )
where (`t`.`Category` = 'Lead Sheet')
order by `t`.`id_Task`

GC seem to handle static TABLES just fine but SUB-SELECTS are problematic. The datagrid generates ERRORS or the options do not all work because GC does not see “appt_date” in the target table.

I tried creating a MySQL VIEW with this query and use that, but the load performance is horrible in GC. In my MySQL client (Sequel Pro - MAC). It is lightning fast. I am only viewing 50 rows. There are over 11K records in the view.

I need to know if there is a work around, otherwise I might not be able to continue with GC. This is one of many instances where this might come into play. I need make sure I am using the proper app to get the job done.

Thanks in advance for any insight here…

Don

2

Hello @dwdc ,

You are looking for a custom query which is available only through a custom model. For your example specifically I have created a custom model just for you to test it and see that this fits your needs. Please keep in mind that this custom model doesn’t have any filtering including or different order since everything that you are adding need to be translated to the custom model you use. For example if I search for full_name in reality I need to search to a logic that I combine both values, so the where statement will be like:

(`t`.`Category` = 'Lead Sheet') and concat(`c`.`Name_First`, ' ', `c`.`Name_Last`) like '%$search_value%'

which you will need to first validate the $search_value

In any case, please try with the custom model below and tell me at least if this is pointing you to the right direction:

<?php

// CustomModel.php
use GroceryCrud\Core\Model;

class customModel extends Model {

    public function getList()
    {
        $adapter = $this->adapter;

        $limit = $this->limit;
        $offset = ($this->limit * ($this->page - 1));

        // Execute the query
        $sql = <<<SQL
select `c`.`ID_Contact` AS `ID_Contact`,
    if(
        (`c`.`ID_Contact` = ''),
        '',
        concat(`c`.`Name_First`, ' ', `c`.`Name_Last`)
    ) AS `full_name`,
(
        select `appt_Appts`.`Appt_Date`
        from `appt_Appts`
        where (`appt_Appts`.`fk_taskID` = `t`.`id_Task`)
        limit 1
    ) AS `appt_date`,
    `t`.`Item` AS `Item`,
    `t`.`Service Type Requested` AS `service_type_requested`,
    `t`.`lead_tech_dsp` AS `lead_tech_dsp`,
    `t`.`lead_labor_crew_dsp` AS `lead_labor_crew_dsp`,
    `c`.`Primary_Street1` AS `Primary_Street1`,
    `c`.`Primary_Postal_Code1` AS `Primary_Postal_Code1`,
    `t`.`Date_Created` AS `Date_Created`,
    `t`.`AccountName_Created` AS `AccountName_Created`,
    `t`.`id_Task` AS `id_Task`
from (
        `fsc_TSK_Tasks` `t`
        left join `fsc_CON_Contacts` `c` on((`t`.`id_contact` = `c`.`ID_Contact`))
    )
where (`t`.`Category` = 'Lead Sheet')
order by `t`.`id_Task`
limit $limit offset $offset
SQL;

        $statement = $adapter->query($sql);
        $result = $statement->execute();

        $resultsArray = [];
        foreach ($result as $row) {
            $resultsArray[]  = $row;
        }

        return $resultsArray;
    }

    public function getTotalItems()
    {
        $adapter = $this->adapter;

        // Execute the query
        $sql = <<<SQL
select count(*) AS `num`
from (
        `fsc_TSK_Tasks` `t`
        left join `fsc_CON_Contacts` `c` on((`t`.`id_contact` = `c`.`ID_Contact`))
    )
where (`t`.`Category` = 'Lead Sheet')
SQL;

        $statement = $adapter->query($sql);
        $result = $statement->execute();

        foreach ($result as $row) {
            return (int)$row['num'];
        }

        // fallback
        return 0;
    }

}

Code from your Controller:

...
$model = new customModel($database);

$crud->setModel($model);

$crud->setTable('fsc_TSK_Tasks');
$crud->setSubject('Task', 'Tasks');

$crud->columns([
    'full_name', 'appt_date', 'Item', 'service_type_requested', 'lead_tech_dsp', 'lead_labor_crew_dsp',
    'Primary_Street1', 'Primary_Postal_Code1', 'Date_Created', 'AccountName_Created', 'id_Task'
]);

$output = $crud->render();
...

Regards
Johnny