Repeat region help with filtering

[psweb]

Im a little stuck here, any help would be great

I have three database tables
ProductsTable > Fields (product_id, product_description)
GroupsTable > Fields (group_id, group_name)
PriceTable > Fields (price_id, group_id_fk, product_id_fk, price)

The reason behind this is my site sells a single set of products, but the price of each product is different according the group I will be selling to.

Typical data placed in these tables would be

• ProductsTable
• 1 > Apples
• 4 > Oranges
• 6 > Grapes

---

• GroupsTable
• 16 > Shops
• 21 > Malls
• 23 > EndUser

---

• PriceTable
• 117 > 16(Shops) > 1(Apples) > $3,00
• 119 > 16(Shops) > 4(Oranges) > $4,00
• 125 > 16(Shops) > 6(Grapes) > $2,00
• 141 > 21(Malls) > 4(Oranges) > $3,75
• 163 > 23(EndUser) > 1(Apples) > $4,00
• 166 > 23(EndUser) > 6(Grapes) > $3,00

---

What I am trying to do is make a list, filtered by Group, where it only returns all available products that have not had a price set already.

So if I chose from a select dropdown, “Shops” it should return nothing because all pricing for Shops has already been set.
If I choose Malls, it should only show me Apples and Grapes because I only have a price set for Oranges.
If I choose EndUser, it should only show me Oranges because it is the only product left without price.

I hope this makes sense. It seemed so simple to me until i actually tried to do it. Just can not get the correct result returned.

[s.alpaslan]

you can use formatter (where condtions) - (dynamic)

[psweb]

I have tried that, but still can not get it right.

I did a query like this
SELECT ProductsTable.product_id, ProductsTable.product_description, PriceTable.group_id_fk, PriceTable.product_id_fk, PriceTable.price FROM ProductsTable LEFT JOIN PriceTable ON (PriceTable.product_id_fk = ProductsTable.product_id)

This gives me my entire products list with many duplicates, which is good for now i suppose
Output
1 > Apples > 16 > 1 > $3,00
1 > Apples > 23 > 1 > $4,00
4 > Oranges > 16 > 4 > $2,00
4 > Oranges > 21 > 4 > $3,75
6 > Grapes > 16 > 6 > $2,00
6 > Grapes > 23 > 6 > $3,00

Then i filter the results by removing all the group_id_fk that are equal to a select dropdown that already has all the groups and values assigned so my repeat expression is a simple serverconnect.data.query to get all the data, then i add a dmx-hide=“group_id_fk == group_select.value”

If I now choose in my group_select lets say Malls, with a value of 21 this is the
Output
1 > Apples > 16 > 1 > $3,00
1 > Apples > 23 > 1 > $4,00
4 > Oranges > 16 > 4 > $2,00
//////4 > Oranges > 21 > 4 > $3,75 (Just this one removes)
6 > Grapes > 16 > 6 > $2,00
6 > Grapes > 23 > 6 > $3,00

and I want the output to be
1 > Apples > 16|23(Does Not Matter) > 1|1(Does Not Matter) > $3,00|$4,00(Does Not Matter)
6 > Grapes > 16|23(Does Not Matter) > 6|6(Does Not Matter) > $2,00|$3,00(Does Not Matter)

The reason I say does not matter is because I will only display the ProductsTable.product_description and have a hidden input with the ProductsTable.product_id, and an input allowing the user to enter a price.
So the insert will be
Insert to price table, group_id_fk = group_select.value, product_id_fk = product_id, price = price

I really hope this is making sense

[psweb]

I had to make changes to the way the system works but a have a working solution now, because i really could not get this idea to work as i wanted. Thanks anyway guys.

[t11]

Please check this too. It might help you.

MySQL Subquery in Query Builder

HI, thank you very much for your solutions, sometimes things are easier than they appear… I did the same: I inserted GET Variable Id and make the steps as you said: [21] Then made the Repeat picking up the table values I needed [51] In the second query done inside the repeat, I used the following conditions: [05] I got the page with listed ALL the users, then I simply used HIDE the line of the table if query 2 has items. [39] Got the result. [20] Is that ok Nikolas ? …

[psweb]

Thanks @t11, unfortunately that was the first way i tried to do this, because i thought if i get all the products as one query and then filter in all the prices as a second query on the repeat step then I could use a NOT EQUAL operator, and it honestly night have even worked if the table joins area did not currently have a bug where the not equal is just not working right now.
Query Builder Bug in Joins when using various operators

[Teodor]

So Paul, you only want to show the products which do not have a value in the price field in the PriceTable for the selected category, is that right?

[psweb]

Yes pretty much, I want to only show products from the products table that is linked to the price table where there is no price assigned for the product.

So I have 10 products in a self contained products table
Then i have a price table with its own id, the product id, the group/shop/franchise id, and a price.

I might have 3 prices in the table for group/shop/franchise Wallmart for products with id 2,3,5 and a price set for each

I want to allow the administrator to see a list of products that do NOT have prices assigned only for Wallmart

[Teodor]

Have you tried joining the price and products table, then in the conditions only show products which value is == null?
Additionally just add the GET variable to filter by category if you need to.

[psweb]

I think I already tried that, but I tried so many things that i could be wrong, going to give it another shot and see if this idea works, thanks for all the suggestions @Teodor

[Hyperbytes]

Does the Price table hold an entry for a Product / Group with a zero cost or is there no in the table entry at all? I assume the latter?

[psweb]

@Hyperbytes At the time when i just could not get this right then yes, the price table only held real entries so although i had 10 products in my products table the price table had about 14 entries, 4 products from group A, 5 products from Group B, and 5 products from Group C.

It looked something like this
GROUPS/FRANCHISE Table with no references or foreign keys to any other tables
Group A; Group B; Group C

PRODUCTS Table with no references or foreign keys to any other tables
Prod 1; Prod 2; Prod 3; Prod 4; Prod 5; Prod 6; Prod 7; Prod 8; Prod 9; Prod 10

PRICE Table with price; group_id; product_id

1. 10.00; A; 2;
2. 11.00; A; 4;
3. 11.50; A; 5;
4. 11.75; A; 7;

---

1. 10.20; B; 1;
2. 11.10; B; 2;
3. 11.60; B; 4;
4. 11.95; B; 6;
5. 12.75; B; 9;

---

1. 13.00; C; 2;
2. 10.00; C; 5;
3. 10.50; C; 6;
4. 14.75; C; 9;
5. 15.75; C; 10;

[Hyperbytes]

Yes, in a case like this i would be tempted to look at the use SQL triggers to create zero price entries at the time of creation of a product or group and then filter them accordingly later. This would simplify the client side greatly as the queries would simply need clauses "where Price = " or "where Price <> "

[Hyperbytes]

Basic Table structure could be

Table Groups
[GroupID int Primary Key Auto Increment]
[GroupName Varchar nnnn]

Table Products
[ProductID int Primary Key Auto Increment]
[ProductName Varchar nnnn]

Table Prices
[GroupID int]
[ProductID int]
[Price numeric 5.2]

No strict need for a primary key in this table

[psweb]

Thanks, good to know for future use, I have never used triggers, so i will look into that.

What I actually did to get this working in the end was exactly this but done in an entirely different way without using triggers. Haha, when you see in the videos, you might have a little laugh at the way I got it working.

[Hyperbytes]

Yes, multiple inserts can do the same, triggers are just much easier once set up as they are set at database level and happen automatically… Most database managers (i use Navicat mainly but not exclusively ) allow you to input triggers quite easily but i accept they can be fiddly to get right, stick to what you are comfortable with, if it works OK it’s an acceptable solution in my books