title: Turning my raw SQL code in Laravel ORM code vs using it raw
body: <p>I have a bit of a complicated SQL query that I need to have it on my Laravel web app in order to filter some data.</p>
  <p>After a bit of trial and error, I figured out how my SQL query should look (the results are fine in PHPmyadmin).</p>
  <p>But now I get some errors when using it in Laravel. I know why, but I don't know should I fix it.</p>
  <p>So here's the file that I'm using for filtering:</p>
  <pre><code>&lt;?php
  
  namespace App\Filters;
  
  use Illuminate\Database\Eloquent\Builder;
  use DB;
  
  use App\Student;
  
  
  /**
   * Class DepartamentFilter
   * @package App\Filters
   */
  class DoctoranziFilter extends Filter
  {
      /**
       * @param Builder $query
       * @return mixed
       */
      public function apply($query)
      {
  
          if ($this-&gt;request-&gt;exists('filters')) {
  
              $name = $this-&gt;request-&gt;get('nume');
              if ($name !== null) {
                  $query = $query-&gt;where('nume', 'like', '%' . $name . '%');
              }
  
              $an_inmatriculare = $this-&gt;request-&gt;get('an_inmatriculare');
              if ($an_inmatriculare !== null) {
                  $query = $query-&gt;where('an_inmatriculare', 'like', '%' . $an_inmatriculare . '%');
              }
  
              $domeniu_doctorat_id = $this-&gt;request-&gt;get('domeniu_doctorat_id');
              if ($domeniu_doctorat_id !== &quot;0&quot;) {
                  $query = $query-&gt;where('domeniu_doctorat_id', '=', $domeniu_doctorat_id);
              }
  
              $conducator_doctorat_id = $this-&gt;request-&gt;get('conducator_doctorat_id');
              if ($conducator_doctorat_id !== &quot;0&quot;) {
                  $query = $query-&gt;where('conducator_doctorat_id', '=', $conducator_doctorat_id);
              }
  
              $status = $this-&gt;request-&gt;get('status');
              if ($status !== &quot;0&quot;) {
                  $query = DB::Select(&quot;select * from `students`
                  where (
                      select status_student.id from `status_student` 
                  WHERE
                  status_student.id = (SELECT MAX(status_student.id) from `status_student`
                  WHERE 
                  status_student.student_id = students.id)
                  AND
                  status_student.status_id = 6
                  ) 
                  IS NOT NULL;&quot;);
              } 
  
              $stare_stagiu = $this-&gt;request-&gt;get('stare-stagiu');
              if ($stare_stagiu !== &quot;0&quot;) {
                  $query = Student::whereHas('statusuri', function($q) use ($stare_stagiu) {
                      $q-&gt;where('stare_stagiu_id', '=', $stare_stagiu);
                  });
              }        
  
              $forma_admitere_id = $this-&gt;request-&gt;get('forma_admitere_id');
              if ($forma_admitere_id !== &quot;0&quot;) {
                  $query = $query-&gt;where('forma_admitere_id', '=', $forma_admitere_id);
              }
  
              $campOrdonare = $this-&gt;request-&gt;get('ordonare-dupa-camp');
              if($campOrdonare !== &quot;0&quot;) {
                  $ordine = $this-&gt;request-&gt;get('ordine');
                  if($campOrdonare === &quot;1&quot;)
                  {
                      $query = $query-&gt;orderBy('nume', $ordine);
                  }
                  else
                  {
                      $query = $query-&gt;orderBy('an_inmatriculare', $ordine);
                  }
              }
  
              return $query;
  
          } else {
  
              $query = $query-&gt;where('respins', '=', 0);
  
              return $query;
          }
      }
  }
  </code></pre>
  <p>The problematic part is the query on $status.</p>
  <p>From all the others $query variable, I chain a bit of SQL. (like, a where, an order by, etc)
  For the $status variable, I don't do that, I'm using raw SQL already and that is why it is not working well.</p>
  <p>So, what should I do? Is there any way to somehow turn that DB::Select into a QueryBuilder? (I don't think so, to be honest)</p>
  <p>I think that I need to turn that raw SQL into Eloquent but I'm having trouble doing that. I've been trying that for a long time and I couldn't do it.</p>
  <p>I have 3 tables that this $status query needs to operate on:</p>
  <pre><code>STUDENTS     STATUS        STATUS_STUDENT
  ID           ID            students.id
                             status.id
  </code></pre>
  <p>I will explain the query from the code:</p>
  <pre><code>&quot;select * from `students`
                  where (
                      select status_student.id from `status_student` 
                  WHERE
                  status_student.id = (SELECT MAX(status_student.id) from `status_student`
                  WHERE 
                  status_student.student_id = students.id)
                  AND
                  status_student.status_id = 6
                  ) 
                  IS NOT NULL;&quot;
  
  6 would be the user input, in this case it is hardcoded for testing purposes
  </code></pre>
  <p>A student can have multiple status - but the active one is the last one that he has. Then I want to check if the student's active status id is equal to the user input (in the case above with 6, it was hardcoded for my tests). If so, return the students which match the criteria. If not, don't return anything. (so, for example, if the student has that status but it is not the active one, don't return anything)</p>
  <p>Here's the relationship between Student and Status in code:</p>
  <pre><code>public function statusuri()
  {
          return $this-&gt;belongsToMany(Status::class, 'status_student')
              -&gt;withPivot('id', 'status_id', 'stare_stagiu_id')
              -&gt;withTimestamps();
  }
  </code></pre>
  <p>So, how can I make this query work in my context? (having it chained with QueryBuilders while it being raw SQL). If I am right and I need to turn it into Eloquent code, can anybody help me do it? I couldn't figure it out myself.</p>
  <p>Thanks.</p>