We would get an idea of how to implement GraphQL schema for a custom table to fetch more than one result row.

Multiple result row

Let’s say we have a module Mage2_Person. This module will define the GraphQL schema to fetch multiple data results.

Step 1: Create a module.xml file
File Path: Mage2/Person/etc/module.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Mage2_ProductDocs">
        <sequence>
            <module name="Magento_Ui"/>
            <module name="Magento_GraphQl"/>
        </sequence>
    </module>
</config>

Step 2: Create db_schema.xml file
File Path: Mage2/Person/etc/db_schema.xml

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="mage2_person" resource="default" engine="innodb" comment="Mage2 Person">
        <column name="person_id" xsi:type="int" padding="11" unsigned="true" identity="true" nullable="false" comment="Person ID"/>
        <column name="name" xsi:type="varchar" length="255" nullable="false" comment="Person Name"/>
        <column name="age" xsi:type="int" padding="10" nullable="false" comment="Person Age"/>
        <column name="sort_order" xsi:type="int" padding="10" unsigned="true" nullable="false" identity="false" default="0" comment="Sort Order"/>
        <column name="created_at" xsi:type="timestamp" default="CURRENT_TIMESTAMP" comment="Created At"/>

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="person_id"/>
        </constraint>
        <index referenceId="MAGE2_PERSON_NAME" indexType="fulltext">
            <column name="name"/>
        </index>
    </table>
</schema>

Step 3: Create schema.grapgqls file
File Path: Mage2/Person/etc/schema.graphqls
This file is the main file to tell Magento this is the definition of a custom table. Define GraphQL schema for the custom table and its columns. The naming convention of schema definition would not be considered, but make sure the syntax of each definition and property should be followed. You need to define the Resolver path to the main query. In the following example. the resolver path would be: “Mage2\\Person\\Model\\Resolver\\Person”

type Query {
    person (
        name: String @doc(description: "Person name")
    ): Person @resolver(class: "Mage2\\Person\\Model\\Resolver\\Person") @doc(description: "The Person query returns information about Person")
}

type PersonInterface @doc(description: "Person grapgql gather of specific person") {
    person_id: Int @doc(description: "Person Id of person")
    name: String @doc(description: "Person name")
    age: String @doc(description: "Person age")
    sort_order: Int @doc(description: "Position of person")
    created_at: String @doc(description: "Date time when person added by admin")
}

type Person @doc(description: "Person grapgql gather of all specific person") {
    totalCount: Int @doc(description: "Total number of person")
    items: [PersonInterface] @doc(description: "An array of Person")
}

Step 4: Create Resolver Class file
File Path: Mage2/Person/Model/Resolver/Person.php
The main logic of retrieving data from a custom table will go here. You can create a separate class file for more logic and call that class file in the Resolver and return data which actually you receive in the result. Here I have provided a small example, that’s why I have not created a separate class file.

<?php
declare(strict_types=1);

namespace Mage2\Person\Model\Resolver;

use Magento\Framework\Exception\NoSuchEntityException;
use Magento\Framework\GraphQl\Config\Element\Field;
use Magento\Framework\GraphQl\Exception\GraphQlInputException;
use Magento\Framework\GraphQl\Exception\GraphQlNoSuchEntityException;
use Magento\Framework\GraphQl\Query\ResolverInterface;
use Magento\Framework\GraphQl\Schema\Type\ResolveInfo;
use Mage2\Person\Model\ResourceModel\Person\CollectionFactory;

class Person implements ResolverInterface
{
    private $personCollectionFactory;

    public function __construct(
        CollectionFactory $personCollectionFactory
    ) {
        $this->personCollectionFactory = $personCollectionFactory;
    }

    public function resolve(
        Field $field,
        $context,
        ResolveInfo $info,
        array $value = null,
        array $args = null
    ) {
        $this->checkArguments($args);
        $personData = $this->getPersonData($args);

        return $personData;
    }

    private function checkArguments(array $args): array
    {
        if (!isset($args['name'])) {
            throw new GraphQlInputException(__('"person name should be specified'));
        }

        return $args;
    }

    private function getPersonData($args) {
        $items = [];
        try {
            $collection = $this->personCollectionFactory->create();
            $collection->addFieldToFilter('name', ['like' => '%' . $args['name'] . '%'])
                ->setOrder('sort_order', 'ASC');

            $totalCount = $collection->getSize();

            if ($totalCount > 0) {
                $items = $collection->getData();
            }
        }
        catch (NoSuchEntityException $e) {
            throw new GraphQlNoSuchEntityException(__($e->getMessage()), $e);
        }
        $personData = ['totalCount' => $totalCount, 'items' => $items];
        return $personData;
    }
}

Input Query:
You can test your GrahpQL query in ChromeiQL or Altair GraphQL addon in Chrome browser as follows:

query getPersonData($name: String!) {
    person(name: $name) {
    totalCount
    items {
        person_id
        name
        age
        sort_order
        created_at
    }
    }
}

Input Variables:
{"name": "Daisy"}

Output Result:

{
    "data": {
	"person": {
	    "totalCount": 3,
	    "items": [
		{
		    "person_id": 3,
		    "name": "Daisy Paul",
		    "age": 20,
		    "sort_order": 1,
		    "created_at": "2021-04-15 12:27:39"
		},
		{
		    "person_id": 2,
		    "name": "Daisy Cena",
		    "age": 25,
		    "sort_order": 2,
		    "created_at": "2021-04-15 12:28:17"
		},
		{
		    "person_id": 1,
		    "name": "Harper Daisy",
		    "age": 18,
		    "sort_order": 3,
		    "created_at": "2021-04-15 12:25:28"
		}
	    ]
	}
    }
}

Single result row

If you want to fetch a single data row in search results, you can review this blog: Define GraphQL schema for a custom table – Single Row

We hope this blog may understandable and useful to you. You can email us at mage2developer@gmail.com if we missed anything or want to add any suggestions. We will respond to you as soon as possible. Happy to help 🙂