Magento – How to filter product collection with default graphql products query

First, it is not that simple, Magento database modeling is in eav (entity,attribute,value) so it requires a lot of JOINS

I recommand doing this by passing through Magento collections (in php script)

But anyway here is the MySQL query that responds to your need :

SELECT e.entity_id AS 'id',
       v1.value AS 'name',
       e.sku,
       d1.value AS 'price',
       t1.value AS 'short_description',
       t2.value AS 'description',
       v2.value AS 'image',
       v3.value AS 'thumbnail',
       mg.value AS 'media_gallery',
       cids.category_ids AS 'category_ids',
       cids.category_names AS 'category_names'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0
AND v1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'name'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
AND t1.store_id = 0
AND t1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'short_description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
AND t2.store_id = 0
AND t2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id = 0
AND v2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'image'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
AND v3.store_id = 0
AND v3.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'thumbnail'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0
AND d1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'price'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
  (SELECT m1.entity_id,
          GROUP_CONCAT(m2.value) AS value
   FROM catalog_product_entity_media_gallery_value_to_entity m1
   INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
   AND m2.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'media_gallery'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_product'))
   GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
LEFT JOIN
  (SELECT product_id,
          GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids,
          GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
   FROM catalog_category_product c
   INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
   AND cv.store_id = 0
   AND cv.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'name'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_category'))
   GROUP BY product_id) cids ON e.entity_id = cids.product_id ;

Try it and let me know if it helped you.

If I am not wrong you meant to ask What if we’re adding a custom attribute, e.g., an attribute we’ve added to the database? And how we gonna deal with making that attribute available to GraphQL. So let's proceed with an example:

Let’s assume you want to add the parent_item_id as an attribute available through the GraphQL Quote Endpoint.Now, you may already be aware, parent_item_id is an existing field (it’s not a new database attribute). But currently, it’s not supported through the native GraphQL Quote Endpoint – so it’s not accessible via GraphQL responses. We’ll walk through a simple example to add it:

First, We’ll create a module:

// File: app/code/GraphQlHandson/Example/registration.php
<?php

\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'GraphQlHandson_Example',
    __DIR__
);

Next, We’ll add our etc/schema.graphqls file:

// File: app/code/GraphQlHandson/Example/etc/schema.graphqls
interface CartItemInterface @typeResolver(class: "Magento\\QuoteGraphQl\\Model\\Resolver\\CartItemTypeResolver") {
    parent_item_id: Int
}

Now, refresh your database / cache:

php bin/magento setup:upgrade

…That’s it! On the client side, We can send a GraphQL query that looks like this:

query { cart { items { parent_item_id } } }

(Obviously in a real-world scenario, you would request additional information).

Now, the only point that confusing at first glance is the interface definition in the schema.graphqls file. …Where did we get that? Well, if you look in the native Quote graphql module:

// File>: vendor/magento/module-quote-graph-ql/etc/schema.graphqls
...
type SimpleCartItem implements CartItemInterface @doc(description: "Simple Cart Item") {
    customizable_options: [SelectedCustomizableOption] @resolver(class: "Magento\\QuoteGraphQl\\Model\\Resolver\\CustomizableOptions")
}

type VirtualCartItem implements CartItemInterface @doc(description: "Virtual Cart Item") {
    customizable_options: [SelectedCustomizableOption] @resolver(class: "Magento\\QuoteGraphQl\\Model\\Resolver\\CustomizableOptions")
}

interface CartItemInterface @typeResolver(class: "Magento\\QuoteGraphQl\\Model\\Resolver\\CartItemTypeResolver") {
    id: String!
    quantity: Float!
    product: ProductInterface!
}
...

You can see above, that by default Magento 2 does several things:

• Defines an interface CartItemInterface which is used to represent cart items.
• Defines concrete implementations of that interface for different product types.

• Defines the fields available through GraphQL for each of them.

  So, by default, the only available fields for an item are id, quantity, product — the last of which is a complex ProductInterface type

In our custom module, all we did was copy the interface definition and start adding new properties to it.

That’s an important point to remember – the GraphQL definitions function more like layout xml in Magento 2, and less like typical extension_attributes. The beauty of this is that we can freely add new types, modify queries, etc.

Hope this provide a clear glimpse on how you can modify the GraphQL queries by adding custom or new attributes. If yes, then kindly upvote it and mark it as an answer. Thanks!