Filter query based on subquery condition... Possible?

Wappler General Need Help
1

Hey guys,

I have a query pulling a table and each subtable data (table: Orders, subtable:Order_Products)

image
image1165×917 61.2 KB

I’m trying to filter a query by a subtable column’s value… Is that possible?

for example,
I’m trying to get orders that include products with quantity > 10)

Orders:
1. Order 1 has 2 products,

  • product A, has 5 pieces X 10€
  • product B, has 15 pieces X 10€

2. Order 2 has 2 products,

  • product A, has 25 pieces X 10€
  • product B, has 12 pieces X 10€

3. Order 3 has 3 products,

  • product A, has 15 pieces X 10€
  • product B, has 22 pieces X 10€
  • product C, has 17 pieces X 10€

The result of my query should output only 2 resuls:
Order 2 and Order 3
(Order 1 should not match the condition because has 1 product with quantity 5 pcs)

Any idea will be match appreciated!!

2

I think I found a way to work on this without Custom Query but I get an error…

  1. Before my Main query, I added a query based on my subtable, pulling the Order_ID ONLY when the product_Category_ID exists in the categories_ID Array (GET_catid).

    01-qr_products
    01-qr_products1857×1019 91.8 KB

  2. Then I get an Array from the results of that query and set a condition in my Main_Order

    02-gr_Order_Main
    02-gr_Order_Main1862×1020 105 KB

Works fine and filters the results if the products query return values…
If no match found (empty Array, [ ]), I get this 500 Internal Server Error:

#0 C:\xampp\htdocs\stock\stock\dmxConnectLib\lib\db\Connection.php(112): PDO->prepare()
#1 C:\xampp\htdocs\stock\stock\dmxConnectLib\modules\dbconnector.php(242): lib\db\Connection->execute()
#2 C:\xampp\htdocs\stock\stock\dmxConnectLib\lib\App.php(206): modules\dbconnector->paged()
#3 C:\xampp\htdocs\stock\stock\dmxConnectLib\lib\App.php(178): lib\App->execSteps()
#4 C:\xampp\htdocs\stock\stock\dmxConnectLib\lib\App.php(146): lib\App->execSteps()
#5 C:\xampp\htdocs\stock\stock\dmxConnectLib\lib\App.php(135): lib\App->exec()
#6 C:\xampp\htdocs\stock\stock\dmxConnectLib\lib\App.php(113): lib\App->exec()
#7 C:\xampp\htdocs\stock\stock\dmxConnect\api\admin\orders\crud\order_list.php(7): lib\App->define()
#8 {main}

@Teodor, this is the way that IN condition works?

Working on Wappler v6.0.0 beta8 STABLE channel.
MySQL, php

3

I got it working by adding an extra “switch” to flag the products_Array result and change the specific condition in my main_Orders query to adjust…

03-extraSwitch_forEmptyArray
03-extraSwitch_forEmptyArray1815×1016 103 KB

But my question is still there:

A query with an IN condition should return an error if the related Array is empty or just the condition is not met and returns 0 records?

5

The query will return an error, just like any other condition where not a valid value is passed. And empty string will generate a query like SELECT * FROM table WHERE col IN () which is invalid. That is why we have conditional conditions where you specify when it should be applied.

We could assume that when the array is empty you probably didn’t want to filter on it, but does that mean you wanted it to return all records or none? This is not only for IN, but also for other conditions. When you have SELECT * FROM table WHERE id = ? and the value for the id is not set, should we then assume 0 and return nothing or did you mean to return them all?

1 Like
6

Thanks Patrick!
You are right brother…
I just didn’t know if I did something wrong or that’s the right behavior but now I got it!

In my case I need to return a mismatch (The value DOES NOT EXIST in the array). It means NONE record for me…

Anyway now I know what I’m dealing with and I’m trying to proceed in different way.

Thank you!!