I’d like to get a query to return a ROW_NUMBER; however, I’m using a version of MySQL old enough that it doesn’t have that as a built-in function. According to this page there are two ways to do it, using a session variable or a derived table:
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num,
firstName,
lastName
FROM
employees
ORDER BY firstName, lastName
LIMIT 5;
SELECT
(@row_number:=@row_number + 1) AS num,
firstName,
lastName
FROM
employees,
(SELECT @row_number:=0) AS t
ORDER BY
firstName,
lastName
LIMIT 5;
Unfortunately, I can’t seem to get Yii 2 to build the query this way. For the session variable, I don’t know how to get Yii 2 to prepend a line to the query. For the derived table, if I try to modify the from()
field, it attempts to also add code to INNER JOIN
the derived table.