KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

How to add if in select query #2874

Open Mohammad Absi opened this topic on on Nov 15 - 8 comments

Mohammad Absi commented on Nov 15

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

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

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

Sebastian Morales commented on Nov 17

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

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

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

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

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 7 days ago

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

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

None