文章目录[隐藏]
实操指南：实现心愿单与收藏夹接口的3个贴心设计
引言：为什么需要精心设计收藏功能？
在当今的电商和内容平台中，收藏夹与心愿单功能已成为用户留存和转化的重要工具。据统计，拥有完善收藏功能的电商平台，用户复购率平均提升23%，页面停留时间增加35%。对于WordPress开发者而言，实现这些功能不仅是技术挑战，更是提升用户体验的关键机会。
本文将从WordPress开发者的角度，深入探讨如何实现既实用又贴心的收藏功能。我们将聚焦三个核心设计：智能分类与标签系统、跨设备同步与分享机制、以及个性化推荐与提醒功能。每个设计都将附有详细的代码实现和最佳实践建议。
设计一：智能分类与标签系统
1.1 为什么需要智能分类？
传统的收藏夹往往只是一个简单的列表，当用户收藏的项目达到一定数量时，查找特定内容变得异常困难。智能分类系统通过自动或半自动的方式帮助用户组织收藏内容，显著提升用户体验。
1.2 数据库设计
首先，我们需要设计合理的数据库结构。在WordPress中，我们可以利用现有的数据表结构，添加必要的自定义表：
-- 创建心愿单主表
CREATE TABLE wp_wishlist (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT(20) UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
is_default TINYINT(1) DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建心愿单项目表
CREATE TABLE wp_wishlist_items (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
wishlist_id BIGINT(20) UNSIGNED NOT NULL,
post_id BIGINT(20) UNSIGNED NOT NULL,
quantity INT(11) DEFAULT 1,
notes TEXT,
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY wishlist_id (wishlist_id),
KEY post_id (post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建标签表
CREATE TABLE wp_wishlist_tags (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
slug VARCHAR(100) NOT NULL,
user_id BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY slug_user (slug, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建项目-标签关联表
CREATE TABLE wp_wishlist_item_tags (
item_id BIGINT(20) UNSIGNED NOT NULL,
tag_id BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY (item_id, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;1.3 自动分类算法实现
我们可以基于内容分析和用户行为实现自动分类：
<?php
/**
* 智能分类器类
*/
class SmartWishlistClassifier {
/**
* 根据内容自动分配标签
*/
public function auto_tag_item($post_id, $user_id) {
$post = get_post($post_id);
$content = $post->post_title . ' ' . $post->post_content;
// 提取关键词
$keywords = $this->extract_keywords($content);
// 获取用户现有标签
$user_tags = $this->get_user_tags($user_id);
// 匹配最佳标签
$matched_tags = $this->match_tags($keywords, $user_tags);
// 如果没有匹配的标签，创建新标签
if (empty($matched_tags)) {
$primary_keyword = $this->get_primary_keyword($keywords);
$tag_id = $this->create_tag($primary_keyword, $user_id);
$matched_tags = [$tag_id];
}
return $matched_tags;
}
/**
* 从内容中提取关键词
*/
private function extract_keywords($content) {
// 移除HTML标签和特殊字符
$content = wp_strip_all_tags($content);
$content = preg_replace('/[^p{L}p{N}s]/u', '', $content);
// 分词处理（简化版，实际应使用更复杂的分词算法）
$words = explode(' ', strtolower($content));
// 移除停用词
$stop_words = $this->get_stop_words();
$words = array_diff($words, $stop_words);
// 统计词频
$word_freq = array_count_values($words);
// 按频率排序并返回前10个关键词
arsort($word_freq);
return array_slice(array_keys($word_freq), 0, 10);
}
/**
* 匹配现有标签
*/
private function match_tags($keywords, $user_tags) {
$matched = [];
foreach ($user_tags as $tag) {
$tag_name = strtolower($tag->name);
foreach ($keywords as $keyword) {
// 使用相似度算法（这里使用简单的字符串包含）
if (strpos($tag_name, $keyword) !== false ||
strpos($keyword, $tag_name) !== false) {
$matched[] = $tag->id;
break;
}
}
}
return array_unique($matched);
}
/**
* 获取停用词列表
*/
private function get_stop_words() {
return ['the', 'and', 'or', 'a', 'an', 'in', 'on', 'at', 'to', 'for', 'of', 'with'];
}
}1.4 用户界面实现
在前端，我们需要提供直观的标签管理界面：
<?php
/**
* 心愿单标签管理界面
*/
function render_wishlist_tags_ui($user_id, $item_id = null) {
$tags = get_user_wishlist_tags($user_id);
$item_tags = $item_id ? get_item_tags($item_id) : [];
ob_start();
?>
<div class="wishlist-tags-container">
<h4>管理标签</h4>
<!-- 现有标签 -->
<div class="existing-tags">
<?php foreach ($tags as $tag): ?>
<label class="tag-checkbox">
<input type="checkbox"
name="wishlist_tags[]"
value="<?php echo esc_attr($tag->id); ?>"
<?php echo in_array($tag->id, $item_tags) ? 'checked' : ''; ?>>
<span class="tag-label"><?php echo esc_html($tag->name); ?></span>
</label>
<?php endforeach; ?>
</div>
<!-- 添加新标签 -->
<div class="add-tag-form">
<input type="text"
class="new-tag-input"
placeholder="添加新标签...">
<button type="button" class="add-tag-btn">添加</button>
</div>
<!-- 智能建议 -->
<div class="tag-suggestions">
<p>智能建议：</p>
<div class="suggested-tags">
<!-- 通过AJAX加载建议标签 -->
</div>
</div>
</div>
<script>
jQuery(document).ready(function($) {
// 添加新标签
$('.add-tag-btn').click(function() {
var tagName = $('.new-tag-input').val().trim();
if (tagName) {
$.ajax({
url: ajaxurl,
type: 'POST',
data: {
action: 'add_wishlist_tag',
tag_name: tagName,
user_id: <?php echo $user_id; ?>,
nonce: '<?php echo wp_create_nonce("add_tag_nonce"); ?>'
},
success: function(response) {
if (response.success) {
location.reload();
}
}
});
}
});
// 标签自动完成
$('.new-tag-input').on('input', function() {
var query = $(this).val();
if (query.length > 2) {
$.get(ajaxurl, {
action: 'search_tags',
query: query,
user_id: <?php echo $user_id; ?>
}, function(suggestions) {
$('.suggested-tags').html(suggestions);
});
}
});
});
</script>
<?php
return ob_get_clean();
}设计二：跨设备同步与分享机制
2.1 同步架构设计
跨设备同步是现代应用的基本要求。我们需要设计一个可靠的数据同步机制：
<?php
/**
* 同步管理器类
*/
class WishlistSyncManager {
private $user_id;
private $last_sync_time;
public function __construct($user_id) {
$this->user_id = $user_id;
$this->last_sync_time = get_user_meta($user_id, 'wishlist_last_sync', true);
}
/**
* 获取需要同步的更改
*/
public function get_changes_since($timestamp) {
global $wpdb;
$changes = [
'added' => [],
'removed' => [],
'modified' => []
];
// 查询新增的项目
$added_items = $wpdb->get_results($wpdb->prepare(
"SELECT wi.*, w.name as wishlist_name
FROM {$wpdb->prefix}wishlist_items wi
JOIN {$wpdb->prefix}wishlist w ON wi.wishlist_id = w.id
WHERE w.user_id = %d AND wi.added_at > %s
ORDER BY wi.added_at DESC",
$this->user_id, $timestamp
));
// 查询修改的项目（包括标签变更）
$modified_items = $wpdb->get_results($wpdb->prepare(
"SELECT wi.*,
GROUP_CONCAT(wt.name) as tags,
w.name as wishlist_name
FROM {$wpdb->prefix}wishlist_items wi
JOIN {$wpdb->prefix}wishlist w ON wi.wishlist_id = w.id
LEFT JOIN {$wpdb->prefix}wishlist_item_tags wit ON wi.id = wit.item_id
LEFT JOIN {$wpdb->prefix}wishlist_tags wt ON wit.tag_id = wt.id
WHERE w.user_id = %d AND wi.updated_at > %s
GROUP BY wi.id",
$this->user_id, $timestamp
));
// 转换数据结构
foreach ($added_items as $item) {
$changes['added'][] = $this->format_item($item);
}
foreach ($modified_items as $item) {
$changes['modified'][] = $this->format_item($item);
}
return $changes;
}
/**
* 处理来自客户端的同步请求
*/
public function process_sync($client_changes, $device_id) {
$results = [
'server_changes' => $this->get_changes_since($client_changes['last_sync']),
'client_changes_processed' => []
];
// 处理客户端的新增项目
if (!empty($client_changes['added'])) {
foreach ($client_changes['added'] as $item) {
$item_id = $this->add_item_from_client($item, $device_id);
if ($item_id) {
$results['client_changes_processed']['added'][] = $item_id;
}
}
}
// 处理客户端的删除请求
if (!empty($client_changes['removed'])) {
foreach ($client_changes['removed'] as $item_id) {
if ($this->remove_item($item_id, $device_id)) {
$results['client_changes_processed']['removed'][] = $item_id;
}
}
}
// 更新同步时间
$this->update_sync_time();
return $results;
}
/**
* 生成分享链接
*/
public function generate_share_link($wishlist_id, $options = []) {
$defaults = [
'expires' => '+30 days',
'password' => '',
'allow_edit' => false
];
$options = wp_parse_args($options, $defaults);
// 生成唯一令牌
$token = wp_generate_password(32, false);
// 存储分享信息
$share_data = [
'wishlist_id' => $wishlist_id,
'created_by' => $this->user_id,
'expires' => strtotime($options['expires']),
'password' => $options['password'],
'allow_edit' => $options['allow_edit']
];
set_transient("wishlist_share_{$token}", $share_data, DAY_IN_SECONDS * 30);
// 返回分享链接
return add_query_arg(['share_token' => $token], home_url('/wishlist/shared/'));
}
}2.2 REST API 端点设计
为了实现跨平台同步，我们需要提供完善的REST API：
<?php
/**
* 注册心愿单REST API端点
*/
add_action('rest_api_init', function() {
// 获取用户心愿单
register_rest_route('wishlist/v1', '/wishlists', [
'methods' => 'GET',
'callback' => 'get_user_wishlists_api',
'permission_callback' => 'is_user_logged_in'
]);
// 同步数据
register_rest_route('wishlist/v1', '/sync', [
'methods' => 'POST',
'callback' => 'sync_wishlist_data',
'permission_callback' => 'is_user_logged_in'
]);
// 分享相关端点
register_rest_route('wishlist/v1', '/share', [
'methods' => 'POST',
'callback' => 'create_share_link',
'permission_callback' => 'is_user_logged_in'
]);
register_rest_route('wishlist/v1', '/shared/(?P<token>[a-zA-Z0-9]+)', [
'methods' => 'GET',
'callback' => 'get_shared_wishlist',
'permission_callback' => '__return_true'
]);
});
/**
* 同步API处理函数
*/
function sync_wishlist_data(WP_REST_Request $request) {
$user_id = get_current_user_id();
$sync_manager = new WishlistSyncManager($user_id);
$client_data = $request->get_json_params();
$device_id = sanitize_text_field($request->get_header('X-Device-ID'));
try {
$result = $sync_manager->process_sync($client_data, $device_id);
return new WP_REST_Response([
'success' => true,
'data' => $result,
'server_time' => current_time('mysql')
], 200);
} catch (Exception $e) {
return new WP_REST_Response([
'success' => false,
'message' => $e->getMessage()
], 500);
}
}2.3 离线支持与冲突解决
<?php
/**
* 离线支持管理器
*/
class OfflineSupportManager {
/**
* 为客户端生成离线数据包
*/
public function generate_offline_package($user_id) {
global $wpdb;
// 获取用户所有心愿单数据
$wishlists = $wpdb->get_results($wpdb->prepare(
"SELECT w.*,
COUNT(wi.id) as item_count
FROM {$wpdb->prefix}wishlist w
LEFT JOIN {$wpdb->prefix}wishlist_items wi ON w.id = wi.wishlist_id
WHERE w.user_id = %d
GROUP BY w.id",
$user_id
));
$package = [
'wishlists' => [],
'last_updated' => current_time('mysql'),
'version' => '1.0'
];
foreach ($wishlists as $wishlist) {
$items = $this->get_wishlist_items($wishlist->id);
$package['wishlists'][] = [
'id' => $wishlist->id,
'name' => $wishlist->name,
'items' => $items,
'is_default' => (bool)$wishlist->is_default
];
}
return $package;
}
/**
* 解决数据冲突
*/
public function resolve_conflict($local_item, $server_item) {
$resolution_strategy = get_user_meta(
get_current_user_id(),
'wishlist_sync_strategy',
true
) ?: 'server_wins';
switch ($resolution_strategy) {
case 'server_wins':
return $server_item;
case 'client_wins':
return $local_item;
case 'merge':
return $this->merge_items($local_item, $server_item);
case 'newest_wins':
$local_time = strtotime($local_item['updated_at']);
$server_time = strtotime($server_item['updated_at']);
return $local_time > $server_time ? $local_item : $server_item;
}
}
/**
* 合并项目数据
*/
private function merge_items($item1, $item2) {
$merged = array_merge($item1, $item2);
// 合并标签（去重）
if (!empty($item1['tags']) && !empty($item2['tags'])) { $merged['tags'] = array_unique(
array_merge(
(array)$item1['tags'],
(array)$item2['tags']
)
);
}
// 合并备注
if (!empty($item1['notes']) && !empty($item2['notes'])) {
if ($item1['notes'] !== $item2['notes']) {
$merged['notes'] = $item1['notes'] . "n---n" . $item2['notes'];
}
}
return $merged;
}
}设计三：个性化推荐与智能提醒
3.1 推荐引擎设计
基于用户收藏行为提供个性化推荐：
<?php
/**
* 心愿单推荐引擎
*/
class WishlistRecommendationEngine {
private $user_id;
private $min_similarity_score = 0.3;
public function __construct($user_id) {
$this->user_id = $user_id;
}
/**
* 获取个性化推荐
*/
public function get_recommendations($limit = 10, $source = 'mixed') {
$recommendations = [];
// 基于用户收藏内容的协同过滤
if ($source === 'collaborative' || $source === 'mixed') {
$collaborative_recs = $this->get_collaborative_recommendations($limit);
$recommendations = array_merge($recommendations, $collaborative_recs);
}
// 基于内容相似度的推荐
if ($source === 'content' || $source === 'mixed') {
$content_recs = $this->get_content_based_recommendations($limit);
$recommendations = array_merge($recommendations, $content_recs);
}
// 基于标签的推荐
if ($source === 'tags' || $source === 'mixed') {
$tag_recs = $this->get_tag_based_recommendations($limit);
$recommendations = array_merge($recommendations, $tag_recs);
}
// 去重、排序并限制数量
$recommendations = $this->deduplicate_and_sort($recommendations, $limit);
return $recommendations;
}
/**
* 协同过滤推荐
*/
private function get_collaborative_recommendations($limit) {
global $wpdb;
// 获取与当前用户收藏相似的其他用户
$similar_users = $wpdb->get_results($wpdb->prepare(
"SELECT other.user_id,
COUNT(DISTINCT other.post_id) as common_items,
COUNT(DISTINCT other.user_id) as total_items
FROM {$wpdb->prefix}wishlist_items current
INNER JOIN {$wpdb->prefix}wishlist_items other
ON current.post_id = other.post_id
INNER JOIN {$wpdb->prefix}wishlist w1 ON current.wishlist_id = w1.id
INNER JOIN {$wpdb->prefix}wishlist w2 ON other.wishlist_id = w2.id
WHERE w1.user_id = %d
AND w2.user_id != %d
AND current.post_id IS NOT NULL
GROUP BY other.user_id
HAVING common_items >= 2
ORDER BY common_items DESC
LIMIT 20",
$this->user_id, $this->user_id
));
$recommendations = [];
foreach ($similar_users as $similar_user) {
// 计算相似度分数
$similarity_score = $this->calculate_similarity_score($similar_user);
if ($similarity_score >= $this->min_similarity_score) {
// 获取相似用户收藏但当前用户未收藏的内容
$user_recs = $wpdb->get_results($wpdb->prepare(
"SELECT DISTINCT p.ID, p.post_title,
'collaborative' as source_type,
%f as score
FROM {$wpdb->prefix}posts p
INNER JOIN {$wpdb->prefix}wishlist_items wi ON p.ID = wi.post_id
INNER JOIN {$wpdb->prefix}wishlist w ON wi.wishlist_id = w.id
WHERE w.user_id = %d
AND p.post_status = 'publish'
AND p.ID NOT IN (
SELECT wi2.post_id
FROM {$wpdb->prefix}wishlist_items wi2
INNER JOIN {$wpdb->prefix}wishlist w2 ON wi2.wishlist_id = w2.id
WHERE w2.user_id = %d
)
ORDER BY wi.added_at DESC
LIMIT 5",
$similarity_score,
$similar_user->user_id,
$this->user_id
));
$recommendations = array_merge($recommendations, $user_recs);
}
}
return $recommendations;
}
/**
* 基于内容的推荐
*/
private function get_content_based_recommendations($limit) {
global $wpdb;
// 获取用户最近收藏的内容
$user_items = $wpdb->get_results($wpdb->prepare(
"SELECT p.ID, p.post_content, p.post_title
FROM {$wpdb->prefix}posts p
INNER JOIN {$wpdb->prefix}wishlist_items wi ON p.ID = wi.post_id
INNER JOIN {$wpdb->prefix}wishlist w ON wi.wishlist_id = w.id
WHERE w.user_id = %d
AND p.post_status = 'publish'
ORDER BY wi.added_at DESC
LIMIT 20",
$this->user_id
));
if (empty($user_items)) {
return [];
}
// 提取用户兴趣关键词
$user_keywords = $this->extract_user_keywords($user_items);
// 基于关键词查找相似内容
$recommendations = $wpdb->get_results($wpdb->prepare(
"SELECT p.ID, p.post_title,
MATCH(p.post_title, p.post_content)
AGAINST (%s IN NATURAL LANGUAGE MODE) as relevance,
'content' as source_type,
(MATCH(p.post_title, p.post_content)
AGAINST (%s IN NATURAL LANGUAGE MODE)) as score
FROM {$wpdb->prefix}posts p
WHERE p.post_status = 'publish'
AND p.ID NOT IN (
SELECT wi.post_id
FROM {$wpdb->prefix}wishlist_items wi
INNER JOIN {$wpdb->prefix}wishlist w ON wi.wishlist_id = w.id
WHERE w.user_id = %d
)
AND MATCH(p.post_title, p.post_content)
AGAINST (%s IN NATURAL LANGUAGE MODE) > 0
ORDER BY relevance DESC
LIMIT %d",
implode(' ', $user_keywords),
implode(' ', $user_keywords),
$this->user_id,
implode(' ', $user_keywords),
$limit * 2
));
return $recommendations;
}
/**
* 基于标签的推荐
*/
private function get_tag_based_recommendations($limit) {
global $wpdb;
// 获取用户常用标签
$user_tags = $wpdb->get_results($wpdb->prepare(
"SELECT wt.id, wt.name, COUNT(wit.item_id) as usage_count
FROM {$wpdb->prefix}wishlist_tags wt
INNER JOIN {$wpdb->prefix}wishlist_item_tags wit ON wt.id = wit.tag_id
INNER JOIN {$wpdb->prefix}wishlist_items wi ON wit.item_id = wi.id
INNER JOIN {$wpdb->prefix}wishlist w ON wi.wishlist_id = w.id
WHERE w.user_id = %d
GROUP BY wt.id
ORDER BY usage_count DESC
LIMIT 10",
$this->user_id
));
if (empty($user_tags)) {
return [];
}
$tag_ids = wp_list_pluck($user_tags, 'id');
$tag_ids_placeholder = implode(',', array_fill(0, count($tag_ids), '%d'));
// 查找具有相同标签的内容
$recommendations = $wpdb->get_results($wpdb->prepare(
"SELECT p.ID, p.post_title,
COUNT(DISTINCT wt.id) as common_tags,
'tags' as source_type,
(COUNT(DISTINCT wt.id) / %d) as score
FROM {$wpdb->prefix}posts p
INNER JOIN {$wpdb->prefix}term_relationships tr ON p.ID = tr.object_id
INNER JOIN {$wpdb->prefix}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN {$wpdb->prefix}terms t ON tt.term_id = t.term_id
INNER JOIN {$wpdb->prefix}wishlist_tags wt ON t.name = wt.name
WHERE p.post_status = 'publish'
AND tt.taxonomy IN ('post_tag', 'category')
AND wt.id IN ($tag_ids_placeholder)
AND p.ID NOT IN (
SELECT wi.post_id
FROM {$wpdb->prefix}wishlist_items wi
INNER JOIN {$wpdb->prefix}wishlist w ON wi.wishlist_id = w.id
WHERE w.user_id = %d
)
GROUP BY p.ID
HAVING common_tags > 0
ORDER BY common_tags DESC
LIMIT %d",
count($tag_ids),
...array_merge($tag_ids, [$this->user_id, $limit * 2])
));
return $recommendations;
}
/**
* 计算用户相似度分数
*/
private function calculate_similarity_score($similar_user) {
// 使用Jaccard相似度系数
$common_items = $similar_user->common_items;
$total_current_items = $this->get_user_item_count();
$total_other_items = $similar_user->total_items;
if ($total_current_items + $total_other_items - $common_items == 0) {
return 0;
}
return $common_items / ($total_current_items + $total_other_items - $common_items);
}
/**
* 提取用户关键词
*/
private function extract_user_keywords($items, $max_keywords = 15) {
$all_content = '';
foreach ($items as $item) {
$all_content .= ' ' . $item->post_title . ' ' . wp_strip_all_tags($item->post_content);
}
// 简单的关键词提取（实际项目中可以使用更复杂的NLP处理）
$words = str_word_count(strtolower($all_content), 1);
$stop_words = ['the', 'and', 'or', 'a', 'an', 'in', 'on', 'at', 'to', 'for'];
$words = array_diff($words, $stop_words);
$word_freq = array_count_values($words);
arsort($word_freq);
return array_slice(array_keys($word_freq), 0, $max_keywords);
}
/**
* 去重和排序推荐结果
*/
private function deduplicate_and_sort($recommendations, $limit) {
$unique_recommendations = [];
foreach ($recommendations as $rec) {
$post_id = $rec->ID;
if (!isset($unique_recommendations[$post_id])) {
$unique_recommendations[$post_id] = $rec;
} else {
// 如果已存在，保留分数较高的
if ($rec->score > $unique_recommendations[$post_id]->score) {
$unique_recommendations[$post_id] = $rec;
}
}
}
// 按分数排序
usort($unique_recommendations, function($a, $b) {
return $b->score <=> $a->score;
});
return array_slice($unique_recommendations, 0, $limit);
}
}3.2 智能提醒系统
<?php
/**
* 智能提醒系统
*/
class WishlistNotificationSystem {
/**
* 检查并发送相关提醒
*/
public function check_and_send_notifications() {
$this->check_price_drops();
$this->check_back_in_stock();
$this->check_abandoned_wishlist();
$this->check_recommendation_updates();
}
/**
* 价格下降提醒
*/
private function check_price_drops() {
global $wpdb;
// 获取设置了价格提醒的商品
$price_alerts = $wpdb->get_results(
"SELECT wi.id, wi.post_id, wi.user_id,
pa.original_price, pa.desired_price,
u.user_email, p.post_title
FROM {$wpdb->prefix}wishlist_items wi
INNER JOIN {$wpdb->prefix}price_alerts pa ON wi.id = pa.item_id
INNER JOIN {$wpdb->prefix}users u ON wi.user_id = u.ID
INNER JOIN {$wpdb->prefix}posts p ON wi.post_id = p.ID
WHERE pa.is_active = 1
AND pa.last_checked < DATE_SUB(NOW(), INTERVAL 1 HOUR)"
);
foreach ($price_alerts as $alert) {
$current_price = $this->get_current_price($alert->post_id);
if ($current_price && $current_price <= $alert->desired_price) {
$this->send_price_drop_notification($alert, $current_price);
// 更新最后检查时间
$wpdb->update(
"{$wpdb->prefix}price_alerts",
['last_checked' => current_time('mysql')],
['item_id' => $alert->id]
);
}
}
}
/**
* 库存恢复提醒
*/
private function check_back_in_stock() {
global $wpdb;
$out_of_stock_items = $wpdb->get_results(
"SELECT wi.id, wi.post_id, wi.user_id,
u.user_email, p.post_title
FROM {$wpdb->prefix}wishlist_items wi
INNER JOIN {$wpdb->prefix}stock_alerts sa ON wi.id = sa.item_id
INNER JOIN {$wpdb->prefix}users u ON wi.user_id = u.ID
INNER JOIN {$wpdb->prefix}posts p ON wi.post_id = p.ID
WHERE sa.is_active = 1
AND sa.last_checked < DATE_SUB(NOW(), INTERVAL 30 MINUTE)"
);
foreach ($item as $out_of_stock_items) {
$is_in_stock = $this->check_stock_status($item->post_id);
if ($is_in_stock) {
$this->send_back_in_stock_notification($item);
// 停用该提醒
$wpdb->update(
"{$wpdb->prefix}stock_alerts",
['is_active' => 0],
['item_id' => $item->id]
);
}
// 更新最后检查时间
$wpdb->update(
"{$wpdb->prefix}stock_alerts",
['last_checked' => current_time('mysql')],
['item_id' => $item->id]
);
}
}
/**
* 心愿单放弃提醒
*/
private function check_abandoned_wishlist() {
global $wpdb;
// 查找30天未访问但有活跃心愿单的用户
$abandoned_users = $wpdb->get_results(
"SELECT u.ID, u.user_email,
COUNT(wi.id) as item_count,
MAX(wi.added_at) as last_added
FROM {$wpdb->prefix}users u
INNER JOIN {$wpdb->prefix}wishlist w ON u.ID = w.user_id
INNER JOIN {$wpdb->prefix}wishlist_items wi ON w.id = wi.wishlist_id
WHERE u.last_activity < DATE_SUB(NOW(), INTERVAL 30 DAY)
AND w.is_default = 1
GROUP BY u.ID
HAVING item_count > 0"
);
foreach ($user as $abandoned_users) {
$this->send_abandoned_wishlist_notification($user);
}
}
/**
* 发送价格下降通知
*/
private function send_price_drop_notification($alert, $current_price) {
$subject = sprintf('价格提醒：%s 已降价', $alert->post_title);
$message = sprintf(
"您好！nn您关注的商品《%s》价格已下降！nn" .
"原价：¥%.2fn" .
"当前价：¥%.2fn" .
"目标价：¥%.2fnn" .
"立即查看：%snn" .
"祝您购物愉快！",
$alert->post_title,
$alert->original_price,
$current_price,
$alert->desired_price,
get_permalink($alert->post_id)
);
wp_mail($alert->user_email, $subject, $message);
}
/**
* 获取商品当前价格
*/
private function get_current_price($post_id) {
// 这里需要根据实际的价格获取逻辑来实现
// 如果是WooCommerce商品
if (function_exists('wc_get_product')) {
$product = wc_get_product($post_id);
if ($product) {
return $product->get_price();
}
}
// 自定义价格字段
$price = get_post_meta($post_id, '_price', true);
if ($price) {
return floatval($price);
}
