Symphony.

Hi everyone. I'm about to test your seemingly angelic patience!

I'm brand new to Symphony and already adore it. I have a slightly-more-complex-than-normal data source question. I've searched through the forum and couldn't find something similar enough, though what I have found has been really helpful.

I have 3 sections: Products, Brands & Categories.

Each Product has a Subsection Manager for one or more Categories and a Select Box Link for a single Brand.

What I would like to do on the front end is list all the categories that have products of a particular brand (filtering out categories that don't). The URL structure could be /categories?brand=sony, or /sony/categories.

It looks to me that there's some reverse engineering involved, and I'm trying to get my head around how to go about it.

...Ok, while writing this a possible solution occurred to me. Would I list products filtered by brand, and then group by category, then just list those categories in the template? Surely that's not a very streamlined method as there's a lot of unwanted data in the XML, namely the products, which could theoretically be in the thousands. Your thoughts on the best method would be greatly appreciated.

Thanks for reading!

Surely that's not a very streamlined method as there's a lot of unwanted data in the XML, namely the products, which could theoretically be in the thousands

Yep that was my thinking. That's the only native way of achieving this, since your product entry is the link between mapping a category to a brand, so it'll be the thing you'll pivot on.

To do this most efficiently you'd get dirty with some raw SQL. You'll want to join a few tables together:

• the title/name field in the Brands section to filter on initially (fetching a brand ID by its handle from the URL)
• join the table for the "Brand" SBL field from the products section with the Categories SSM table
• select rows from the Brand SBL table where the relation_id column matches the chosen brand ID
• this gives you all categories for all products in this brand
• group by the category ID, thereby removing duplicate categories
• join to the title/name field in the Categories section to get the category name

If your SQL isn't so good then the above might sound horrifically complicated, but it is certainly the most efficient way of achieving this.

Hi Nick,

Thank you for your reply. I've done raw SQL in the past and dealt with various joins, so your explanation makes sense (though I'm gonna have to scrape some rust off that part of my brain before attempting it).

So I'm already in the realm of custom data sources! Could I use your SymQL extension to accomplish this perchance?

I've done raw SQL in the past and dealt with various joins, so your explanation makes sense

Goodo! When your SQL is complete, throw it into to execute it.

Symphony::Database->fetch("sql here...");

Could I use your SymQL extension to accomplish this perchance?

'fraid not! SymQL is still intended for working with entries plain and simple, just a nicer wrapped like a data source. It doesn't aid with anything more custom I'm afraid.

Good luck!