reading query where clause conditions as JSON and converting it to SQL query

what I want is accessing query whereClause parameters and converting it to a string query to read records from database by eloquent where or whereRaw method ex: converting this:

{"working_years":{"$greaterThan":2,"$lowerThan":6},"car_name":"someName","car_company":"companyName"}

to:

working_years > 2 AND working_years < 5 AND car_name=someName AND car_company = companyName my current code:

(it only reads and saves uery parametrs and whereclause operators in arrays)

   // $query=$request->where;
        //for now we access it from a string:
        $query = '{"working_years":{"$greaterThan":2,"$lowerThan":6},"car_name":"someName","car_company":"companyName"}';
        
        if (isset($query)) {


            $i = 0;
            $whereFieldsAndVals = array([]);// ex:-> [10][name,abc]

            try {

                $operator = array([]);// ex:-> [10][=,>]
                foreach (json_decode($query) as $key => $value) {


                    if (is_object($value)) {//has custom conditions

                        $j = 0;
                        foreach ($value as $k => $v) {

                            $operator[$i][$j] = $k;
                            $whereFieldsAndVals[$i][1] = $v;
                            $j++;
                        }

                    } else {
                        $whereFieldsAndVals[$i][1] = $value;
                    }
                    $whereFieldsAndVals[$i][0] = $key;



                    $i++;

                }
            } catch (\Exception $exception) {
                return $this->customError($exception->getMessage(), 30, 500);
            }

                    }

                    return $operator;
                    //this will return:
                    //[[">","<"]]
                    return $whereFieldsAndVals;
                    //this will return:
                    //[{"1":6,"0":"working_years"},{"1":"someName","0":"car_name"},{"1":"companyName","0":"car_company"}]

    }

aurawindsurfing

7 years ago

Level 50

Hi @winner1

It kind of does not make any sense to do it that way. You have a great explicit framework and yet decide to write pure SQL. I'm a beginner as well but it kind of hurts to read this code ;-)

You know that in Laravel you can chain queries before you finalise them with ->get(). You should look at local query scopes: https://laravel.com/docs/master/eloquent#query-scopes

So you would do something like that on your model:

 public function scopePopular($query)
    {
        return $query->where('votes', '>', 100);
    }

public function scopeActive($query)
    {
        return $query->where('active', 1);
    }

and then use it like so:

$users = App\User::popular()->active()->orderBy('created_at')->get();

You can also build dynamic scopes.

Hope it helps!

winner1

7 years ago

Level 1

thanks @aurawindsurfing ,but i don't have a static model, users choose their table and i connect that table name to a model thanks to a trait but the problem is running this type of complex query on a database

I prefer eloquent or any laravel stuff than pure PHP code but i don't know how, or even that's possible or not because queries are not simple like "$query->where('votes', '>', 100);" a user can add many conditions for each table column

aurawindsurfing

7 years ago

Level 50

So do you actually have a Model?

1 like

winner1

7 years ago

Level 1

@aurawindsurfing yes, not a model for a table, its a model for all of DB tables(we connect tables to that model by table name dynamically)

winner1

7 years ago

Best Answer

Level 1

Finally got the solution

according to Jarek Tkaczyk's answer in this thread: https://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent

we can run our where clause queries using eloquent in this template:

where([
    ['column_1', '=', 'value_1'],
    ['column_2', '<>', 'value_2'],
    [COLUMN, OPERATOR, VALUE],
    ...
])

here is my new code:

if (isset($query)) {

            $fullQuery = [];
            /*
             https://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent
             template:
             where([
             [COLUMN, OPERATOR, VALUE],
             ['column_1', '=', 'value_1'],
             ['column_2', '<>', 'value_2'],
            */

            try {
  //$query = '{"working_years":{">":2,"<":5,"<>":3},"car_name":"bmw","car_company":"abc"}';
                $i = 0;
                foreach (json_decode($query,true) as $key => $value) {

                    if (is_array($value)) {//has custom where condition

                        $count=count($value);
                        $j = 0;
                        foreach ($value as $whereKey => $whereValue) {
                            $fullQuery[$i+$j][0]=$key;
                            $fullQuery[$i+$j][1]=$whereKey;
                            $fullQuery[$i+$j][2]=$whereValue;
                            $j++;
                        }
                        if ($count>0)
                        $i+=($count-1);
                    } else {
                        $fullQuery[$i][0]=$key;
                        $fullQuery[$i][1]='=';
                        $fullQuery[$i][2]=$value;
                      }


                    $i++;

                }
            } catch (\Exception $exception) {
                return $this->customError($exception->getMessage(), 30, 500);
            }
            return $table->where($fullQuery)->first();
}

1 like

Please or to participate in this conversation.