Is the count operation for limiting queries necessary?

[ahokinson]

My understanding is that pagination and limiting should be enabled by using the SQLAlchemyConnectionField on a Connection type.

Running a query such as,

{
  facts(first: 1) {
    edges {
      node {
        id
        appid
        pageid
      }
      cursor
    }
    pageInfo {
      endCursor
      hasNextPage
    }
  }
}

yields a SQL query without LIMIT %s, %s in the SELECT statement which is suggested to be the case in #27. Specifically, the generated query is,

SELECT count(*) AS count_1 
FROM (SELECT "TABLE".id AS "TABLE_id", "TABLE".appid AS "TABLE_appid", "TABLE".pageid AS "TABLE_pageid" 
FROM "TABLE" ORDER BY "TABLE".id ASC) AS anon_1

My schema is pretty straight forward,

from graphene import Connection, ObjectType, Schema, relay
from graphene_sqlalchemy import SQLAlchemyConnectionField, SQLAlchemyObjectType

from models.generated.fact import Fact as FactModel


class Fact(SQLAlchemyObjectType):
    class Meta:
        model = FactModel
        interfaces = (relay.Node,)


class FactConnection(Connection):
    class Meta:
        node = Fact


class Query(ObjectType):
    node = relay.Node.Field()
    facts = SQLAlchemyConnectionField(FactConnection)


schema = Schema(query=Query)

and my model (while generated) is,

class Fact(Base):
    __tablename__ = "TABLE"
    id = Column(Integer, primary_key=True)
    appid = Column(Integer)
    pageid = Column(String)

Am I missing a step or is this possibly a bug? I am using graphene-sqlalchemy 2.1.0.

[ahokinson]

So I actually understand what's going on here from re-reading @ornj's comment on #27.

You will see a query to get a count of the number of total records so that it can provide additional paging information.

I have a GIANT database with more than 250 TB of data and this model is mostly flat... Why is that count operation necessary? Can I disable it?

[ornj]

It's been a long time since I've used this, but the count(*) query is to satisfy arguments required by connection_from_list_slice. I'd maybe look into providing your own Connection that implements the logic you need rather than using the default "good enough" implementation.

[ahokinson]

Thanks for that, @ornj. Do you know if it is possible for the subselection being counted to be filtered?

Like from above, only with some WHERE clause?

SELECT count(*) AS count_1 
FROM (SELECT "TABLE".id AS "TABLE_id", "TABLE".appid AS "TABLE_appid", "TABLE".pageid AS "TABLE_pageid" 
FROM "TABLE" WHERE ... ORDER BY "TABLE".id ASC) AS anon_1

I guess my question is, can connection fields be filtered?

[ornj]

Anything is possible when you're defining your own classes. I believe graphene behaves more or less like reference Javascript implementation does and then graphene_sqlalchemy is providing a pretty basic integration. It's querying for everything and filtering / slicing the results in the application layer. For most usages I'm sure this fine.

The resolver methods receive an info argument. Again, it's been awhile-- but assuming behavior along the lines of the Javascript implementation this should be a dictionary or object that contains information about the received query including a parsed AST from which you can extract the filters. and build your query with a WHERE clause.

[github-actions]

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related topics referencing this issue.