how to add if in select query

Mohammad Absi commented on Nov 15, 2022

i have column smoking the value of it in database is 0,1 how to display Yes, No on the view when execute the query on database its work fine

SELECT IF('somke' == 0, 'No', 'Yes') as isSmoking FROM patients

this code not work for me any help should be apricated

protected function setup()
{
    $this->src('jbcpDB')
    ->query(
        DB::table("patients")            
        ->select( 
        'patients.patientsID',
        'patients.patientsName',
        IF('patients.somke' == 0, 'No', 'Yes') as isSmoking
        )
        ->pipe($this->dataStore('patientsList'));
}

Sebastian Morales commented on Nov 16, 2022

For complex expressions I think you'd better using selectRaw instead of select. In your case pls try this:

    $this->src('jbcpDB')
    ->query(
        DB::table("patients")            
        ->selectRaw( 
        "
            patients.patientsID,
            patients.patientsName,
            IF('patients.somke' == 0, 'No', 'Yes') as isSmoking
        "
        )->toMySQL() //you need to return the querybuilder as a sql query of your db type
   ->pipe($this->dataStore('patientsList'));

https://www.koolreport.com/docs/querybuilder/overview/#supported-database-systems

Mohammad Absi commented on Nov 16, 2022

Thank you very much its work but why it return only 15K raw from 37k

Sebastian Morales commented on Nov 17, 2022

Pls try to output the query and copy it to your DB admin interface to see how many results it returns:

    $sqlQuery = DB::table("patients")            
        ->selectRaw( 
        "
            patients.patientsID,
            patients.patientsName,
            IF('patients.somke' == 0, 'No', 'Yes') as isSmoking
        "
        )->toMySQL();
    echo "sqlQuery = $sqlQuery<br>";
    $this->src('jbcpDB')
    ->query($sqlQuery)
    ->pipe($this->dataStore('patientsList'));

Mohammad Absi commented on Nov 17, 2022

yes found it, thank you very much

The reason is the query didn't return raws if the foreign key is null when using join

Mohammad Absi commented on Nov 17, 2022

Now when export to excel 35k record i get below error also its take too long time to display the table though i used paging

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 20971520 bytes) in D:\xampp\htdocs\koolreport\koolreport\excel\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Collection\Cells.php on line 157

Sebastian Morales commented on Nov 21, 2022

It's an out of memory error. When you export large data to excel, it's better to use Big Spreadsheet export to reduce the amount of memory used:

https://www.koolreport.com/docs/excel/export_to_big_spreadsheet/

There's also risk of time out. If it happens pls consider increase your php's max_execution_time as well.

Mohammad Absi commented on Nov 27, 2022

get another error

Fatal error: Maximum execution time of 120 seconds exceeded in D:\xampp\htdocs\koolreport\koolreport\excel\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Style\Style.php on line 698

Sebastian Morales commented on Dec 1, 2022

Pls increase max_execution_time value (unit: seconds) in your php.ini file. Then restart your http server to see if it works.

KoolReport's Forum

How to add if in select query #2874

Build Your Excellent Data Report