How should I rewrite this sub query with AR

Hi

I’m using Oracle 11g for this so LIMIT doesn’t exist. And I’m not sure how I’d write the following query into Active Record

Yii::$app->db->createCommand("

SELECT FIELD_ID,

       FIELD_VALUE, chr (13), ''), chr (10), ' ')) as FIELD_VALUE

       FROM PROCESS_EXTRACTION T1

       WHERE (PROCESS_ID = :PROCESS_ID)

       AND CREATED_AT = (

                    SELECT MAX(T2.CREATED_AT)

                    FROM PROCESS_EXTRACTION T2

                    WHERE T2.PROCESS_ID = T1.PROCESS_ID

                    AND T2.FIELD_ID = T1.FIELD_ID

                )

                ORDER BY T1.ID ASC")

                ->bindValue(':PROCESS_ID', $id)

                ->queryAll();

I’d use as is with the PDO instance. I don’t want to repeat a bunch of stuff, so this is very similar to a laravel question.

Please read over the replies:

https://laracasts.com/discuss/channels/guides/getpdo-usage

https://laracasts.com/discuss/channels/eloquent/writing-all-queries-directly-vs-model-relations

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

https://laracasts.com/discuss/channels/laravel/coverting-ms-access-queries-to-laravel-query-builder

https://laracasts.com/discuss/channels/general-discussion/sql-injection-2

As far as Oracle 11g and LIMIT a few years back I wrote one for an older version of sql server. Oracle is similar. Not at that computer now, but will try to post tomorrow. Of course newer versions you don’t have to code your own, it’s now built in.

Meanwhile search for limit and offset equivalent in oracle, I am sure you will find many examples. The trick is to make it a reusable class that can be called.

Much as I appreciate your answer, the basis of the question is to use Active Record

This is how I did it:

$sql = Yii::$app->db->createCommand("SELECT FIELD_ID,

       FIELD_VALUE, chr (13), ''), chr (10), ' ')) as FIELD_VALUE

       FROM PROCESS_EXTRACTION T1

       WHERE (PROCESS_ID = :PROCESS_ID)

       AND CREATED_AT = (

                    SELECT MAX(T2.CREATED_AT)

                    FROM PROCESS_EXTRACTION T2

                    WHERE T2.PROCESS_ID = T1.PROCESS_ID

                    AND T2.FIELD_ID = T1.FIELD_ID

                )

                ORDER BY T1.ID ASC")

                ->bindValue(':PROCESS_ID', $id)


return ProcessExtraction::findBySql($sql->rawSql)->all();