Database SELECT to get all label from a specific space

[Henning Karl]

Hello together, 

I search a SELECT to get a list of all labels in a specific space (by SPACE-ID) on the database.

I have already tried a few things here, but nothing gave the right result. 

Yes I know, it gives a macro with options, that I got all labels from a specific space. But I think the macro gives to much results.

Can anyone help me?

[Dominic Lagger · Accepted solution]

Hi @Henning Karl 

I tried a bit and think got a good statement:

Select cl.labelid, l.name, s.spacekey, count(l.name) as count from content_label cl
join label l on l.labelid = cl.labelid
join content c on c.contentid = cl.contentid
join spaces s on s.spaceid = c.spaceid
where c.CONTENT_STATUS != 'deleted' and c.contenttype != 'SPACEDESCRIPTION' and s.spacekey = 'SPACEKEY' group by cl.labelid, s.spacekey, l.name
order by count desc;

This makes a list of all labels of a space. But not "Spacedescriptions" and not deleted pages.

Hope this is a good hint for you.

Regards, Dominic

[Henning Karl]

Hi @Dominic Lagger 

I tried your SQL and it's almost perfect.

I added the following phrase on the where clause, because the SQL returned additional all favourites from each users from the pages in the space.  

and l.name != 'favourite

So the SQL is now this one:

Select cl.labelid, l.name, s.spacekey, count(l.name) as count from content_label cl
join label l on l.labelid = cl.labelid
join content c on c.contentid = cl.contentid
join spaces s on s.spaceid = c.spaceid
where c.CONTENT_STATUS != 'deleted' and c.contenttype != 'SPACEDESCRIPTION' and s.spacekey = 'SPACEKEY' and l.name != 'favourite' group by cl.labelid, s.spacekey, l.name
order by count desc;

Thanks a lot of your help.

Kind regards, 
Henning