CODEWITHSUNDEEP
mysqllaraveleloquent
D3V1L
D3V1L

Reputation: 155

Laravel Eloquent (QueryBuilder) filtering in hasMany relations tables

Have this code:

this is in RentRequest model:

/**
 * Apply all relevant advert filters.
 *
 * @param Builder $query
 * @param ParameterFilter $filters
 * @return Builder
 */
public function scopeParamFilter($query, ParameterFilter $filters)
{
    $query->leftJoin('request_parameter', 'rent_requests.id', '=', 'request_parameter.request_id');
    return $filters->apply($query);
}

this in parameter filter:

/**
 * Filter the query by a given area.
 *
 * @param $total_area
 * @return \Illuminate\Database\Eloquent\Builder
 */
protected function total_space($total_area)
{
    $this->builder->where(function ($queryId) use ($total_area) {
        $query = DB::table('request_parameter')->where('request_parameter.key', 'total_space_from');

        if (!empty($total_area['from'])) {
            $query->where('request_parameter.value', '>=', (int) $total_area['from']);
        }

        $valueIds = $query->pluck('request_id');

        $haveNoValueIds = DB::table('request_parameter')
            ->selectRaw('DISTINCT request_id')
            ->whereNotIn('request_id',
                DB::table('request_parameter')
                    ->select('request_id')
                    ->where('key',  'total_space')
            )
            ->pluck('request_id');

        $advertIds = $valueIds->merge($haveNoValueIds);

        $queryId->whereIn('request_parameter.request_id', $advertIds->all());
    });

    return $this->builder;
}

this is request_parameter schema:

enter image description here

so i have simple has many relation with parameters table based on rent_request.id

now i make filter logic, and try to do the same logic as previous developers made, but i cant understand it
main question is how to filter requests by the parameters that lying in the another table ? current filter function works with the same logic in another project, maybe there are errors there ? sorry for my english <3

Upvotes: 1

Views: 180

Answers (1)

D3V1L
D3V1L

Reputation: 155

this is final version of my function, it works for me

/**
 * Filter the query by a given area.
 *
 * @param $total_area
 * @return \Illuminate\Database\Eloquent\Builder
 */
protected function total_space($total_area)
{
    $this->builder->where(function ($queryId) use ($total_area) {
        $query = DB::table('request_parameter');

        if (!empty($total_area['from'])) {
            $query->where('request_parameter.key', 'total_space_from')
                ->where('request_parameter.value', '<=', (int) $total_area['from']);
        }

        if (!empty($total_area['to'])) {
            $query->where('request_parameter.key', 'total_space_to')
                ->where('request_parameter.value', '<=', (int) $total_area['to']);
        }

        $valueIds = $query->pluck('request_id');

        info($valueIds);

        $haveNoValueIds = DB::table('request_parameter')
            ->selectRaw('DISTINCT request_id')
            ->whereNotIn('request_id',
                DB::table('request_parameter')
                    ->select('request_id')
                    ->where('key',  'total_space_from')
                    ->orWhere('key',  'total_space_to')
            )
            ->pluck('request_id');

        $advertIds = $valueIds->merge($haveNoValueIds);

        $queryId->whereIn('request_parameter.request_id', $advertIds->all());
    });

    return $this->builder;
}

p.s. the filter algo is next: even when adverts dont have these parameters, its also return from filter function. these was the key to understand what they are do in this function, and helps me to solve my problem.

Upvotes: 1

Related Questions