wpdb COALESCE won't work

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);

Contents hide

Result

Answers:

Method 1

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
        )

)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating