How to make complexe query with laravel Query Builder

Question

Let consider the senario where i have this table parameters with some sample data.

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.

Arnold Tagne · Accepted Answer · 2016-02-04 11:14:33Z

1

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.

answered Feb 4, 2016 at 11:14

Arnold Tagne

744 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arnold Tagne Over a year ago

Happy to know it helped. Happy coding (^_^)

Hamelraj · Accepted Answer · 2016-02-04 06:04:38Z

0

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 :)

edited Feb 4, 2016 at 6:04

answered Feb 3, 2016 at 17:26

Hamelraj

4,8464 gold badges21 silver badges42 bronze badges

2 Comments

tony pro Over a year ago

thanks for your response. But how will i filter for the three parameters if a query can't take three whereBetween() ?

Hamelraj Over a year ago

i have edit my answer im not clear about what you need output just check my answer it will help you sure

Collectives™ on Stack Overflow

How to make complexe query with laravel Query Builder

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related