Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Single query for fetching Channels in list endpoint #206

Open
elpiel opened this issue Nov 27, 2019 · 0 comments
Open

Single query for fetching Channels in list endpoint #206

elpiel opened this issue Nov 27, 2019 · 0 comments
Labels
enhancement New feature or request
Projects

Comments

@elpiel
Copy link
Member

elpiel commented Nov 27, 2019

In #203 we've created 2 queries for fetching the channels and the total channels count of the query itself (not paginated).
@samparsky had an idea to make it in 1 query DB Fiddle, but trying to implement this caused quite some issues with deserialization of the Channels in many ways.

Instead of investing this time right away, I am putting this idea here, so that we can investigate more later on.

Couple of notes:

  1. Channel is deserialized based on camelCase fields as well as some other restrictions like timestamp seconds for valid_until, which doesn't play well with the DB field names and the TIMESTAMP WITH TIME ZONE.
  2. We use impl From<Row> for Channel in the other cases, which cannot be used now, since we are receiving 2 rows, 1 of which is JSON of array of Channels, which already applies that we cannot use this From implementation

There are couple of hiccups as we can see, but if we need a single query in the future or it's more performant and we need the speed, we can try and solve those issues.

A simplified query:

(SELECT COUNT(id)::varchar FROM channels WHERE {where_clause}) as total_count,
            -- TODO: add ORDER BY!!!
            (SELECT json_agg(row_to_json(tt))::jsonb FROM
                (SELECT id, creator, deposit_asset as "depositAsset",
                    deposit_amount as "depositAmount",
                    EXTRACT(EPOCH FROM valid_until) as "validUntil",
                    spec
                    FROM channels WHERE {where_clause} LIMIT {limit} OFFSET {offset}
                    ORDER BY spec->>'created' DESC
                ) tt
            ) as channels
@elpiel elpiel added the enhancement New feature or request label Nov 27, 2019
@elpiel elpiel added this to To do in Sentry via automation Nov 27, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
Sentry
  
To do
Development

No branches or pull requests

1 participant