猜你喜欢按百分比推荐

时间：2021-03-08 16:35:16

要求，每页数据有4部门组成(系统推荐之最新商品10%，系统推荐之最热标签10%，用户偏好标签关联的商品60%，用户最新标签10%)，其他数据没有时用《系统推荐之最新商品》补齐。

思路一：

建表：按每个用户生成商品排序表，计划任务(实时性差些)或触发更新排序数据(商品多执行时间长)

思路二:

将数据拆成4部分分别查询，最终用《系统推荐之最新商品》补齐分页数据,遇到问题某一个或多个数据刚开始有数据，页数增加后会无数据情况，造成分页乱的问题。

最终决定用思路2尝试

分页动态更新

select goods_attr_id from xxxx limit 2,5;
select goods_attr_id from xxxx limit 2,7; # 2 7
select goods_attr_id from xxxx limit 9,8; # 3 (9+8)
select goods_attr_id from xxxx limit 17,8;# 4 (17+8)
select goods_attr_id from xxxx limit 25,8;# 5 (25+3)
// 如果limit有值则下页的页数就是上次的页数+条数

最终代码

$cacheKey = 'now_page_' . $this->userId;
            if ($p <= 0) {
                // 第一页清空之前的配置
                F($cacheKey, '');
            }
            $limitHot = floor($limit * 0.6);
            $limitNew = floor($limit * 0.2);
            $limitSysHot = floor($limit * 0.1);
            $limitSys = $limit - $limitHot - $limitNew - $limitSysHot;
//            echo $limitHot.'-'.$limitNew.'-'.$limitSysHot.'-'.$limitSys;
            $goodsRes = [];
            $strWere = 'mg.is_check = 1 and mg.is_on_sale = 1';
            if ($this->userId) {
                // 用户偏好标签
                // 取用户前10个赋值最多的标签作为用户的偏好标签进行产品的推荐
                $hotLabelIds = M('xxxx')->where(['user_id' => $this->userId, 'type' => 1, 'status' => 1])->field('label_id')->distinct(true)->limit(10)->order('num desc')->select();
                if ($hotLabelIds) {
                    $strHotLabelids = implode(',', array_column($hotLabelIds, 'label_id'));
                    $sqlhotCount = "select mg.id from sline_label_goods la left join xxxx mg on la.goods_id=mg.id where {$strWere} and la.label_id in ($strHotLabelids)";
                    if ($arrHotCount = M()->query($sqlhotCount)) {
                        $sqlhot = "select mg.id,mg.shop_id,mg.goods_name,mg.goods_img from xxxx la left join xxxx mg on la.goods_id=mg.id where {$strWere} and la.label_id in ($strHotLabelids) order by field(la.label_id,$strHotLabelids) limit $limitHot offset $p";
                        $goodsRes = M()->query($sqlhot);
                        $strWere .= " and mg.id not in (" . implode(',', array_column($arrHotCount, 'id')) . ")";
                    }
                }
                // 用户最新标签
                $newLabelIds = M('xxxx')->where(['user_id' => $this->userId, 'type' => 1, 'status' => 1])->field('label_id')->distinct(true)->limit(1)->order('create_time desc')->select();
                if ($newLabelIds) {
                    $strNewLabelIds = implode(',', array_column($newLabelIds, 'label_id'));
                    $sqlNewCount = "select mg.id from xxxx la left join xxxx mg on la.goods_id=mg.id where {$strWere} and la.label_id in ($strNewLabelIds)";
                    if ($arrNewCount = M()->query($sqlNewCount)) {
                        $sqlnew = "select mg.id,mg.shop_id,mg.goods_name,mg.goods_img from xxxx la left join xxxx mg on la.goods_id=mg.id where {$strWere} and la.label_id in ($strNewLabelIds) order by field(la.label_id,$strNewLabelIds) limit $limitHot offset $p";
                        $goodsNewRes = M()->query($sqlnew);
                        if ($goodsNewRes) {
                            $strWere .= " and mg.id not in (" . implode(',', array_column($arrNewCount, 'id')) . ")";
                            $goodsRes = array_merge($goodsRes, $goodsNewRes);
                        }
                    }
                }
            }
 
            // 系统推荐
            // 系统推荐 之 拥有热门标签最多的产品
            $sysHotLabelIds = M('xxxx')->where(['type' => 1, 'status' => 1])->field('label_id')->distinct(true)->limit(1)->order('create_time desc')->select();
            if (!empty($sysHotLabelIds)) {
                $strsysHotLabelIds = implode(',', array_column($sysHotLabelIds, 'label_id'));
                $sqlsysHotCount = "select mg.id from xxxx la left join xxxx mg on la.goods_id=mg.id where {$strWere} and la.label_id in ($strsysHotLabelIds)";
                if ($arrsysHotCount = M()->query($sqlsysHotCount)) {
                    $sqlnew = "select mg.id,mg.shop_id,mg.goods_name,mg.goods_img from xxxx la left join xxxx mg on la.goods_id=mg.id where {$strWere} and la.label_id in ($strsysHotLabelIds) order by field(la.label_id,$strsysHotLabelIds) limit $limitSysHot offset $p";
                    $goodsNewRes = M()->query($sqlnew);
                    if ($goodsNewRes) {
                        $strWere .= " and mg.id not in (" . implode(',', array_column($arrsysHotCount, 'id')) . ")";
                        $goodsRes = array_merge($goodsRes, $goodsNewRes);
                    }
                }
            }
            if ($cachePage = F($cacheKey)) {
                $p = $cachePage['p'] + $cachePage['l'];
            }
            if (count($goodsRes) != ($limitHot + $limitNew + $limitSysHot)) {
                $limitSys = $limit - count($goodsRes);
                F($cacheKey, ['p' => $p, 'l' => $limitSys]);
            }
            // 系统推荐 之 冷启动且补齐分页条数
            $sqlSys = "SELECT mg.id,mg.shop_id,mg.goods_name,mg.goods_img FROM xxxx mg where {$strWere} ORDER BY mg.id desc  limit $limitSys offset $p";
            $goodsSys = M('')->query($sqlSys);
            $goodsRes = array_merge($goodsRes, $goodsSys);
            // 最终的数据
            print_r($goodsRes);