Prestashop compare data from one table to another then print in tpl

Question

I'm trying to build a basic prestashop (1.7.8.6) module that collects 'id_customer' and 'firstname' from the ps_customer table then checks to see what date the last order was by that customer by checking the 'invoice_date' on the ps_orders table WHERE id_customer = 'id_customer'. If the order was over 732 days it then flags that account to be deleted

I can do this using something like the below:

    <?php
    $con=mysqli_connect("localhost","****","****","****");
    if (mysqli_connect_errno())
        {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }
        $sql="SELECT id_customer, date_add FROM ps_customer ORDER BY id_customer DESC";
        $result = mysqli_query($con,$sql);
        while ($row = mysqli_fetch_assoc($result))
            { 
            $id_customer = $row['id_customer'];
            $date_add =  $row['date_add'];
                
            $sqldelete="SELECT invoice_date FROM ps_orders WHERE (id_customer = '$id_customer') ORDER BY invoice_date ASC";

            $resultdelete = mysqli_query($con,$sqldelete);
                while ($rowdelete = mysqli_fetch_assoc($resultdelete))
                    { 
                    $invoice_delete = $rowdelete['invoice_date'];
                    if(strtotime($invoice_delete) < strtotime('-732 days')) {
                        $shouldbedeleted = "Y";
                        } else {
                        $shouldbedeleted = "N";
                        }
                
                    }

However, I'm trying to do this by building a module instead of in a stand alone php page.

So far, I have got the below:

$db = \Db::getInstance();
$request = 'SELECT `id_customer`,`date_add`,`firstname` FROM `' . _DB_PREFIX_ . 'customer` LIMIT 20';
/** @var array $result */
$results = $db->executeS($request);
$this->context->smarty->assign('customerdata', $results); 

which I can then echo out in the tpl using

<table>
{foreach $customerdata as $userone}
<tbody>
        <td style="padding-right:10px;">{$userone.id_customer}</td>
        <td style="padding-right:10px;">{$userone.date_add}</td>
        <td style="padding-right:10px;">{$userone.firstname}</td>
</tbody>
{/foreach}
</table>

However, I am unsure how to compare this data with the ps_orders table. Ideally, I want to show the last order date next to each customer. e.g. so the admin can see when the customer last placed an order.

Any help with this would be greatly appreciated

Answer 1

You can edit your query in something like :

SELECT c.id_customer, c.firstname,
(SELECT datediff(CURDATE(), o.date_add) AS number_of_days_from_last_order  
FROM ps_orders o WHERE 
c.id_customer = o.id_customer 
ORDER BY o.date_add DESC limit 1) as last_order_date
FROM ps_customer c
JOIN ps_orders o2 ON c.id_customer = o2.id_customer
GROUP BY c.id_customer

to get all the required data and display it in your TPL, of course you can add a clause on number_of_days_from_last_order value to display only orders older than xxx