product based follow-ups

Can you elaborate more on this? I'm not sure I understand.

I'm not sure if it's what Sam was meaning, but in my scenario, we want to send a followup when a customer has purchased (payment received or completed) a specific product. So,

Customer 1 purchases product A. Three weeks later, he gets an email with a discount for another product.
Customer 2 purchases product B. He doesn't get a followup, because he didn't purchase product A.

Any thoughts on this?
Thanks,
Isaac

I suppose it would be possible to add a multi-select list to the followup set-up form, for optionally selecting products that trigger the followup. So, along the same lines as the example you proposed, when you're defining a followup, an additional select list would be provided that lists all the products in your site. You could optionally select one or more products from the list, and that would cause additional conditions to be added to the query that creates the followups.

It would definitely add extra overhead to the already-huge query used to find followups, however. But as long as the products section was optional, then this would be a performance-hit only for people who choose to use it, so they could make the decision whether or not to. Another thing to consider, though, is shops with a huge amount of products. The shop I'm working with now has over 12000 products... which would be a ridiculously long select list, thus making it basically unusable. So maybe the select list isn't the best approach.

A autocomplete field might be a better fit here. I'm comfortable enough with coding the admin side of things -- it's the query-writing part that throws me off.

FWIW, I'd love to see the order status as a multi-select (checkboxes or whatever) as well -- for our use case, we're looking for orders that are either "completed" or "payment received", and we'll have to define two separate followups to meet that. No biggie, though.

The query part wouldn't be too hard, I don't think. I'd have to take a closer look at it.

If you want to code up the admin side and submit it as a patch to this issue, maybe we can get the ball rolling on it. No guarantees that I'll have time to work on it anytime soon, but maybe someone else will. At the very least I would be happy to provide input and direction on the query side of things as well.

Sounds good. I'll probably try to put a patch together this afternoon. Not sure I'll be able to have it wrapped up today, but it shouldn't be *too* bad, and I think I have code from another project I can pull in as a starter.

Alright, here's a patch that adds the field to the database, the autocomplete field to the form, and handles storing the data on the followup item. It does not yet do anything as far as actually *using* that data.

Note that I'm showing node titles right now to the user, but SKUs might be more readable.

If I don't end up trying my hand at the query, I set up the field to take serialized data, and if you unserialize it, the array key is the nid of the product that should be used. I won't be able to do anything with the query side of things till at least Monday, I think, so if you've got time, go for it.

Awesome! Thanks for taking the initiative on this.

My one thought is regarding the data storage: I'm not sure if serialized data will work well with the query. Here's why...

Currently all the logic for generating the pending emails is contained within the SQL query itself. So in order to add a product condition to it, I think it would need to JOIN in the {uc_order_products} table, and filter it based on the node ids of products that are selected in the followup set-up form (which you added). The problem is that there's no way to unserialize the list of product IDs within the query.

It may work to just save them as a comma-separated list of node ids, and then add an IN statement to the query for filtering.

Now that I think more about it, I was wrong about what I said before: there's no way to make the this optional in the query. The query will need to perform the JOIN with {uc_order_products} all the time if we add this feature. But maybe it won't be much of a performance hit if there aren't any products selected to join with? I'm not sure...

Also, if you could submit a patch generated with Git, that would be even more helpful. Here are instructions for that: https://drupal.org/project/uc_followup/git-instructions

If you don't have Git set up, don't worry about it. I'd rather have you submitting code than giving up trying to get Git up and running. But I would definitely recommend learning Git if you haven't already. It's WELL worth it. ;-)

I plan to use Git at some point, but haven't taken the time to set it up, as the shops I work with are all still using SVN :p

The reason I ended up doing the serialized data is that, that way there didn't need to be a query to look up the product name each time it was displayed (i.e., in the followup listing table and the followup edit screen). I should have looked more closely at your query before doing that -- I assumed you were pulling data out before doing the query, which would have allowed that field to be manipulated a bit before doing the final query.

Would you like me to tweak the code so it does the comma-separated nids, or do you want to handle it?

Isaac

Feel free to continue with it if you need to. Like I said before I'm not sure when I'll have time to put nto it myself. The feature isn't a requirement for any of the projects I'm currently working on, but I'd be happy to commit it if it makes sense and doesn't reduce the performance of the query significantly for those who aren't using it.

Hey, here's the updated comma-separated version. Still an SVN diff... Someday, someday...

Alright, here's a new patch with the query and a fix to a problem I introduced when doing the comma-separated NIDs. I don't really have a good DB for doing performance testing on right now, and haven't, you know, actually tested sending a followup, but it *should* work, and I've tested the queries.

Take a look and let me know if you see anything. In the meantime, I'm going to test followup sending.

Dangit, didn't test thoroughly enough. Thought that I could do an in() using a column name, but apparently not. Gotta get the values in there. I'll try to take a look at it, but complex mysql queries are a little out of my league...

Also, forgot to wrap the table name in {brackets}. I've updated that locally, but still have to work out the query.

Did you ever get this patch working? I could definitely use this feature.