跟踪用户愿望清单上产品价格下降的方法？
假设我有这些表/模型：
Product
- id
- last_updated_date
- name
- price
User
- id
- name
WishlistItem
- id
- user_id
- product_id
Product 表有几百万条记录，并且每天晚上通过数据导入自动更新（插入新表，删除旧表）。我基本上对该表/模型具有只读访问权限。
如果某个产品在用户的愿望清单上并且价格下降，我希望能够通知该用户。可以使用什么方法来做到这一点？
我有几个想法：
跟踪愿望清单模型中的 Product.last_updated_date 并定期轮询产品表以查看它是否已更新。这听起来像是一个可怕/不可扩展的解决方案。
某种 Postgres 视图或函数在产品表更新时触发？我是 postgres 新手，所以我还不确定这是否可能。
您会建议我没有想到的令人惊奇的事情:)
非常感谢任何在正确方向上的帮助！
更新： 出现的一个想法是将当前价格缓存在愿望清单条目本身内，即：
WishlistItem
- id
- user_id
- product_id
- price
...然后，在导入之后，我可以将 WishlistItem.price 与 Product.price 进行比较并相应地发出通知。虽然它会起作用，但这种方法似乎有点浪费，因为每个用户的每个 WishlistItem 基本上都具有相同的价格数据缓存副本，如果有更新，我将不得不使用新价格更新每个列表。但我不确定有没有办法解决这个问题。
更新： 最后，我决定我应该只跟踪数据的所有版本，以便我可以在更新触发器上使用标准。这也使我能够记录所有价格变化，以便我可以跟踪定价趋势等。
Let's say I have these tables/models:
Product
- id
- last_updated_date
- name
- price
User
- id
- name
WishlistItem
- id
- user_id
- product_id
The Product table has a few million records and is being updated automatically each night via a data import (inserting into a new table, dropping the old one). I basically have read-only access to that table/model.
If a product is on a user's wishlist and the price drops, I'd like to be able to notify that user. What methods can be used to do this?
I have a couple of ideas:
Keep track of the Product.last_updated_date in the wishlist model and periodically poll the product table to see if it has been updated. This sounds like a horrible/non-scaleable solution.
Some sort of Postgres View or Function that triggers when the Product table is updated? I'm new to postgres so I'm not yet sure if this is even possible.
Something amazing that you will suggest that I haven't thought of :)
Any help in the right direction is greatly appreciated!
UPDATE:
One idea that came up was caching the current price inside the wishlist entry itself, ie:
WishlistItem
- id
- user_id
- product_id
- price
... then, after the import, I could compare WishlistItem.price with Product.price and notify accordingly. Although it would work, this approach seems a bit wasteful since every WishlistItem for every user will essentially have the same cached copy of the price data and I'll have to update every list with the new price if there's an update. I'm not sure there's a way around it though.
UPDATE:
In the end I decided I should probably just be tracking all versions of the data so that I can use standard on update triggers. This also allows me to have a record of all price changes so I can track pricing trends, etc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以在
Product上使用更新触发器，但听起来Product表实际上并未更新，而是被替换；如果是这种情况，则没有更新可以触发触发器，因此您必须通过在Wishlist中缓存价格（如 user247245 注释）并扫描Product< /code> 价格变动。如果
产品正在更新而不是批量替换，则可以使用更新触发器来记录价格变化并安排通知感兴趣的各方。触发器可能会将通知插入到一个单独的表中（以避免锁定Products更新），例如Product_price_changes；然后，当Products更新完成后，一个单独的任务可以将Product_price_changes与Wishlist进行比较，通知感兴趣的用户，并通过删除所有内容来完成在Product_price_changes中。请注意，在 PostgreSQL 的最新版本中，您可以将更新触发器限制为仅在列更改时触发，而不是在每行更改时触发。
You could use an update trigger on
Productbut it sounds like theProducttable is not actually being updated, it is being replaced; if this is the case then there is no update to trigger the trigger so you'll have to do it the hard way by caching the price inWishlist(as user247245 notes) and scanningProductfor price changes.If
Productsis being updated rather than replaced wholesale, then an update trigger could be used to note when a price changes and arrange to have the interested parties notified. The trigger would probably queue up the notifications by inserting them into a separate table (to avoid locking up theProductsupdate) called, sayProduct_price_changes; then, when theProductsupdate is done, a separate task can compareProduct_price_changestoWishlist, notify the interested users, and finish off by deleting everything inProduct_price_changes.Note that recent versions of PostgreSQL you can restrict an update trigger to only fire when a column changes rather than firing on every row change.
我将在愿望清单表中添加一个“价格”字段。更新产品表查询较低价格并生成通知表作为结果后
，
/吨
I'd add a field "price" to the wishlist table. After update of product-table query for lower prices and generate a notification table as a result
regards,
/t