Conditional Where Clauses using Query Builder

I have one query that is dependent on the results of another. First, I run the following query against a SQL Server DB table:

$variableRevision = DB::connection('engineering')
                ->table('VariableValue as vv')
                ->join('Documents as d', 'd.DocumentID', '=', 'vv.DocumentID')
                ->select('VariableID', 'max(RevisionNo) as MaxRevisionNo')
                ->where('d.Filename', '=', $this->partNumber . self::SOLIDWORKS_PART_FILE_EXTENSION)
                ->whereIn('vv.VariableID', self::PDM_VARIABLES)
                ->get();

This query may return as many as 3 results. I would like to use the results of this query to generate the where clause of the following query:

SELECT v.VariableName, vv.ValueText
  FROM VariableValue as vv
  join Variable as v on v.VariableID = vv.VariableID
  join Documents as d on vv.DocumentID = d.DocumentID
  where d.Filename='151208.SLDPRT' and ((vv.VariableID=61 and vv.RevisionNo=3) or (vv.VariableID=62 and vv.RevisionNo=12) or (vv.VariableID=69 and vv.RevisionNo=12))
  order by vv.VariableID asc

How can I use the VariableIDs and MaxRevisionNos from the first query in the second query?

jlrdw

5 years ago

Level 75

Get the results in variables and use them in the other query.

I do similar by putting results in an array for usage

        $chkmybal[0] = $recol;
        $chkmybal[1] = $finalbalance;
return $chkmybal;

Now I use those results elsewhere.

grozavule

5 years ago

Best Answer

Level 3

Thanks for your input. I had my variables in an Eloquent collection already. After tinkering with the code, I think I found the solution:

$variableRevision = DB::connection('engineering')
                ->table('VariableValue as vv')
                ->join('Documents as d', 'd.DocumentID', '=', 'vv.DocumentID')
                ->select('VariableID', DB::raw('max(RevisionNo) as MaxRevisionNo'))
                ->where('d.Filename', '=', $this->partNumber . self::SOLIDWORKS_PART_FILE_EXTENSION)
                ->whereIn('vv.VariableID', self::PDM_VARIABLES)
                ->groupBy('vv.VariableID')
                ->get();

            $variableValues = DB::connection('engineering')
                ->table('VariableValue as vv')
                ->join('Variable as v', 'v.VariableID', '=', 'vv.VariableID')
                ->join('Documents as d', 'd.DocumentID', '=', 'vv.DocumentID')
                ->select('v.VariableName', 'vv.ValueText')
                ->where('d.Filename', '=', $this->partNumber . self::SOLIDWORKS_PART_FILE_EXTENSION);

            if($variableRevision->count() > 0)
            {
                $variableValues->where(function($query) use ($variableRevision, $variableValues){
                    $variableRevision->each(function($item, $key) use ($query, $variableValues){
                        $query->orWhere(function($q) use($item){
                           $q->where('vv.VariableID', '=', $item->VariableID)
                               ->where('vv.RevisionNo', '=', $item->MaxRevisionNo);
                        });
                    });
                });
            }
            $values = $variableValues->get();

Please or to participate in this conversation.