+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: [SOLVED] Query builder gone wrong? (/showthread.php?tid=87176)
[SOLVED] Query builder gone wrong? - superior - 03-22-2023
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
![[Image: 9MUPblDgrYop.png?o=1]](https://gcdnb.pbrd.co/images/9MUPblDgrYop.png?o=1)
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.
RE: Query builder gone wrong? - InsiteFX - 03-22-2023
Did you try using the getLastQuery?
PHP Code:
echo $db->getLastQuery();
RE: Query builder gone wrong? - superior - 03-23-2023
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!
RE: [SOLVED] Query builder gone wrong? - InsiteFX - 03-23-2023
Ok, Thanks for the heads up.