0

I'd like to use SQL Server's OUTER APPLY in Laravel for a query like this:

SELECT ACol1, ACol2, b.BCol2
FROM tblA
OUTER APPLY (SELECT TOP 1 BCol2 
             FROM tblB WHERE BCol1 = tblA.ACol1
             ORDER BY ins_dtim DESC) b

However, as infered, Laravel doesn't offer any direct method for OUTER APPLY. I was thinking of using raw expressions to cope with this, but the documentation only describes raw methods for SELECT, WHERE, ORDER, etc. Is there a workaround to archive this, or a library capable of using OUTER APPLY. I'd like to avoid, if possible, nested subqueries, views or stored procedures. Thanks!

  • In that case you can use your complete select raw query in `select` method like this `$result = DB::connection('')->select($query, $bindings);` – Haridarshan Jun 03 '23 at 17:37

1 Answers1

0

A raw expression can be utilized to accomplish the desired outcome. However, this method uses a subquery. Try the following.

DB::table('tblA')
    ->select('ACol1', 'ACol2', 'b.BCol2')
    ->leftJoin(DB::raw('(SELECT TOP 1 BCol2 FROM tblB WHERE BCol1 = tblA.ACol1 ORDER BY ins_dtim DESC) AS b'))
    ->get();
Karl Hill
  • 12,937
  • 5
  • 58
  • 95