[FIXED] Cakephp how I will write coalesce in query builder on sum?

Issue

I have postgres query like below

coalesce(sum(SUMMARY_COINS.get_count),0) as total 

I am trying to convert in cakephp query builder

$query = $query->select([
    'total' => $query->func()->coalesce($query->func()->sum('c.get_count'),0)
]

Getting error , How I will apply coalesce in query builder for make isnull zero in sum?

Solution

Check the API documentation, the coalesce() function expects an array that holds the function arguments!

Also note that you need to pass corresponding type information unless you want your scalar values to be bound as strings:

$query->func()->coalesce(
    [$query->func()->sum('c.get_count'), 0],
    // the first type is `null` because expressions (`sum()`) are not being bound
    [null, 'integer']
)

See also

• API > \Cake\Database\FunctionsBuilder::coalesce()
• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions
• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions > Custom Functions

Answered By - ndm