lib/library.js
function(parentCategoryId, languageSlug, translateMode) {
            let preferredSlug = [languageSlug, 'en', '%'];
            let categories = [];
            if(translateMode) {
                categories = query('select \'category\' as type, c.slug as name, \'\' as source_language_slug,' +
                    ' c.id, c.slug, c.parent_id, count(p.id) as num from category as c' +
                    ' left join (' +
                    '  select p.id, p.category_id, count(r.id) as num from project as p' +
                    '  left join resource as r on r.project_id=p.id and r.translate_mode like(?)' +
                    '  group by p.slug' +
                    ' ) p on p.category_id=c.id and p.num > 0' +
                    ' where parent_id=? and num > 0 group by c.slug', [translateMode, parentCategoryId]);
            } else {
                categories = query('select \'category\' as type, category.slug as name, \'\' as source_language_slug, * from category where parent_id=?', [parentCategoryId]);
            }

            // find best name
            let catNameQuery = 'select sl.slug as source_language_slug, cn.name as name' +
                ' from category_name as cn' +
                ' left join source_language as sl on sl.id=cn.source_language_id' +
                ' where sl.slug like(?) and cn.category_id=?';
            for(let cat of categories) {
                for(let slug of preferredSlug) {
                    let result = query(catNameQuery, [slug, cat.id]);
                    if(result.length > 0) {
                        cat.name = result[0]['name'];
                        cat.source_language_slug = result[0]['source_language_slug'];
                        break;
                    }
                }
            }

            let projects = query('select * from (' +
                ' select \'project\' as type, \'\' as source_language_slug,' +
                ' p.id, p.slug, p.sort, p.name, count(r.id) as num from project as p' +
                ' left join resource as r on r.project_id=p.id and r.translate_mode like (?)' +
                ' where p.category_id=? group by p.slug' +
                ')' + (translateMode ? ' where num > 0' : ''), [translateMode || '%', parentCategoryId]);
            // find best name
            let projNameQuery = 'select sl.slug as source_language_slug, p.name as name' +
                ' from project as p' +
                ' left join source_language as sl on sl.id=p.source_language_id' +
                ' where sl.slug like(?) and p.slug=? order by sl.slug asc';
            for(let proj of projects) {
                for(let slug of preferredSlug) {
                    let result = query(projNameQuery, [slug, proj.slug]);
                    if(result.length > 0) {
                        proj.name = result[0]['name'];
                        proj.source_language_slug = result[0]['source_language_slug'];
                        break;
                    }
                }
            }
            return categories.concat(projects);
        }
function(parentCategoryId, languageSlug, translateMode) {
            let preferredSlug = [languageSlug, 'en', '%'];
            let categories = [];
            if(translateMode) {
                categories = query('select \'category\' as type, c.slug as name, \'\' as source_language_slug,' +
                    ' c.id, c.slug, c.parent_id, count(p.id) as num from category as c' +
                    ' left join (' +
                    '  select p.id, p.category_id, count(r.id) as num from project as p' +
                    '  left join resource as r on r.project_id=p.id and r.translate_mode like(?)' +
                    '  group by p.slug' +
                    ' ) p on p.category_id=c.id and p.num > 0' +
                    ' where parent_id=? and num > 0 group by c.slug', [translateMode, parentCategoryId]);
            } else {
                categories = query('select \'category\' as type, category.slug as name, \'\' as source_language_slug, * from category where parent_id=?', [parentCategoryId]);
            }

            // find best name
            let catNameQuery = 'select sl.slug as source_language_slug, cn.name as name' +
                ' from category_name as cn' +
                ' left join source_language as sl on sl.id=cn.source_language_id' +
                ' where sl.slug like(?) and cn.category_id=?';
            for(let cat of categories) {
                for(let slug of preferredSlug) {
                    let result = query(catNameQuery, [slug, cat.id]);
                    if(result.length > 0) {
                        cat.name = result[0]['name'];
                        cat.source_language_slug = result[0]['source_language_slug'];
                        break;
                    }
                }
            }

            let projects = query('select * from (' +
                ' select \'project\' as type, \'\' as source_language_slug,' +
                ' p.id, p.slug, p.sort, p.name, count(r.id) as num from project as p' +
                ' left join resource as r on r.project_id=p.id and r.translate_mode like (?)' +
                ' where p.category_id=? group by p.slug' +
                ')' + (translateMode ? ' where num > 0' : ''), [translateMode || '%', parentCategoryId]);
            // find best name
            let projNameQuery = 'select sl.slug as source_language_slug, p.name as name' +
                ' from project as p' +
                ' left join source_language as sl on sl.id=p.source_language_id' +
                ' where sl.slug like(?) and p.slug=? order by sl.slug asc';
            for(let proj of projects) {
                for(let slug of preferredSlug) {
                    let result = query(projNameQuery, [slug, proj.slug]);
                    if(result.length > 0) {
                        proj.name = result[0]['name'];
                        proj.source_language_slug = result[0]['source_language_slug'];
                        break;
                    }
                }
            }
            return categories.concat(projects);
        }
