java 查询 solr ftl,B2B2B2C 电商系统过滤solr搜索结果

最新推荐文章于 2023-08-23 14:42:36 发布

张梓染Ashley

最新推荐文章于 2023-08-23 14:42:36 发布

阅读量86

点赞数

文章标签： java 查询 solr ftl

商品池怎么定义的？

自有商品SKU库的表： shop_goods

alter table shop_goods modify column `goods_pools` varchar(255) DEFAULT '';

这个很重要，保证 goods_pools 字段不为null

自有商品分类就是表： shop_goods_class_custom

自定义的商品库的表： bus_goods_pool

商品池定义: bus_goods_pool

DROP TABLE IF EXISTS bus_goods_pool;

CREATE TABLE bus_goods_pool (

pool_id varchar(50) NOT NULL,

pool_name varchar(50) DEFAULT NULL,

effective_time TIMESTAMP null default now() COMMENT '有效期起始时间',

failure_time TIMESTAMP null default now() COMMENT '有效期结束时间',

create_time TIMESTAMP null default now() COMMENT '创建时间',

update_time TIMESTAMP null default now() on update now() COMMENT '更新时间',

operator_id varchar(50) DEFAULT NULL COMMENT '操作人id',

operator varchar(50) DEFAULT NULL COMMENT '操作人',

pool_type int(10) DEFAULT 1 NULL COMMENT '商品池类型',

pool_status int(10) DEFAULT 0 NULL comment '状态：0 未启用 1 启用 2 失效',

PRIMARY KEY (pool_id),

key pool_name (pool_name) using btree,

key effective_failure_time (effective_time, failure_time) using btree

) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '商品池定义';

insert into bus_goods_pool (pool_id, pool_name, effective_time, failure_time, update_time, pool_type, pool_status) values

((select replace(uuid(), '-', '')), '狗东商品池1', NOW(), DATE_ADD(NOW(),INTERVAL 100 day), NOW(), 1, 1),

((select replace(uuid(), '-', '')), '狗东商品池2', NOW(), DATE_ADD(NOW(),INTERVAL 101 day), NOW(), 1, 1),

((select replace(uuid(), '-', '')), '二手东商品池1', NOW(), DATE_ADD(NOW(),INTERVAL 102 day), NOW(), 1, 1),

((select replace(uuid(), '-', '')), '二手东商品池2', NOW(), DATE_ADD(NOW(),INTERVAL 103 day), NOW(), 1, 1),

((select replace(uuid(), '-', '')), '网易严选商品池1', NOW(), DATE_ADD(NOW(),INTERVAL 104 day), NOW(), 1, 1),

((select replace(uuid(), '-', '')), '网易严选商品池2', NOW(), DATE_ADD(NOW(),INTERVAL 104 day), NOW(), 1, 1);

生成的数据

select * from bus_goods_pool;

f536fc9151a011e8b1b5005056bf65f0 狗东商品池1 2018-05-07 10:47:18 2018-08-15 10:47:18 2018-05-07 10:47:18 1 1 2018-05-07 20:38:15

f536ff8a51a011e8b1b5005056bf65f0 狗东商品池2 2018-05-07 10:47:18 2018-08-16 10:47:18 2018-05-07 10:47:18 1 1 2018-05-07 20:38:15

f537014951a011e8b1b5005056bf65f0 二手东商品池1 2018-05-07 10:47:18 2018-08-17 10:47:18 2018-05-07 10:47:18 1 1 2018-05-07 20:38:15

f537029551a011e8b1b5005056bf65f0 二手东商品池2 2018-05-07 10:47:18 2018-08-18 10:47:18 2018-05-07 10:47:18 1 1 2018-05-07 20:38:15

f53703d451a011e8b1b5005056bf65f0 网易严选商品池1 2018-05-07 10:47:18 2018-08-19 10:47:18 2018-05-07 10:47:18 1 1 2018-05-07 20:38:15

f537054751a011e8b1b5005056bf65f0 网易严选商品池2 2018-05-07 10:47:18 2018-08-19 10:47:18 2018-05-07 10:47:18 1 1 2018-05-07 20:38:15

企业和自定义商品库的映射关系： bus_enterprise_goodspool

DROP TABLE IF EXISTS bus_enterprise_goodspool;

