Flexible Search Query to find the count of supercategories of a category

[former_member630291]

I want to get the count of supercategories of a category. Basically, I want to check if a category's supercategories has only one entry through a flexible search query.

I found there's a categorycategoryrelation but totally confused how to get this.

A query would be a great help.

Thanks in Advance

[andyfletcher]

On a relation type you'll want to use the source and target attributes, where source is the pk of the parent category and target is the pk of the child category.

If you know the pk then you could use this:

 select count(*) from {categorycategoryrelation} where {target}=?category

If you want to search by category code and catalogversion then something like this:

It sounds like what you are trying to do needs an aggregate query though. Something like this?

 select {child.code},count(*) from {categorycategoryrelation as rel join category as child on {rel.target}={child.pk} join category as parent on {rel.source}={parent.pk} join catalogversion as cv on {child.catalogversion}={cv.pk} join catalog as c on {cv.catalog}={c.pk}}
 where {c.id}='myCatalog' and {cv.version}='Online'
 group by {child.code}
 having count(*)=1