CODEWITHSUNDEEP
mysqldatabaselaravel-5laravel-query-builder
tony pro
tony pro

Reputation: 435

How to make complexe query with laravel Query Builder

Let consider the senario where i have this table parameters with some sample data. enter image description here

I need to query these data into three categories as describing bellow.

query1 = get all records that has: param1=param2=param3= 100% as from this case the oupout is the first record. Not problem with this query.

query2 = get all records that has: at least param1 < 80 OR param2 < 80 OR param3 < 80 OR all as from this case the outpout is the second and the third record.

query3 = get all records that has: at least param1 >= 80 OR param2 >= 80 OR param3 >= 80 BUT NOT ALL EQUAL to 100% as from this case the outpout is the fourth and fifth records.

I am really stock at query2 and query3. bellow is my query using laravel query builder.

            $query = DB::table('parameters');                
            if ($query === 1) {
                $query->where('param1', '=', 100)
                      ->where('param2', '=', 100)
                      ->where('param3', '=', 100);
            }elseif ($query === 2) {
                $query->where('param1', '<', 80)
                      ->where('param2', '<', 80)
                      ->where('param3', '<', 80);
            }else{
                $query->whereBetween('param1', [80, 100])
                      ->whereBetween('param2', [80, 100])
                      ->whereBetween('param3', [80, 100]);
            }
            $result = $query->get();

Hope my question is clair enougth. Thanks in advance for your help.

Upvotes: 2

Views: 107

Answers (2)

Arnold Tagne
Arnold Tagne

Reputation: 74

I think it will be easier for you to use an average of the three params since you have an interval to respect. 

//param_avg = (param1 + param2 + param3)/3;

$query = DB::table('parameters')->get();
//For query 1
    $query->where('param1', '=', 100)
          ->where('param2', '=', 100)
          ->where('param3', '=', 100); 

//For query2
 $query->where('param1', '<', 100)
       ->where('param2', '<', 100)
       ->where('param3', '<', 100);

//For query3
$query->select(DB::raw('WHERE (param1 + param2 + param3)/3 <=80 AND (param1 + param2 + param3)/3 <100'));

Hope it will help you.

Upvotes: 1

Hamelraj
Hamelraj

Reputation: 4826

Hey you can't use in one query three whereBetween() and in your query2 = is you have mention param1,param2,param3 < 80 the query should be

$query = DB::table('parameters')
                ->whereBetween('param1', [0, 80])->get();

same way query 3

$query = DB::table('parameters')
                    ->whereBetween('param1', [80, 100])->get();

use query for every column use rules for that all queries use this link - link

EDITED ANSWER

public function getData($id)
    {
        $query = DB::table('parameters')->find($id);

        if($query->param1==100 && $query->param2==100 && $query->param3==100){

        }elseif($query->param1<80 or $query->param2<80 or $query->param3<80){

        }else{

        }

    }

Route this function properly you will get output :)

Upvotes: 0

Related Questions