Take the 2-minute tour ×
ExpressionEngine® Answers is a question and answer site for administrators, end users, developers and designers for ExpressionEngine® CMS. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 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.

share|improve this answer
    
Thanks Adrian. It's pretty easy to tell what's going on in the database, but harder for me to write the MySQL query that would do what I want ("give me all the values of modifiers that apply to the SKU") as I'm kind of only baby steps into MySQL. However, my workaround (for now) is that my SKUs will just have markers in them like -sm, -lg, -f, -m and -color so they can be filtered by which product entry an SKU belongs to and so that they can be grouped by modifier. Eventually I'll be able to write the more complex query down the road. –  Rob Butz Jan 1 '13 at 22:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.