[SOLVED] Database query help

Quote

Post by Joe1234 » Thu Dec 22, 2022 12:39 pm

This is what I'm trying to achieve:

Code: Select all

SELECT DISTINCT product_id FROM db_product_to_category WHERE category_id <> 224
This is how the product is situated:
Category 225 -> Category 224 -> Category 223 -> Product

I want to eliminate all products that are a part of 224, but those products will still come back in the query because they are also under category 225 and 223. Basically once it's a part of category 224 I don't want that product returned no matter what other category it's a part of. How can I do this?
Last edited by Joe1234 on Fri Dec 23, 2022 2:22 pm, edited 1 time in total.

v3.0.4.0 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.

Joe1234
Active Member
Posts
637
Joined
Sat Jan 01, 2022 5:47 am
Top

Re: Database query help

Quote

Post by pprmkr » Thu Dec 22, 2022 2:59 pm

Set product.status to 0 and it will be hidden no matter wich category it's in.

Code: Select all

UPDATE oc_product op SET status = 0 WHERE op.product_id IN(SELECT product_id FROM `oc_product_to_category` optc WHERE optc.category_id = 224 AND optc.product_id = op.product_id);
set status to 0 when in category 224
User avatar
pprmkr
Active Member
Posts
1701
Joined
Sat Jan 08, 2011 11:05 pm
Location - Netherlands
Top

Re: Database query help

Quote

Post by paulfeakins » Thu Dec 22, 2022 7:46 pm

Joe1234 wrote: ↑
Thu Dec 22, 2022 12:39 pm
 This is what I'm trying to achieve:

Code: Select all

SELECT DISTINCT product_id FROM db_product_to_category WHERE category_id <> 224
This is how the product is situated:
Category 225 -> Category 224 -> Category 223 -> Product

I want to eliminate all products that are a part of 224, but those products will still come back in the query because they are also under category 225 and 223. Basically once it's a part of category 224 I don't want that product returned no matter what other category it's a part of. How can I do this?
Something like:

Code: Select all

SELECT product_id 
FROM db_product_to_category 
WHERE product_id NOT IN (
SELECT product_id 
FROM db_product_to_category
WHERE category_id = 224
 )

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk

User avatar
paulfeakins
Legendary Member
Online
Posts
10054
Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom
Top

Re: Database query help

Quote

Post by Joe1234 » Thu Dec 22, 2022 11:28 pm

@Paul, thanks that worked. Now can you do me a favor and explain this function as simply as possible. I have actually seen and utilized something like this before, but it wasn't on the top of my mind as a solution because I guess I don't fully understand it. I'm looking at it, and I see it's doing exactly what I want, but nothing is clicking with the logic behind it.

v3.0.4.0 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.

Joe1234
Active Member
Posts
637
Joined
Sat Jan 01, 2022 5:47 am
Top

Re: Database query help

Quote

Post by OSWorX » Fri Dec 23, 2022 8:23 am

Well, that query has 2 parts:

1. select all product ids

Code: Select all

SELECT product_id 
FROM db_product_to_category
would return all products regardless which categories they are assigned to.

But now - and this is the part you might be interested most
2. but IF product id IS 224 then NOT:

Code: Select all

WHERE product_id
NOT IN (
SELECT product_id 
FROM db_product_to_category
WHERE category_id = 224
 )
The query returns also all products which are NOT assigned to the category 224, because this subquery asks (or matches) exactly for that.

Another case could be to check if the products are not assigned to more/many categories.
The query could be then as follows:

Code: Select all

SELECT product_id, FROM oc_product_to_category WHERE category_id NOT IN (224,225,226);
Which means, get all product ids when not assigned to categories 224, 225 and 226

Full Stack Web Developer :: Dedicated OpenCart Development & Support DACH Region
Contact for Custom Work / Fast Support.

User avatar
OSWorX
Administrator
Posts
7852
Joined
Mon Jan 11, 2010 10:52 pm
Location - Austria
Top

Re: Database query help

Quote

Post by Joe1234 » Fri Dec 23, 2022 2:22 pm

Thanks.

v3.0.4.0 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.

Joe1234
Active Member
Posts
637
Joined
Sat Jan 01, 2022 5:47 am
Top

Re: Database query help

Quote

Post by paulfeakins » Fri Dec 23, 2022 5:53 pm

Joe1234 wrote: ↑
Fri Dec 23, 2022 2:22 pm
Thanks.
Yep, what OSWorX said :D

The query almost reads like plain English ... or at least it does to me :crazy:

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk

User avatar
paulfeakins
Legendary Member
Online
Posts
10054
Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: No registered users and 19 guests