SubSelect

SubSelect and Dynamic Calculations in ArtiGrid

This example shows how to use subselect in ArtiGrid to retrieve dynamic values from other tables and use them within the grid, including derived calculations.

1. Creating a SubSelect

$grid->subselect('Paid',
    'SELECT SUM(amount) FROM payments WHERE customerNumber = {customerNumber}'
);

This subselect calculates the total sum of payments made by each customer. The placeholder {customerNumber} is dynamically replaced with the value from each row.

2. Creating a Calculated Field

$grid->calculate('Profit', '{Paid} - {creditLimit}');

This field calculates the difference between the total paid (Paid) and the customer’s credit limit (creditLimit), generating a dynamic value called Profit.

3. Rendering the Table

<?php
    $grid = new ArtiGrid();
    $grid->table('customers');
    $grid->crudCol(['customerNumber', 'customerName', 'city', 'creditLimit', 'Paid', 'Profit']);
    $grid->subselect('Paid',
        'SELECT SUM(amount) FROM payments WHERE customerNumber = {customerNumber}'
    );
    $grid->calculate('Profit', '{creditLimit} - {Paid} + 1');
    echo $grid->render();
?>