CREATE TABLE bus_enterprise_goodspool (

id varchar(50) not null,

enterprise_id varchar(50) NOT NULL comment '企业id',

enterprise_no varchar(50) NOT NULL comment '企业ep_no',

pool_id varchar(50) NOT NULL comment '商品池id',

-- effective_time datetime null default now() COMMENT '有效期起始时间',

-- failure_time datetime null default now() comment '有效期结束时间',

priority int(10) DEFAULT 1 null comment '优先级',

discount double(10, 2) default 1.0 comment '折扣',

create_time TIMESTAMP null default now() COMMENT '创建时间'

primary key (id),

key enterprise_id(enterprise_id) using btree,

key pool_id(pool_id) using btree

-- key effective_failure_time(effective_time, failure_time) using btree

) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='企业商品池关联表';

insert into bus_enterprise_goodspool (id, enterprise_id, enterprise_no, pool_id, priority, discount) values

((select replace(uuid(), '-', '')), '93fcf2a17a9b4e82af36891726a8e448', '11', 'f536fc9151a011e8b1b5005056bf65f0', 1, 1.0),

((select replace(uuid(), '-', '')), '93fcf2a17a9b4e82af36891726a8e448', '11', 'f536ff8a51a011e8b1b5005056bf65f0', 2, 1.0),

((select replace(uuid(), '-', '')), '93fcf2a17a9b4e82af36891726a8e448', '11', 'f537014951a011e8b1b5005056bf65f0', 3, 1.0),

((select replace(uuid(), '-', '')), '33a6aeed50f011e8b1b5005056bf65f0', 'NO002', 'f537029551a011e8b1b5005056bf65f0', 1, 1.0),

((select replace(uuid(), '-', '')), '33a6aeed50f011e8b1b5005056bf65f0', 'NO002', 'f53703d451a011e8b1b5005056bf65f0', 2, 1.0),

((select replace(uuid(), '-', '')), '33a6aeed50f011e8b1b5005056bf65f0', 'NO002', 'f537054751a011e8b1b5005056bf65f0', 3, 1.0);

生成的数据

select * from bus_enterprise_goodspool;

ab13591851a411e8b1b5005056bf65f0 93fcf2a17a9b4e82af36891726a8e448 11 f536fc9151a011e8b1b5005056bf65f0 1 1 2018-05-07 20:38:33

ab135c0251a411e8b1b5005056bf65f0 93fcf2a17a9b4e82af36891726a8e448 11 f536ff8a51a011e8b1b5005056bf65f0 2 1 2018-05-07 20:38:33

ab135d7851a411e8b1b5005056bf65f0 93fcf2a17a9b4e82af36891726a8e448 11 f537014951a011e8b1b5005056bf65f0 3 1 2018-05-07 20:38:33

ab135e8651a411e8b1b5005056bf65f0 33a6aeed50f011e8b1b5005056bf65f0 NO002 f537029551a011e8b1b5005056bf65f0 1 1 2018-05-07 20:38:33

ab135faf51a411e8b1b5005056bf65f0 33a6aeed50f011e8b1b5005056bf65f0 NO002 f53703d451a011e8b1b5005056bf65f0 2 1 2018-05-07 20:38:33

ab1360e751a411e8b1b5005056bf65f0 33a6aeed50f011e8b1b5005056bf65f0 NO002 f537054751a011e8b1b5005056bf65f0 3 1 2018-05-07 20:38:33

商品池、商品映射关系表: bus_rel_goods_pool

DROP TABLE IF EXISTS bus_rel_goods_pool;

