Hi list, I'm not sure how to explain this, so let me know if I lose you. I have the same products database as yesterday, but I've just learned that product attributes are stored in their own tables. A product can have many attributes (size, color, weight, etc), and each attribute value is in a table. That table is tied to the product through an attribute assignment table, which lets us write "large" once and then assign that to thousands of products at once, for instance. Essentially, the item_id is a foreign key into attributeAssignment, which also has an attr_value_id. That attr_value_id matches the PK in attr_values, which is the table that actually holds the attribute text.
The problem is that, when I use filter() to join all this stuff together, I get valueCount*productCount rows. That's not really a problem, actually, as it's doing what I want. Putting things back together is going to be a challenge, though. I essentially want, for example, color and size under the same product ID, but my current query will return two different rows with the same ID. One row will have the color, and the next row will have the size. I don't think I can flatten these out, so my next idea is doing post-query processing as I iterate through the results. I'm tempted to just hard-code a sub-loop, to iterate through each n rows, knowing that n will be the number of rows that share an ID. Using grouping should make that work. My fear is that I'll get a set of data which, somehow, has a different size--maybe a missing attribute--and thus my entire system will be off a row or two. My next idea is to store the ID of the first row inside the for loop iterating through all the rows. In that for loop is a while loop: while IDOfNextRow==currentID: (check IDOfNewRow). That way, I can keep related rows together and manually pull out the data I need for each one. Using group-by, I shouldn't ever have a case where a used ID surfaces again way down the line. Is there an easier way I haven't thought of? I can't be the first person to run into this, and SA has a lot of powerful features that make doing DB work easy, so maybe there's something I just don't know about. As I said, I'm not so new to SQL that I just started last week, but neither am I any kind of experienced user at all; maybe SQL itself can offer something here. Thanks for any information/ideas anyone has, and again, let me know if I haven't explained this well enough. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
