vendredi 18 septembre 2015

sqlite3 limit cte recursion

I have this query:

WITH RECURSIVE cte(Id, Type, ParentId, CreationDate, SenderId, Message) AS 
(
  SELECT c.* FROM (SELECT Id, Type, ParentId, CreationDate, SenderId, Message
                   FROM GroupMessage
                   WHERE GroupId = '4a80590e-036c-45b6-a512-be0d8e48fbb6'
                     AND ParentId is NULL
                   ORDER BY CreationDate DESC
                   LIMIT 20 OFFSET 0) as c
  UNION
  SELECT c.Id, cte.Type+1, c.ParentId, c.CreationDate,  c.SenderId, c.Message
  FROM cte, GroupMessage c
  WHERE c.ParentId = cte.Id AND c.Type = '1'
  ORDER BY CreationDate DESC
)

SELECT * FROM cte

Currently I am limiting the number of parent messages to 20 most recent ones. How could I also limit the number of children to lets say 5 or 10 most recent ones? I Tried adding a LIMIT keyword after the UNION ORDER BY, but then the whole query returned only 5 or 10 rows. Also the OPTION keyword is not available in Sqlite3 and I am running out of ideas.

Aucun commentaire:

Enregistrer un commentaire