How to select parent category if sub category has listings?

[RJP1]

Hi guys,

 

I have a sql query that selects categories as long as they are a parent and they have items published within it:

 

SELECT c.* FROM #__categories AS c WHERE (SELECT COUNT(catid) FROM #__items WHERE catid=c.id AND published=1) > 0 AND parentid=0 ORDER BY name ASC

 

However, I'm having difficulty combining the above query to also get subcategories that are within a parent category but only when the subcategory has items in it and not its parent.

 

I want to get these "empty" parents if they contain subcategories with items in them... possible?

 

Cheers, RJP1

[fenway]

This sounds a lot like the other topic you just started. 

[RJP1]

Yes, but it's not. This issue I figured out.

 

Cheers, RJP1

[fenway]

Care to post the solution?

[RJP1]

Sure, this is what I did:

 

function getCategories()
{
	$db =& JFactory::getDBO();
	$query = 'SELECT DISTINCT c.parentid FROM #__vault as i,jos_vault_categories as c WHERE i.catid=c.id AND c.parentid>0 AND i.published=1';
	$db->setQuery( $query );
	$parentidswithchildren = $db->loadObjectList();

	if ($parentidswithchildren) {
	$cat_ids = array();

	foreach ( $parentidswithchildren AS $c ) {
		$cat_ids[] = $c->parentid;
	}
	$in = "c.id IN (".implode(',',$cat_ids).") OR ";
	} else {
	$in = null;
	}

	$query = "SELECT c.* FROM #__vault_categories AS c WHERE [b]$in[/b](SELECT COUNT(catid) FROM #__vault WHERE catid=c.id AND published=1) > 0 AND parentid=0";

	$db->setQuery( $query );
	$categories = $db->loadObjectList();

    return $categories;
    }

 

If there are children, it puts their id's into a comma separated list as $in.

 

Works well. What do you think?

 

RJP1

[fenway]

Why 2 separate queries?

[RJP1]

Because that's how I did it. I wasn't able to do it in one, hence my asking here.

[fenway]

Sorry, I was confused by "solved".