首先我们来看三个表的关联 goods(商品表)、goods_category(商品分类表)、shops(商铺表)
Field | Type | Comment | |
goodsId | int(11) NOT NULL | ||
goodsSn | varchar(20) NOT NULL | ||
goodsName | varchar(50) NOT NULL | ||
img_big | varchar(150) NOT NULL | ||
img_centre | varchar(150) NOT NULL | ||
img_small | varchar(150) NOT NULL | ||
brandId | int(11) NULL | ||
shopId | int(11) NOT NULL | ||
marketPrice | decimal(11,2) NOT NULL | ||
shopPrice | decimal(11,2) NOT NULL | ||
goodsStock | int(11) NOT NULL | ||
saleCount | int(11) NULL | ||
isBook | tinyint(4) NOT NULL | ||
bookQuantity | int(11) NOT NULL | ||
warnStock | int(11) NOT NULL | ||
goodsUnit | char(10) NOT NULL | ||
goodsSpec | text NULL | ||
isSale | tinyint(4) NOT NULL | ||
isBest | tinyint(4) NULL | ||
isHot | tinyint(4) NULL | ||
isRecomm | tinyint(4) NULL | ||
isNew | tinyint(4) NULL | ||
isAdminBest | tinyint(4) NULL | ||
isAdminRecom | tinyint(4) NULL | ||
recommDesc | varchar(255) NULL | ||
goodsCatId1 | int(11) NOT NULL | ||
goodsCatId2 | int(11) NOT NULL | ||
goodsCatId3 | int(11) NOT NULL | ||
shopCatId1 | int(11) NOT NULL | ||
shopCatId2 | int(11) NOT NULL | ||
goodsDesc | text NOT NULL | ||
isShopRecomm | tinyint(4) NOT NULL | ||
isIndexRecomm | tinyint(4) NOT NULL | ||
isActivityRecomm | tinyint(4) NOT NULL | ||
isInnerRecomm | tinyint(4) NOT NULL | ||
goodsStatus | tinyint(4) NOT NULL | ||
saleTime | datetime NULL | ||
attrCatId | int(11) NULL | ||
goodsKeywords | varchar(200) NULL | ||
goodsFlag | tinyint(4) NOT NULL | ||
statusRemarks | varchar(255) NULL | ||
createTime | datetime NOT NULL | ||
isDistribut | int(11) NULL | ||
commission | int(11) NULL |
Field | Type | Comment | |
catId | int(11) NOT NULL | ||
parentId | int(11) NOT NULL | ||
isShow | tinyint(4) NOT NULL | ||
catName | varchar(20) NOT NULL | ||
priceSection | text NULL | ||
catSort | int(11) NOT NULL | ||
catFlag | tinyint(4) NOT NULL | ||
isFloor | tinyint(4) NOT NULL |
Field | Type | Comment | |
shopId | int(11) NOT NULL | ||
shopSn | varchar(20) NOT NULL | ||
userId | int(11) NOT NULL | ||
areaId1 | int(11) NOT NULL | ||
areaId2 | int(11) NOT NULL | ||
areaId3 | int(11) NOT NULL | ||
goodsCatId1 | int(11) NOT NULL | ||
goodsCatId2 | int(11) NULL | ||
goodsCatId3 | int(11) NULL | ||
isSelf | tinyint(4) NOT NULL | ||
shopName | varchar(100) NOT NULL | ||
shopCompany | varchar(255) NOT NULL | ||
img_big | varchar(150) NOT NULL | ||
img_small | varchar(150) NOT NULL | ||
shopTel | varchar(20) NULL | ||
shopAddress | varchar(255) NOT NULL | ||
avgeCostMoney | decimal(11,2) NULL | ||
deliveryStartMoney | decimal(11,2) NULL | ||
deliveryMoney | decimal(11,2) NULL | ||
deliveryFreeMoney | decimal(11,2) NULL | ||
deliveryCostTime | int(11) NOT NULL | ||
deliveryTime | varchar(255) NOT NULL | ||
deliveryType | tinyint(4) NOT NULL | ||
bankId | int(11) NOT NULL | ||
bankNo | varchar(20) NOT NULL | ||
isInvoice | tinyint(4) NOT NULL | ||
invoiceRemarks | varchar(255) NULL | ||
serviceStartTime | float(11,1) NOT NULL | ||
serviceEndTime | float(11,1) NOT NULL | ||
shopStatus | tinyint(4) NOT NULL | ||
statusRemarks | varchar(255) NULL | ||
shopAtive | tinyint(4) NOT NULL | ||
shopFlag | tinyint(4) NOT NULL | ||
createTime | datetime NOT NULL | ||
latitude | char(30) NULL | ||
longitude | char(30) NULL | ||
mapLevel | int(11) NULL | ||
qqNo | varchar(20) NULL | ||
bankUserName | varchar(100) NOT NULL | ||
isDistributAll | tinyint(4) NULL |
然后在 Model 用语句取出商品分类名、商铺名(合成取法)
return $this->field( "g.*, s.shopname, c.catname" )->table( "__GOODS__ as g" )->join( "LEFT JOIN __SHOPS__ as s ON g.shopid = s.shopid " . "LEFT JOIN __GOODS_CATEGORY__ as c ON g.goodscatid1 = c.catid" )->select();