GROUP BY clause and Aggregate Functions in Editor Instance Constructor

DT Proger

Given that the following Editor constructor:

Editor::inst( $db, 'products' )
->field(
Field::inst( 'products.item_code' ),
Field::inst( 'products.product_name' ),
Field::inst( 'products.price' ),
Field::inst( 'products.product_type' ),
Field::inst( 'product_details.size' ),
Field::inst( 'product_details.colour' ),
Field::inst( 'product_details.remarks' )
)
->leftJoin( 'product_details', 'product_details.item_code', '=', 'products.item_code' )
->process($_POST)
->json();

should result in the following SQL statement:

SELECT
products.item_code,
products.product_name,
products.price,
products.product_type,
product_details.size,
product_details.colour,
product_details.remarks
FROM products
LEFT JOIN product_details ON product_details.item_code = products.item_code ;

Then, how should I code the Editor constructor in order to lead to the following SQL query?

SELECT products.product_type, SUM(products.price) AS sum_price, COUNT(*) AS count_rows
FROM products
LEFT JOIN product_details ON product_details.item_code = products.item_code
GROUP BY products.product_type

Thanks.

DT Proger

Any help would be greatly appreciated.

allan

Hi,

I'm sorry to say that there is no GROUP BY option in the Editor PHP libraries at this time. The only way to achieve the above is to use a custom SQL query to get the data and then use the Editor libraries to perform the updates on a simple static database table.

Regards,
Allan

DT Proger

Thank you Allan for your prompt response.

Ok let me try your suggestion first. Will ask you again if I encounter further difficulties.

Thanks.