Created
April 12, 2017 15:19
-
-
Save sapiens-sapide/9ed6658acec41501df9e0138ace1d240 to your computer and use it in GitHub Desktop.
requête sql recursive de mastodon pour construire le context
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
def ancestors | |
ids = (Status.find_by_sql(['WITH RECURSIVE search_tree(id, in_reply_to_id, path) AS (SELECT id, in_reply_to_id, ARRAY[id] FROM statuses WHERE id = ? UNION ALL SELECT statuses.id, statuses.in_reply_to_id, path || statuses.id FROM search_tree JOIN statuses ON statuses.id = search_tree.in_reply_to_id WHERE NOT statuses.id = ANY(path)) SELECT id FROM search_tree ORDER BY path DESC', id]) - [self]).pluck(:id) | |
statuses = Status.where(id: ids).with_counters.with_includes.group_by(&:id) | |
ids.map { |id| statuses[id].first } | |
end | |
def descendants | |
ids = (Status.find_by_sql(['WITH RECURSIVE search_tree(id, path) AS (SELECT id, ARRAY[id] FROM statuses WHERE id = ? UNION ALL SELECT statuses.id, path || statuses.id FROM search_tree JOIN statuses ON statuses.in_reply_to_id = search_tree.id WHERE NOT statuses.id = ANY(path)) SELECT id FROM search_tree ORDER BY path', id]) - [self]).pluck(:id) | |
statuses = Status.where(id: ids).with_counters.with_includes.group_by(&:id) | |
ids.map { |id| statuses[id].first } | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment