Sum not working in select

[AnumMuzahir]

Describe the bug Medoo broke while performing a sum function in select query

Information

• Version of Medoo: 1.6.1
• Type of Database (MySQL, MSSQL, SQLite...):MySQL
• System (Liunx\Windows\Mac):Windows

Detail Code My desired SQL Query : SELECT c.name, o.shipping_phone AS mobile, SUM(o.total_cost) AS total_cost, COUNT(o.total_cost) AS total_orders FROM order o LEFT JOIN customer c ON (o.shipping_phone=c.mobile) WHERE o.stats ='Delivered' GROUP BY o.shipping_phone ORDER BY SUM(o.total_cost) DESC LIMIT 5";

My Medoo Implementation: $database->select($table, ["[>]customer(c)" => ["o.shipping_phone" => "mobile"]], ["c.name","o.shipping_phone(mobile)", Medoo::raw("SUM(o.total_cost)")], ["stats" => $stats, "GROUP" => "o.shipping_phone", "ORDER" => ["o.total_cost" => "DESC"], "LIMIT" => 5]);

Expected output Customer's details along with the total amount of aggregated orders along with the order count

Actual Output Warning: preg_match() expects parameter 2 to be string, object given in H:\xampp\htdocs\material\include\medoo.php on line 1103

Warning: Illegal offset type in H:\xampp\htdocs\material\include\medoo.php on line 1115

[catfan]

The correct syntax for raw as column name is:

$database->select($table, [
    "[>]customer(c)" => ["o.shipping_phone" => "mobile"]
], [
    "c.name",
    "o.shipping_phone(mobile)",
    "total" => Medoo::raw("SUM(o.total_cost)")
], [
    "stats" => $stats,
    "GROUP" => "o.shipping_phone",
    "ORDER" => ["o.total_cost" => "DESC"],
    "LIMIT" => 5
]);

https://medoo.in/api/raw

[AnumMuzahir]

This part is missing "SUM(o.total_cost) DESC".

[catfan]

@AnumMuzahir What part?

[AnumMuzahir]

"SELECT c.name, o.shipping_phone AS mobile, SUM(o.total_cost) AS total_cost, COUNT(o.total_cost) AS total_orders FROM order o LEFT JOIN customer c ON (o.shipping_phone=c.mobile) WHERE o.stats ='Delivered' GROUP BY o.shipping_phone ORDER BY SUM(o.total_cost) DESC LIMIT 5" I want to order data by SUM(o.total_cost) in MEDOO

[catfan]

Because of "total" => Medoo::raw("SUM(o.total_cost)") will set SUM() result alias as total, so you can use total to "ORDER" => ["total" => "DESC"].

[AnumMuzahir]

Thank you :)