1 month ago

Conditional Where Clauses using Query Builder

Posted 1 month ago by grozavule

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)

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?

Please sign in or create an account to participate in this conversation.