CREATE TABLE bus_rel_goods_pool (

id varchar(50) NOT NULL ,

pool_id varchar(50) DEFAULT NULL comment '商品池id',

goods_id varchar(50) DEFAULT NULL comment '商品id',

goods_price decimal(10,2) DEFAULT NULL comment '商品池对应的价格',

partner_id varchar(50) DEFAULT NULL,

status int(10) DEFAULT NULL comment '状态',

store_flag int(10) DEFAULT NULL,

PRIMARY KEY (id),

key pool_id(pool_id) using BTREE,

key goods_id (goods_id) using BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC comment='商品池商品关联表';

-- 生成商品池、商品关联数据

SELECT

concat('((select replace(uuid(), ''-'', ''''))', ', ''f537054751a011e8b1b5005056bf65f0'', ''', g.goods_id, ''', ', goods_store_price, '),')

FROM shop_goods g

ORDER BY RAND()

LIMIT 500;

商品池怎么生成？

通过企业id，去 bus_enterprise_goodspool 表查询其对应的商品池，然后通过商品池的id，去 goods_pool_goods 查询对应商品的总和，根据优先级排序、去重。获得所有的商品。然后再根据商品的 goods_id 去 shop_goods 表里查询商品的状态，并根据折扣 * 价格，生成价格。

select

goodspool.priority, pg.goods_price as pool_price, distinct(g.goods_id), g.*

from bus_enterprise en

left join bus_enterprise_goodspool goodspool on en.id = goodspool.enterprise_id

left join bus_goods_pool pool on goodspool.pool_id = pool.pool_id and pool.pool_status = 1

left join bus_rel_goods_pool pg on goodspool.pool_id = pg.pool_id

left join shop_goods g on pg.goods_id = g.goods_id

where en.id = '93fcf2a17a9b4e82af36891726a8e448'

and pool.pool_status = 1

and pool.effective_time < now() and pool.failure_time < DATE_ADD(NOW(),INTERVAL 100 day)

这个sql返回的是所有商品池里的商品的总和。需要按优先级去重

或者可以直接在sql里去重，执行时间差不多

select x.*, gp.goods_price as pool_price , g.*

from

(select

max(goodspool.priority) as priority, g.goods_id

from bus_enterprise en

left join bus_enterprise_goodspool goodspool on en.id = goodspool.enterprise_id

left join bus_goods_pool pool on goodspool.pool_id = pool.pool_id and pool.pool_status = 1

left join bus_rel_goods_pool pg on goodspool.pool_id = pg.pool_id

left join shop_goods g on pg.goods_id = g.goods_id

where en.id = '93fcf2a17a9b4e82af36891726a8e448'

and pool.pool_status = 1

and pool.effective_time < now() and pool.failure_time < DATE_ADD(NOW(),INTERVAL 104 day)

group by g.goods_id

-- order by g.goods_id

) x, bus_rel_goods_pool gp, bus_enterprise_goodspool ep

, shop_goods g

where x.goods_id = gp.goods_id

and gp.pool_id = ep.pool_id

and x.priority = ep.priority

and ep.enterprise_id = '93fcf2a17a9b4e82af36891726a8e448'

and x.goods_id = g.goods_id

order by g.recommend

搜索结果

在登陆的时候，通过获得企业id，通过企业id，去 bus_enterprise_goodspool 表查询该企业有哪些商品池，把这个数据存到redis里面。

应该存入redis，而不是session。session 是每个用户存一份，redis 是所有用户共享的。

给 LucenePager 这个类加一个 goodsPool 字段

调用 search/goodsSearch?searchType=gcIdSearch&keyword=831 这样的接口的时候，从 redis 读该企业都有哪些商品池的编号，把这个编号数据赋给 LucenePager 的 goodsPool 字段

修改 \solr\serviceImpl\SolrServiceImpl.java 的 searchGoodsIndex 方法，把goodsPool 这个查询条件，加到solr的 queryString 里。

搜索出结果之后，价格的处理，要依赖于商品池。遍历LucenePager，去商品池查对应的价格。需要要一个独立的任务维护商品池的数据更新。

搜索的展示层是：

\zzwelfare\welfare-front\src\main\webapp\WEB-INF\views_v4.0\search\goods-search.ftl

是遍历 lucencePager 这个结果集。

假如，想不改展示层的话，那么，从用商品池里取出的结果，替换从solr搜索出来的结果。

\welfare-solr\src\main\java\com\xxyouxx\extend\module\solr\serviceImpl\SolrServiceImpl.java 增加了yige过滤条件，商品池id：

if(StringUtils.isNotEmpty(lucenePager.getPoolIds())){

query.addFilterQuery("goodsPools:" + lucenePager.getPoolIds());

query.set("q", "*:*");

}

\welfare-service\src\main\java\com\xxyouxx\service\module\search\service\impl\GoodsSearchServiceImpl.java 增加了获取当前企业的商品池id列表

String enterpriseId = null;

// 根据当前企业的商品池，修改搜索结果的价格

String poolIds = null;

Subject subject = SecurityUtils.getSubject();

Session session = subject.getSession();

// 获取企业的id，通过企业的id，获取企业的商品池

BusEnterprise busEnterprise = (BusEnterprise)session.getAttribute(SysConstants.SESSION_LOGIN_USER_ENTERPRISE);

if(null != busEnterprise) {

enterpriseId = busEnterprise.getId();

if(null != enterpriseId && !"".equals(enterpriseId)) {

if(JedisConfig.JEDIS_STATUS) {

//需要安装redis

Object obj = JedisUtils.getObject(JedisConfig.GOODS_POOL + enterpriseId);

if(obj == null){

// 先获取商品池id

poolIds = busEnterpriseGoodsPoolService.getAllPoolIds(enterpriseId);

//1小时，甚至可以更长，因为这基本是配置好了，就不会再变动的数据了。

JedisUtils.setObject(JedisConfig.GOODS_POOL + enterpriseId, poolIds, 60 * 60);

} else {

poolIds = (String)obj;

}

} else {

poolIds = busEnterpriseGoodsPoolService.getAllPoolIds(enterpriseId);

}

// 根据当前企业的商品池，修改搜索结果的价格

if(org.apache.commons.lang3.StringUtils.isNotBlank(poolIds)) {

lucenePager.setPoolIds(poolIds);

}

和根据商品池里的价格，更新solr搜索结果价格的代码

// 用商品池里的价格，来更新商品的价格

// 根据当前企业的商品池，修改搜索结果的价格

if(org.apache.commons.lang3.StringUtils.isNotBlank(poolIds)) {

// 商品池里的全部商品

List poolGoodsList = null;

PoolGoodsVo vo = new PoolGoodsVo();

vo.setEnterpriseId(enterpriseId);

// 确保搜索出结果，再去处理价格

if(lucenePager.getResult().size() > 0 ) {

if(JedisConfig.JEDIS_STATUS) {

//需要安装redis

Object obj = JedisUtils.getObject(JedisConfig.ENTERPRISE_POOL_GOODS + enterpriseId);

if(null == obj || ((List)obj).size() == 0) {

poolGoodsList = poolGoodsService.findPoolGoods(vo);

//然后把这个商品池放到缓存里，而不是修改价格之后的

//1小时，甚至可以更长，因为这基本是配置好了，就不会再变动的数据了。

JedisUtils.setObject(JedisConfig.ENTERPRISE_POOL_GOODS + enterpriseId, poolGoodsList, 60 * 60);

} else {

poolGoodsList = (List)obj;

}

} else {

poolGoodsList = poolGoodsService.findPoolGoods(vo);

}

if(null != poolGoodsList && poolGoodsList.size() > 0) {

// 用小结果集，驱动大结果集

for(Object object : lucenePager.getResult()) {

Goods goods = (Goods)object;

for(PoolGoods poolGoods: poolGoodsList) {

// Mappper 里的sql 确保返回的第一个Goods就是优先级最高的

if(goods.getGoodsId().equals(poolGoods.getGoodsId())) {

// 两个对象的goodsId 相同，则认为就是同一个商品，就可以用商品池里的价格更新商品的价格

goods.setGoodsPrice(poolGoods.getGoodsPrice());

break;

}

// 修改价格之后的，也可以考虑放到缓存里。

// 这个让生成的HTML去缓存是不是更好？

} else {

// 配置的数据不对

// 处理异常配置

}

测试

重新生成 solr 索引

为了避免分页问题，需要solr的搜索结果是一定要给定满足商品池id 这个条件，这个怎么实现？

solr 的配置文件

http://10.9.19.61:37040/solr/admin.html#/shop_goods/files?file=schema.xml

对于goods_pools 字段的配置：

> 注意：shop_goods 表里的 `goods_pools` 字段不能为空，否则使用admin生成索引的时候会报错：

org.apache.solr.client.solrj.impl.HttpSolrClient$RemoteSolrException: Error from server at http://10.9.19.61:37040/solr/shop_goods: [doc=a100004458] missing required field: goodsPools

所以，需要针对表结构做修改

```sql

alter table shop_goods modify column `goods_pools` varchar(255) DEFAULT '';

update shop_goods set goods_pools = '' where goods_pools is null;

同时，针对java 的 entity 类做修改，给 goodsPools 字段赋默认值

\welfare-core\src\main\java\com\xxyouxx\core\entity\base\Goods.java

@Field

private String goodsPools = "";

更新 shop_goods 表里的 goods_pools 字段

select (select GROUP_CONCAT(pool_id SEPARATOR ',') from bus_rel_goods_pool pg where pg.goods_id = g.goods_id), g.* from shop_goods g;

update shop_goods g set goods_pools = (select GROUP_CONCAT(pool_id SEPARATOR ' ') from bus_rel_goods_pool pg where pg.goods_id = g.goods_id);

update shop_goods set goods_pools = "" where goods_pools is null;