title: Raw Laravel query as a collection with conditions
body: <p>I've got a fairly complex query that I'd rather not write in Eloquent so I've written it raw. The only problem is that I need to be able to search/filter through the data using the front-end this query is connected to.</p>
  
  <p>This what I've tried but I'm getting an "Call to a member function get() on null" error.</p>
  
  <p>Here's my code:</p>
  
  <pre><code>$report = collect(DB::connection('mysql2')-&gt;select("SELECT
              t2.holder,
              t2.merchantTransactionId,
              t2.bin,
              t2.last4Digits,
              t3.expDate,
              (CASE WHEN t3.expDate &lt; CURDATE() THEN 'Expired'
              WHEN t3.expDate &gt; CURDATE() THEN 'Due to expire' END) AS expInfo,
              t2.uuid
              FROM transactions AS t2
              INNER JOIN (
                  SELECT t1.uuid, t1.holder, t1.bin, t1.last4Digits, LAST_DAY(CONCAT(t1.expiryYear, t1.expiryMonth, '01')) AS expDate
                  FROM transactions t1
                  JOIN (SELECT t1.merchant_access
                          FROM total_control.users, 
                          JSON_TABLE(merchant_access, '$[*]' COLUMNS (
                              merchant_access VARCHAR(32) PATH '$')
                          ) t1
                          WHERE users.id = :userId
                          ) AS t2
                      ON t1.merchantUuid = t2.merchant_access
                  WHERE t1.paymentType = 'RG'
                  AND t1.status = 1
              ) t3
              ON t2.uuid = t3.uuid
              WHERE t3.expDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
              GROUP BY t2.holder, t2.bin, t2.last4Digits
              ORDER BY t2.holder ASC", ['userId' =&gt; $request-&gt;userId]))
              -&gt;when($request-&gt;search['holder'], function($q) use ($request) {
                  $q-&gt;where('t2.holder', 'LIKE', '%'.$request-&gt;search['holder'].'%');
              })-&gt;get();
  
  return $report;
  </code></pre>