Skip to content

Single query for fetching Channels in list endpoint #206

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

    Fields

    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions