Select only necessary columns for optimized queries

[kplgr]

What problem does this feature proposal attempt to solve?
I am trying out GraphQL, testing various features in order to include it in some of the web apps I am developing with laravel. Being curious, I created a Schema and fired up all sorts of queries, followed by poking around in the SQL server to see what was going on.

What I discovered was this:

Suppose I have a Type defined which corresponds to a Model, and the database table has an ID column and 26 more columns (A, B C, ..., Z).

No matter what the Type definition is, which in my case only includes some of the columns ( say, ID and A, B, C), I can see that the SQL server is asked to fetch all 27 columns from the DB, using a SELECT * FROM [Table] statement, even though given the definition of the type at most 4 of them would be returned via the resolver.

This is an unnecessary data transfer between the DB and PHP, in my opinion.

Which possible solutions should be considered?

I propose that we construct a more refined SQL query, selecting only the columns that are requested in the GraphQL query.

I am not sure I am competent enough to try and implement this on my own, but I will give it a shot. In any case, I thought I'd share my thoughts.

[spawnia]

Technically, this can be done by constraining the SELECT to the requested fields. The fourth resolver argument ResolveInfo can be used to know which fields a client selected.

There are some pitfalls to this which require discussion:

• Fields may not match database columns 1-1
• Laravel assumes SELECT * by default for things like relations, virtual properties, etc.

[mazedlx]

Technically, this can be done by constraining the SELECT to the requested fields. The fourth resolver argument ResolveInfo can be used to know which fields a client selected.

There are some pitfalls to this which require discussion:

* Fields may not match database columns 1-1

* Laravel assumes `SELECT *` by default for things like relations, virtual properties, etc.

Would you be so awesome as to provide an example? I've searched through the docs but I can't seem to find what I am looking for :-(

Let's say I have a model Nerd with the fields glasses and beard,

type Nerd {
    glasses: Int!
    beard: String!
}

and I want to query them with

type Query {
   nerds: [Nerd] @all
}

and I only want the SQL statement to be SELECT glasses, beard FROM nerds. Thanks in advance!

[spawnia]

public function __invoke($root, array $args, GraphQLContext $context, ResolveInfo $resolveInfo)
{
    $resolveInfo->getFieldSelection();
    $resolveInfo->lookAhead();
    // TODO use the information from those methods to SELECT only the given fields
}

[mazedlx]

public function __invoke($root, array $args, GraphQLContext $context, ResolveInfo $resolveInfo)
{
    $resolveInfo->getFieldSelection();
    $resolveInfo->lookAhead();
    // TODO use the information from those methods to SELECT only the given fields
}

Thanks! Just one more stupid question: where do I put that method? On the Eloquent model?

[spawnia]

https://lighthouse-php.com/master/the-basics/fields.html

[mazedlx]

Awesome, thanks!

[canatufkansu]

This kind of feature would be a huge performance improvement for the package. I'm implementing an endpoint for multi million record table with at least 50+ columns. I'm using query builder to make search in a given bounding box (https://wiki.openstreetmap.org/wiki/Bounding_Box) in PostgreSQL also implemented complex where conditions to filter the results. Only issue is Laravel generates SELECT * queries which affects the performance. I could use custom resolvers but when I do that I have to deal with pagination.

Following is my current Query schema:

listings(bbox: BoundingBoxInput! @builder(method: "App\\GraphQL\\Builders\\BoundingBoxSearch@bbSearch"), where: _ @whereConditions(columnsEnum: "ListingColumn")) : [Listing!]! @middleware(checks: ["auth:api"]) @paginate(maxCount: 50 defaultCount: 10)

Is there any way I can modify select columns without writing custom resolver ? Because by this way with very little code I can do so much.

[spawnia]

I am not planning to implement such functionality in Lighthouse myself, but am open to accepting PRs.

[canatufkansu]

I am not planning to implement such functionality in Lighthouse myself, but am open to accepting PRs.

I understand the complexity and side affects like you mentioned above about relations and virtual properties. It would be nice to have some way to control select columns like in query builder @builder directive. Thanks, even like this Lighthouse is very useful.

[spawnia]

Just noticed this is a duplicate of #1214. An implementation attempt was also started #1253