Skip to content

GraphQL Read Provider generates redundant SQL query #5914

Closed
@bkosun

Description

@bkosun

API Platform version(s) affected: 3.2.0, 3.2.1

Description
GraphQL Read Provider generates redundant SQL query, here is a simple example:

Entity:

class User
{
    #[ORM\Id]
    private Uuid $id;
    
    #[ORM\Column(type: "string", length: 32, unique: true)]
    private string $username;
     
    #[ORM\ManyToOne(targetEntity: Tariff::class, inversedBy: "users")]
    private ?Tariff $tariff = null;
    
    #[ORM\OneToMany(mappedBy: "user", targetEntity: Invoice::class)]
    private iterable $invoices;
    
    #[ORM\OneToMany(mappedBy: "user", targetEntity: Order::class)]
    private iterable $orders;
     
    #[ORM\OneToMany(mappedBy: "user", targetEntity: ResetPasswordRequest::class)]
    private iterable $resetPasswordRequests;
     
    #[ORM\OneToMany(mappedBy: "user", targetEntity: EmailVerificationRequest::class)]
    private iterable $emailVerificationRequests;
}

GraphQL query:

query getUser($id: ID!) {
  user(id: $id) {
    id
    username
    tariff {
      name
    }
    orders {
      totalCount
    }
  }
}

Result:

event_listeners_backward_compatibility_layer: true

SELECT
  ...
FROM
  "user" u0_
  LEFT JOIN tariff t1_ ON u0_.tariff_id = t1_.id
  LEFT JOIN order o2_ ON u0_.id = o2_.user_id
WHERE
  u0_.id = ?

event_listeners_backward_compatibility_layer: false

SELECT
  ...
FROM
  "user" u0_
  LEFT JOIN tariff t1_ ON u0_.tariff_id = t1_.id
  LEFT JOIN order o2_ ON u0_.id = o2_.user_id
  LEFT JOIN invoice i3_ ON u0_.id = i3_.user_id
  LEFT JOIN reset_password_request r4_ ON u0_.id = r4_.user_id
  LEFT JOIN email_verification_request e5_ ON u0_.id = e5_.user_id
  ...
WHERE
  u0_.id = ?

As a result of executing such an SQL query, the fields of all related tables will be retrieved, although this is not required. This behavior may cause additional load on the database

How to reproduce

  1. Enable/Disable event_listeners_backward_compatibility_layer (true/false)
  2. Run a similar GraphQL query
  3. Examine the generated SQL query

Possible Solution
Currently normalization context is used exclusively for retrieving collections:

if ($this->serializerContextBuilder) {
// Builtin data providers are able to use the serialization context to automatically add join clauses
$context += $this->serializerContextBuilder->create($operation->getClass(), $operation, $context, true);
}

Use normalization context to retrieve the item as before:

$normalizationContext = $this->serializerContextBuilder->create($resourceClass, $operation, $context, true);

Additional Context
#5657

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions