CODEWITHSUNDEEP
phplaravel
ssuhat
ssuhat

Reputation: 7656

Eloquent Query Dynamically based on json

I want to query dynamically based on payload(json) from database.

Example:

$data = [{"key":"age","relation":">","value":"15"},{"operator":"OR"},{"key":"age","relation":"<=","value":"20"}]

I want to do query based on that payload.

Right now what I'm doing is:

$query = User::all();
$payload = json_decode($data, true);
foreach($payload as $value){
    if ($value['key'] == 'age') {
                $query = $query->where('birthday', $value['relation'], Carbon::now()->subYears($value['age'])->format('Y-m-d');)
            }

    if($value['key'] == 'gender'{
        $query = $query->where('gender', $value['relation'], $value['gender']);
    }
}

The problem is yes it can work, but I don't think this is best approach. I don't get any solution to use the "operator" key. Operator usage is to change where to orWhere.

Any solution or tips to make it call dynamically like this?. I want my column at DB neat and simple. I can only think this way.

Thanks!

Upvotes: 1

Views: 739

Answers (4)

revo
revo

Reputation: 48711

Encountering this problem, I would go with Local Query Scopes. In this approach You create a model method named scopeJson() or whatever you feel better with to handl all conditions inside. I tried to handle most conditions here not only single where and orWhere. I assumed that your payload contains only one builder at a time.

public function scopeJson($query, $json)
{
    $wheres = [
        'between' => ['whereBetween', 'not' => 'whereNotBetween'],
        'null'    => ['whereNull', 'not' => 'whereNotNull'],
        'or'      => ['orWhere', 'not' => 'orWhereNot'],
        'in'      => ['whereIn', 'not' => 'whereNotIn'],
        'and'     => ['where', 'not' => 'orWhereNot'],
        'raw'     => 'whereRaw'
    ];

    $builder = json_decode($json);

    if (count($builder) > 0) {
        $query->where(
            $builder[0]->key,
            $builder[0]->relation,
            $builder[0]->value
        );

        // notBetween, notNull, notOr, notIn, notAnd
        if (stripos($builder[1]->operator, 'not') !== false) {
            $whereCondition = $wheres[strtolower(substr($builder[1]->operator, 3))]['not'];
        } else {
            $whereCondition = $wheres[strtolower($builder[1]->operator)];
        }

        if (count($builder[2]) == 3) {
            if ($whereCondition == 'whereRaw') {
                $query->$whereCondition(implode(" ", $builder[2]));
            } else {
                // where, whereNot
                $query->$whereCondition(
                    $builder[2]->key,
                    $builder[2]->relation,
                    $builder[2]->value
                );
            }
        } elseif (count($builder[2]) == 2) {
            // whereBetween, whereNotBetween, where, whereNot
            $query->$whereCondition(
                $builder[2]->key,
                $builder[2]->value
            );
        } elseif (count($builder[2]) == 1) {
            // whereNull, whereNotNull, whereRaw
            $query->$whereCondition(
                $builder[2]->key ?? $builder[2]->value // PHP 7.0 Null Coalescing Operator
            );
        }
    }
    return $query;
}

If this method is defined within your User's model then you can use it this way:

$users = User::json($data)->get();

PS: Although it should work, I didn't test it.

Upvotes: 2

Jeff
Jeff

Reputation: 25221

You can use a variable function name to add your orWhere logic:

$query = User::all();
$payload = json_decode($data, true);
$function = 'where';
foreach($payload as $value){
  if(isset($value['operator'])){
    $function = $value['operator'] == 'OR' ? 'orWhere' : 'where';
  } else {
    if ($value['key'] == 'age') {
      $query = $query->$function('birthday', $value['relation'], Carbon::now()->subYears($value['age'])->format('Y-m-d');)
    } else {
      $query = $query->$function($value['key'], $value['relation'], $value['value']);
    }
  }
}

As long as your json data doesn't match your database (ie. the json has age but the database has birthday) you will not be able to avoid having that if/else statement. That custom logic will have to remain.

Upvotes: 1

Qevo
Qevo

Reputation: 2371

Ultimately this idea is its own limiter because the stored queries will have to represent the current state of the database. This means that the maintenance cost of these queries will be significant the moment data is stored in a different way -- if you changed the column birthday to date_of birth all of your stored queries will break. Avoid this.

Instead this goal is better achieved by storing the queries on the Model using Query Scopes and Relationships. If you still need a dynamic list of requests you can store keywords that are associated with the queries and loop through them.

Upvotes: 0

z3r0ck
z3r0ck

Reputation: 683

You can do raw query like this.

$data = '[{"key":"age","relation":">","value":"15"},{"operator":"OR"},{"key":"age","relation":"<=","value":"20"}]';

$query = "SELECT * FROM tablename WHERE";

$payload = json_decode($data, true);

foreach ($payload as $value) {
    if (isset($value['operator'])) {
        $query .= " " . $value['operator'];
    } else {
        if ($value['key'] == 'age') {
            $query .= " 'birthday' " . $value['relation'] . " " .  Carbon::now()->subYears($value['value'])->format('Y-m-d');
        }

        if ($value['key'] == 'gender') {
            $query .= " 'gender' " . $value['relation'] . " " . $value['gender'];
        }
    }
}

This results in a query like this :

SELECT * FROM tablename WHERE 'birthday' > 2001-07-02 OR 'birthday' <= 1996-07-02

Of course, you might use printf() for formatting and making this cleaner in some other way but this will get you started hopefully.

Upvotes: 1

Related Questions