[SOLVED] Query builder gone wrong?

Hi,

For a client i'm building an application that will show him some data, not important how or what that is.

This query fails:

PHP Code:

$builder = $orderModel
                ->select("tb1.id, CONCAT('B', LPAD(tb1.is_number, 6, 0)) AS is_number, tb1.total_price, tb1.status, DATE_FORMAT(tb1.created_at, '%Y-%m-%d') AS created_at, tb2.houses")
                ->select("CONCAT_WS(' ',  DATE_FORMAT(tb2.period_start, '%d-%m-%Y'), DATE_FORMAT(tb2.period_end, '%d-%m-%Y')) AS period, CONCAT_WS(' ', tb3.first_name, tb3.last_name) AS relation")
                ->selectSum("tb4.amount_value", 'total_paid')
                ->where('tb1.user_id', user_id())
                ->where('tb1.order_type', 'booking')
                ->from('orders tb1')
                ->join('booking_orders tb2', 'tb2.order_id = tb1.id', 'inner')
                ->join('relations tb3', 'tb3.id = tb1.relation_id', 'inner')
                ->join('payments tb4', 'tb1.id = tb4.order_id', 'left')
                ->groupBy('tb1.id'); 


But this query works

PHP Code:

$builder = db_connect()->query("
            SELECT 
                tb1.id, 
                CONCAT('B', LPAD(tb1.is_number, 6, 0)) AS is_number, 
                tb1.total_price, 
                COALESCE(ROUND(SUM(tb4.amount_value), 2),0) AS total_paid, 
                tb1.status, 
                DATE_FORMAT(tb1.created_at, '%Y-%m-%d') AS created_at, 
                tb2.houses,
                CONCAT_WS(' ',  DATE_FORMAT(tb2.period_start, '%d-%m-%Y'), DATE_FORMAT(tb2.period_end, '%d-%m-%Y')) AS `period`, 
                CONCAT_WS(' ', tb3.first_name, tb3.last_name) AS relation 
            FROM 
                orders tb1
            INNER JOIN 
                booking_orders tb2 ON tb2.order_id = tb1.id
            INNER JOIN 
                relations tb3 ON tb3.id = tb1.relation_id 
            LEFT JOIN 
                payments tb4 ON tb1.id = tb4.order_id 
            GROUP BY
                tb1.id"); 


The mismatch data on failed query is


Is there something wrong with my query in the build i'm unable to see what is going on..
The 'total_paid' should be "107.69" i've got zero clues what i'm doing wrong.

Did you try using the getLastQuery?

PHP Code:

echo $db->getLastQuery(); 


What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Hi @InsiteFX

Thank you for the reply, the first query used to be in my Controller (for testing etc.) after moving it to the Model it seems to be working.
My guess is it has run multiple times because it generates DataTables so this was a simple mistake from my side, the data output is correct now!

I'll mark this topic as solved!

Ok, Thanks for the heads up.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )