SQL query to get default category

English forums
1

Hello,

I’d like to write SQL query for selecting default category for given product (article). Any idea where in the database is this information stored?

Thanks a lot.

2

Maybe this could do it for you:

SELECT oxtitle
FROM oxcategories
WHERE oxid
IN (

SELECT oxcatnid
FROM oxobject2category
WHERE oxobjectid = '$catid'
)
3

Thanks, that query returned all categories for given product.

Now, how can I tell which one is set as “the main category” for the product please?

4 

SELECT oxtitle
FROM oxcategories
WHERE oxparentid = 'oxrootid'
AND oxid
IN (

SELECT oxcatnid
FROM oxobject2category
WHERE oxobjectid = '$catid'
)
5

It looks like it’s filter for the “root category” (in menu hierarchy). It’s not “the main category” for the product… Thanks anyway :slight_smile:

6

Check attached screenshot to see what I mean :slight_smile:

7

Ah, than use this


SELECT oxtitle
FROM oxcategories
WHERE oxparentid = 'oxrootid'
AND oxid
IN (

SELECT oxcatnid
FROM oxobject2category
WHERE oxobjectid = '$catid' and oxtime = 0
)
8

Hmm… it’s not working either…

It really looks like the main category got the smallest time value, I think something like this would work:


SELECT *
FROM oxcategories
WHERE
oxid
IN (

SELECT oxcatnid
FROM oxobject2category
WHERE oxobjectid = '0e310ecdba3ef7adcc793f0f62ab4afe'
ORDER BY OXTIME
LIMIT 1
)

Unfortunately MySQL 5.1 doesn’t support LIMIT in subqueries…

There’s no need to make one single query anyway, I can do it using 2 queries in my script.

9

It looks like your previous query (OXTIME = 0) is working fine for newly created products…

But there are strange values in OXTIME column for old products (created in older OXID version 1-2 years ago)… maybe I just should re-assign the main category in admin for all products to get OXTIME values back to normal.

10

You could use

...WHERE oxtime = 0
or oxtime = xyz