​

hyperf关联子表查询主表数据

1. 商品表goods
2. 商品语言表goods_language
3. 商品资源表goods_image_map

商品表与【语言、资源】属于一对多的关系

• 业务1：查询语言表里面标题等于*** 的商品主表数据
• 业务2：查询出的子表数据，仅仅查询需要的数据

商品表模型

class GoodsModel extends Model
{

    //关联语言数据
    public function language()
    {
        return $this->hasMany(GoodsLanguageModel::class, 'goods_id', 'id');
    }
    //查询使用的配置
    public function goodsable(): \Hyperf\Database\Model\Relations\MorphTo
    {
        return $this->morphTo();
    }
    //关联资源数据
    public function image()
    {
        return $this->hasMany(GoodsImageMapModel::class, 'goods_id', 'id')->orderByRaw('image_type asc,sort_id asc');
    }

}

商品语言表模型

class GoodsLanguageModel extends Model
{
    public function goods(): \Hyperf\Database\Model\Relations\MorphOne
    {
        return $this->morphOne(GoodsModel::class, 'goodsable',null,'id','goods_id');
    }
}

商品资源表

class GoodsImageMapModel extends Model
{

}

查询实现

use Hyperf\Database\Model\Builder;
use Hyperf\Database\Model\Relations\Relation;

class Test
{
    //根据商品ID和语言子表的标题进行查询商品数据
    public function search(string $language,string $title,array $goodsIdList,int $status)
    {
        $model = GoodsModel::query();
        //主表上架状态
        if($status){
            $model = $model->where('status','=',1);
        }
        //主表ID范围
        if($goodsIdList){
            $model = $model->whereIn('id',$goodsIdList);
        }
        //筛选子表的语言标识的字段范围
        $languageRange = array_unique([$language,Context::get('mainLanguageCode')]);

        if($title&&$title=urldecode($title)){
            //查询子表里面包含$title的主表数据，并且在语言范围的
            $model = $model->whereHas('language',function(Builder $query)use($title,$languageRange){
                $query->whereIn('language',$languageRange)->where('title','=',$title);
            });
        }
        //获取到的语言数据有范围限制，资源数据有筛选条件和排序
        return $model->with([
            "language" => function(Relation $relation)use($languageRange){
                return $relation->getQuery()->whereIn('language',$languageRange)->select(['id','goods_id','language','title','description']);
            },
            'image'=>function(Relation $relation){
                return $relation->getQuery()->where('resources_type','=',1)->orderByRaw('image_type asc,sort_id asc')->select(['id','goods_id','resources_id','sort_id','image_type']);
            }])->select(['id','status','shop_id'])->get()->toArray();

    }
}

​

本作品采用《CC 协议》，转载必须注明作者和本文链接