I am struggling to get the result using COALESCE from custom table. I need to run a second query if the first results null but wpdb resulting query string itself rather than the result.

$query = "SELECT coalesce(";
$query .= "(SELECT order_number FROM wp_gs_p_cart ";
$query .= "WHERE group_user = %d AND group_id = %d AND identifier = %s ORDER BY id DESC LIMIT 1),";
$query .= "(SELECT order_number FROM wp_gs_p_cart ORDER BY id DESC LIMIT 1)";
$query .= ");";

$prepare = $wpdb->prepare($query, $user_id, $group_id, $identifier);

return $wpdb->get_results($prepare);

// or 

return $wpdb->get_row($prepare);

Result

Array
(
    [0] => stdClass Object
        (
            [coalesce((SELECT order_number FROM wp_gs_p_cart WHERE group_user = 73 AND group_id = 298 AND identifier = 'AtR4deAVgU4Ensi1_1ac2b33a4df7c5f0cf148d6232074352' ORDER BY id DESC LIMIT 1),(SELECT order_number FROM wp_gs_p_cart ORDER BY id DESC LIMIT 1))] => 3333
        )

)

I am struggling to get the result using COALESCE from custom table. I need to run a second query if the first results null but wpdb resulting query string itself rather than the result.

$query = "SELECT coalesce(";
$query .= "(SELECT order_number FROM wp_gs_p_cart ";
$query .= "WHERE group_user = %d AND group_id = %d AND identifier = %s ORDER BY id DESC LIMIT 1),";
$query .= "(SELECT order_number FROM wp_gs_p_cart ORDER BY id DESC LIMIT 1)";
$query .= ");";

$prepare = $wpdb->prepare($query, $user_id, $group_id, $identifier);

return $wpdb->get_results($prepare);

// or 

return $wpdb->get_row($prepare);

Result

Array
(
    [0] => stdClass Object
        (
            [coalesce((SELECT order_number FROM wp_gs_p_cart WHERE group_user = 73 AND group_id = 298 AND identifier = 'AtR4deAVgU4Ensi1_1ac2b33a4df7c5f0cf148d6232074352' ORDER BY id DESC LIMIT 1),(SELECT order_number FROM wp_gs_p_cart ORDER BY id DESC LIMIT 1))] => 3333
        )

)

• query
• mysql
• wpdb

1 Answer 1

From what I could see, the query did return a result, but the COALESCE() result was not named, hence MySQL and WordPress use the entire COALESCE() query as the field name.

To avoid that, you would use an alias for the COALESCE(), e.g.

//$query = "SELECT COALESCE( <your queries> ) AS <alias>";
$query = "SELECT COALESCE( NULL, 5, 2, 3 ) AS order_number";

$results = $wpdb->get_results( $query );
// Assuming $results[0] exists:
echo $results[0]->order_number;

$result = $wpdb->get_row( $query );
// Assuming the query returned a result:
echo $result->order_number;

But since COALESCE() returns just one result, you'd want to use get_var():

//$query = "SELECT COALESCE( NULL, 5, 2, 3 )"; // this works
// But maybe, better with an alias.
$query = "SELECT COALESCE( NULL, 5, 2, 3 ) AS order_number";

$result = $wpdb->get_var( $query );
echo $result;