raw SQL to laravel query

I see a lot of people that are tempted to use the query builder as a requirement for all queries, but it reaches a limit where the "niceness" of writing your query in PHP is undone as you loose sight of the query and keeping the code maintainable.

If you got it working with the query builder and had to change it, you would need to spend a lot of time debugging it, or keep a copy of the SQL equivalent as a comparison ( toSql() is not going to give you decent indentation of the query).

Once you start using window functions it's rarely worth the effort.

My advice would be to use the repository pattern (Jeffrey has a tutorial on here about it) and to put the whole query into a DB::select($query, $bindings) so you can format it properly. (If you need to use collection functions on the result, just wrap it collect(DB::select("QUERY HERE"));

Then if you need to test parts of the query, you can copy and paste them into your SSMS or other software and transfer them back in when they work.

SELECT *
FROM
  (
    SELECT
      ROW_NUMBER()
        OVER(
          ORDER BY
            CASE
              WHEN (
              indesign.status = 4
              OR indesign.statusdate IS NULL
            ) THEN getdate() + 2
              ELSE indesign.statusdate
            END
          ASC) AS RowNum,
      a.*
    FROM sapakInAdminOrder a
    LEFT JOIN currency cu
      ON cu.id = a.currency
    LEFT JOIN moodboards m
      ON m.id = a.orderMoodboardID
    INNER JOIN Clients b
      ON a.clientID = b.id
    LEFT JOIN moodboards mc
      ON mc.id = b.moodboardID
    INNER JOIN Sapakim c
      ON b.sapakID = c.id
    LEFT JOIN Sapakim sm
      ON sm.id = c.managerid
    LEFT JOIN products p
      ON p.id = a.productKey
    LEFT JOIN
      ( 
        SELECT *
        FROM
          (
            SELECT ROW_NUMBER() OVER(PARTITION BY orderID ORDER BY id DESC) r,
            *
            FROM orderCommunication
          ) f
        WHERE r = 1
      ) chat
      ON chat.orderId = a.id
    LEFT JOIN
      (
        SELECT
          id,
          [status],
          orderid,
          approveSMSDate,
          coverImage,
         statusDate
        FROM
          (
            SELECT
              id,
              [status],
              statusDate,
              approveSMSDate,
              coverImage,
              orderid,
              ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY id DESC) AS r
            FROM SapakimInAdminDesigns
          ) f
        WHERE r = 1
      ) indesign
      ON a.id = indesign.orderid
    WHERE
      (a.isDeleted IS NULL OR a.isDeleted != 1)
      AND c.inAdminManagerID =
        (
          SELECT id
          FROM sapakim
          WHERE sapakguid='test'
        )
      AND c.sapakguid = 'test'
      AND a.isFreeDesign = 0
      AND a.transactionID = -1
      AND (
        a.designerPaid IS NULL
        OR a.designerPaid = 0
      )
      AND (
        chat.sentToPrinter IS NULL
        AND chat.sentToManager IS NULL
        AND chat.sentToDesigner IS NULL
      )
  ) bb
WHERE
  RowNum >= 1
  AND RowNum < 31
ORDER BY RowNum ASC