Sugar Query - Ifnull

Hi everyone.

I am attempting to convert all of my Raw SQL into Sugar Query, having mostly success following the guide @ https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_9.2/Data_Framework/Database/SugarQuery/

However something I'm not able to replace yet is the ifnull(field,'') that I use. So for instance I use:

and ifnull(pp_id_c,'') = ''

For my raw SQL, I can see I can use:

$SugarQuery->where()->isNull('industry');
or
$SugarQuery->where()->contains('name','Test');

But neither do the ifnull function as far as I can tell. Basically I have a checkbox, and with sugar if they save but do not touch the field it returns "Null" but if it's null. And the above is if the field is null make it blank.

Can anyone help?

Thank you!

Daniel

  • Hi Daniel,

    You can use  isEmpty() | isNotEmpty()

    Used to check if a field is or isn't empty.

    //add an isEmpty statement 
    $SugarQuery->where()->isEmpty('industry'); 
    
    //add an isNotEmpty statement 
    $SugarQuery->where()->isNotEmpty('name');

    Which is basically:

    $where->queryOr()
    ->equals($field, '', $bean)
    ->isNull($field, $bean);
  • Hi Neeraja.

    Thanks! That makes sense, if I wanted to check if it was "1" not blank how does that work? The example given works but only generates:

    ((meetings_cstm.send_to_pp_c = ?) OR (meetings_cstm.send_to_pp_c IS NULL)) and ? is a blank ("") but I need the parameter to be 1.


    Thanks!

  • Ah, I see, you can just do the queryOr scenario here, something like:

    $sugarQuery->where()->queryOr()->isNull('send_to_pp_c')->equals('send_to_pp_c','1');

    This will translate into

    ((meetings_cstm.send_to_pp_c is NULL) OR (meetings_cstm.send_to_pp_c = '1'))

    If you think I misunderstood, please provide an example of your Raw SQL to understand it better.