FlexibleSearch - search products & categories

Question

I'm trying to do a flexibleSearch to retrieve Products and his Leaf Categories. The leaf categories are the last categories that have no other subcategories, and the categories must be of type "category" and "productTypeCategory". I try to make some joins but I failed. I appreciate any help!

Answer 1

I would suggest something like this:

SELECT {c.code}, {c.itemtype}, {ccr.source} FROM 
  {Category as c LEFT JOIN CategoryCategoryRelation as ccr ON {c.pk}={ccr.source} 
  JOIN ComposedType as ct ON {c.itemtype} = {ct.pk}} 
WHERE {ccr.source} IS NULL AND {ct.code} = 'Category'

At least, it should be a beginning. It will return the LEAF cats of type Category.

The rest is Joining with Products and probably considering catalog versions. Depending on the concrete use case you haven't provided, it is maybe better to ask Solr for the final result and you use the category result of my query to pass to the solr query, instead of implementing the additional JOINs, but that depends on if you're in the storefront or if you need the info for a backoffice/cronjob etc.