Skip to content

Single query for fetching Channels in list endpoint #206

@elpiel

Description

@elpiel

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions