• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Mysql request problem

#1
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.
Reply

#2
@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/datab...y-bindings
Reply

#3
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.idproducts.referenceproducts.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 qtySUM('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
Reply

#4
@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?
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.