Direct access to exp_store_stock exp_store_stock_options tables via Expresso Store tags?

Question

I was wondering if any tags in Expresso Store just give you direct access to the exp_store_stock and exp_store_stock_options tables.

What I want to do, when I display a store item, is filter the entries in the exp_store_stock table by associated entry_id. Rather than using drop-down options, which seem cumbersome as an interface, I want to have a button representing each SKU for the product that (when clicked) adds one of that variation to the cart.

So, obviously, if there's 16 sizes to a t-shirt available in two colours (2 colour variations, male and female variations for each, and four sizes for each of these), then I'd want to grab just the SKUs associated with these.

Then if there's a zero in the stock_level column, print out a disabled button, and if not, print out an enabled button that (when clicked) adds 1 of that SKU variation to the cart.

What I would like to do -- that's a little trickier -- is filter this list by other product modifiers by modifier_name, and right now, I don't see how I'd do that. So what I'm looking for is to not only filter the exp_store_stock entries by entry_id, but also by the values of modifier_name that apply to that SKU.

So for example, because t-shirt SKUs describe subsets of the stock — gender (male or female), size (S, M, L, XL) and colour (red or green) — characteristics which apply to each SKU, I'd like to product some kind of output in a loop.

• colour 1 (red)
  • gender 1 (female)
    • stock of size 1 (small)
    • stock of size 2 (medium)
    • stock of size 3 (large)
    • stock of size 4 (x-large)
  • gender 2 (male)
    • stock of size 1 (small)
    • stock of size 2 (medium)
    • stock of size 3 (large)
    • stock of size 4 (x-large)
• colour 2 (green)
  • gender 1 (female)
    • stock of size 1 (small)
    • stock of size 2 (medium)
    • stock of size 3 (large)
    • stock of size 4 (x-large)
  • gender 2 (male)
    • stock of size 1 (small)
    • stock of size 2 (medium)
    • stock of size 3 (large)
    • stock of size 4 (x-large)

The tricky thing is that while I might be able to accomplish this by filtering by product_opt_id, I want to filter an entry_id's SKU with a common modifier name.

Product_opt_id assumes that the modifiers for every product are different between every product -- the IDs refer back to the exp_store_product_modifiers table. But if it were possible to just filter the SKUs by modifier name then I could output and use conditionals on them the way I describe in my loop. My modifier names are common to all my products: size, gender, colour.

So to sum up, I guess I'm looking for a query that says "show me the SKUs associated with this entry ID that have the value of these modifier applied to them. But a way to do this with tags.

Doable, or do I need to write a query? Can anyone nudge me toward the way to go?

I know I may be asking for more than what Store can do -- thanks for everyone's cogitation about this in advance!

Answer 1

There are no built in tags to do what you want, but you should be able to achieve this using the EE query module.

It sounds like you have a good handle on the database schema, so I will leave writing the specific SQL query up to you.

Once you have some results from the query tag, you can easily loop through these and output multiple product tag forms (add to cart forms), and create a button for each SKU.