Search code examples
symfonydoctrinedql

How can I get the row number of results querying with DQL(Doctrine)

example: SELECT title,ROW_NUM FROM article ORDER BY count_read. What should ROW_NUM be replace by ?

I don't like to after getting the results generate the index by program, because I want to insert into a table Rank with the result data by querying the example DQL above.

What I want to achieve maybe like :

"INSERT INTO RANK r (title, index, lastIndex)
SELECT title,ROW_NUM,(SELECT index FROM RANK WHERE id = :id - 1) FROM article ORDER BY count_read"

Thanks in advance..

Solution

  • I think you might use variables, like this:

    "
SET @row_num := 1;
INSERT INTO RANK r (title, index, lastIndex)
SELECT title, 
       (@row_num := @row_num + 1),
       (SELECT index FROM RANK WHERE id = :id - 1) 
FROM article ORDER BY count_read
"