代码之家 › 专栏 › 技术社区 › anderlaini

在Laravel或raw SQL中创建基于过滤器的搜索的最佳方法是什么?

laravel sql php

anderlaini · 技术社区 · 6 年前

offers 基于不同的过滤器,需要一些指导如何做到这一点。

我的模型结构基本上是: offer -> place -> categories -> filters -> filterOptions

offer   (belongsTo)      <-> (hasMany)        place
place   (belongsToMany)  <-> (belongsToMany)  category
place   (belongsToMany)  <-> (belongsToMany)  filter
filter  (belongsToMany)  <-> (belongsToMany)  category
filter  (hasMany)        <-> (belongsToMany)  filterOption

{
    "category_id":1,
    "filters":[
        {
            "filter_id":1,
            "options":[
                {"option_id":2},
                {"option_id":3}
            ]
        },
        {
            "filter_id":2,
            "options":[
                {"option_id":4},
                {"option_id":6}
            ]
        }
    ]
}

到目前为止,我只是得到整个名单,没有过滤。

$q = Offer::with([
    'place',
    'place.categories',
    'place.categories.filters',
    'place.categories.filters.filterOptions',
])

[
    {
        "id": 4,
        "place_id": 2,
        "place": {
            "id": 2,
            "categories": [
                {
                    "id": 3,
                    "name": "Mechanics",
                    "pivot": {
                        "place_id": 2,
                        "category_id": 3
                    },
                    "filters": [
                        {
                            "id": 5,
                            "name": "Services",
                            "pivot": {
                                "category_id": 3,
                                "filter_id": 5,
                            },
                            "filter_options": [
                                {
                                    "id": 8,
                                    "name": "Tires",
                                    "filter_id": 5
                                },
                                {
                                    "id": 9,
                                    "name": "Painting",
                                    "filter_id": 5
                                }
                            ]
                        },
                        {
                            "id": 6,
                            "name": "Amenities",
                            "pivot": {
                                "category_id": 3,
                                "filter_id": 6,
                            },
                            "filter_options": [
                                {
                                    "id": 11,
                                    "name": "Wifi",
                                    "filter_id": 6
                                },
                                {
                                    "id": 12,
                                    "name": "Food",
                                    "filter_id": 6
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    }
]

我不知道执行此筛选的起点是什么。

1 回复 | 直到 6 年前

anderlaini 6 年前

这样解决:

$params = json_decode($request->getContent());

$q = Offer::with([
    'place:id,title',
    'place.categories:id,name',
    'place.categories.filters:id,name',
    'place.categories.filters.filterOptions:id,name,filter_id'
])
->whereHas('place.categories', function($q) use ($params){
    $q->where('id', $params->category_id);
})
->whereHas('place.categories.filters', function($q) use ($params){
    $filters = array_flatten(array_pluck($params->filters, 'filter_id'));
    $q->whereIn('id', $filters);
})
->whereHas('place.categories.filters.filterOptions', function($q) use ($params){
    $filterOptions = array_flatten(array_pluck($params->filters, 'options.*.option_id'));
    $q->whereIn('id', $filterOptions);
})
->select('id','percent','place_id')
->get();