function ( parentCategoryId , languageSlug , translateMode ) { let preferredSlug = [ languageSlug , 'en' , '%' ] ; let categories = [ ] ; if ( translateMode ) { categories = query ( 'select \'category\' as type, c.slug as name, \'\' as source_language_slug,' + ' c.id, c.slug, c.parent_id, count(p.id) as num from category as c' + ' left join (' + '  select p.id, p.category_id, count(r.id) as num from project as p' + '  left join resource as r on r.project_id=p.id and r.translate_mode like(?)' + '  group by p.slug' + ' ) p on p.category_id=c.id and p.num > 0' + ' where parent_id=? and num > 0 group by c.slug' , [ translateMode , parentCategoryId ] ) ; } else { categories = query ( 'select \'category\' as type, category.slug as name, \'\' as source_language_slug, * from category where parent_id=?' , [ parentCategoryId ] ) ; } // find best name let catNameQuery = 'select sl.slug as source_language_slug, cn.name as name' + ' from category_name as cn' + ' left join source_language as sl on sl.id=cn.source_language_id' + ' where sl.slug like(?) and cn.category_id=?' ; for ( let cat of categories ) { for ( let slug of preferredSlug ) { let result = query ( catNameQuery , [ slug , cat . id ] ) ; if ( result . length > 0 ) { cat . name = result [ 0 ] [ 'name' ] ; cat . source_language_slug = result [ 0 ] [ 'source_language_slug' ] ; break ; } } } let projects = query ( 'select * from (' + ' select \'project\' as type, \'\' as source_language_slug,' + ' p.id, p.slug, p.sort, p.name, count(r.id) as num from project as p' + ' left join resource as r on r.project_id=p.id and r.translate_mode like (?)' + ' where p.category_id=? group by p.slug' + ')' + ( translateMode ? ' where num > 0' : '' ) , [ translateMode || '%' , parentCategoryId ] ) ; // find best name let projNameQuery = 'select sl.slug as source_language_slug, p.name as name' + ' from project as p' + ' left join source_language as sl on sl.id=p.source_language_id' + ' where sl.slug like(?) and p.slug=? order by sl.slug asc' ; for ( let proj of projects ) { for ( let slug of preferredSlug ) { let result = query ( projNameQuery , [ slug , proj . slug ] ) ; if ( result . length > 0 ) { proj . name = result [ 0 ] [ 'name' ] ; proj . source_language_slug = result [ 0 ] [ 'source_language_slug' ] ; break ; } } } return categories . concat ( projects ) ; }
Returns an array of categories that exist underneath the parent category.
The results of this method are a combination of categories and projects.

@param parentCategoryId {int} the category who's children will be returned. If 0 then all top level categories will be returned.
@param languageSlug {string} the language in which the category titles will be displayed
@param translateMode {string} limit the results to just those with the given translate mode. Leave this falsy to not filter
@returns {[{}]} an array of project categories
Returns an array of categories that exist underneath the parent category . The results of this method are a combination of categories and projects .