+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Mysql request problem (/showthread.php?tid=72804)
Mysql request problem - alain - 02-13-2019
Hello,
I can not solve the good qty in this request.
She always gives me double the value of qty.
The result is false when I put the following condition:
PHP Code:
$this->db->select_sum('qty_alert');
$this->db->join('stocks_alert', 'stocks_alert.id_product = stocks.id_product');
$this->db->having('qty < qty_alert');
Here is the complete request:
PHP Code:
$this->db->select('products.id, products.reference, products.label');
$this->db->select_sum('qty_alert');
$this->db->select("(SUM(CASE WHEN slug IN ('orders','variation','transfer','add') THEN stocks.qty ELSE 0 END) - SUM(CASE WHEN slug ='used' THEN stocks.qty ELSE 0 END)) as qty");
$this->db->from('products');
$this->db->join('stocks', 'stocks.id_product = products.id');
$this->db->join('stocks_alert', 'stocks_alert.id_product = stocks.id_product');
$this->db->group_start();
$this->db->like("products.reference", $_POST["search"]["value"]);
$this->db->or_like("products.label", $_POST["search"]["value"]);
$this->db->group_end();
$this->db->having('qty < qty_alert');
$this->db->group_by('products.id');
I also tried sub-query, but I can not do it.
Would anyone have an idea to put me on the track.
Thank you to all of you.
Excuse me for my english.
RE: Mysql request problem - php_rocs - 02-13-2019
@alain,
Have you been able to run this query directly in MySQL? What does the fully constructed query look like?
I prefer to use the query binding approach when creating my queries (to me it is more readable)... https://codeigniter.com/user_guide/database/queries.html?highlight=query%20builder#query-bindings
RE: Mysql request problem - alain - 02-14-2019
Thank you for your reply.
I did not know the query binding approach. I will deepen it for my developments.
Yes, my request works perfectly.
My problem is that at one point I have to do some qty_alert to compare it to qty.
At this moment the result qty becomes false doubles its value.
Here is the query built:
PHP Code:
SELECT products.id, products.reference, products.label, (SUM(CASE WHEN slug IN ('orders', 'variation', 'transfer', 'add') THEN stocks.qty ELSE 0 END) - SUM(CASE WHEN slug ='used' THEN stocks.qty ELSE 0 END)) as qty, SUM('qty_alert') AS qty_alert
FROM products JOIN stocks ON stocks.id_product = products.id JOIN stock_alert ON stocks_alert.id_product = stocks.id_product
WHERE ( products.reference LIKE '%%' ESCAPE '!' OR products.label LIKE '%%' ESCAPE '!' ) GROUP BY products.id HAVING qty < qty_alert;
RE: Mysql request problem - php_rocs - 02-14-2019
@alain,
You probably have multiple records in one of your joins. I would view each join's query individually to see what values are there. Also, I noticed that you used JOINs instead of LEFT JOINS? Any reason for that?
RE: Mysql request problem - alain - 03-20-2019
Thank you for your help.
I have reconsidered all my request and now it is working properly. That's what I did as a request:
Code:
SELECT products.id, products.reference, products.label, products.category, products.image, stocks.id_warehouse, (SUM(CASE WHEN slug IN ('orders', 'variation', 'transfer', 'add') THEN stocks.qty ELSE 0 END) - SUM(CASE WHEN slug ='used' THEN stocks.qty ELSE 0 END)) as qty, SUM(CASE WHEN stocks_alert.id_product = stocks.id_product THEN stocks_alert.qty_alert ELSE 0 END) as qty_alert FROM products LEFT JOIN stocks ON stocks.id_product = products.id LEFT JOIN stocks_alert ON stcks_alert.id_warehouse = stocks.id_warehouse WHERE (products.reference LIKE '%%' ESCAPE '!' OR products.label LIKE '%%' ESCAPE '!') GROUP BY products.id HAVING (`qty` < `qty_alert`)RE: Mysql request problem - php_rocs - 03-21-2019
@alain,
Glad I could help.