How do I query on an extension attribute through the API?

Question

I added an extension attribute to the Invoice class, and I want to be able to query the Invoice database data by my extension attribute.

My extension attribute (ExportedFlag) has these properties

• entity_id The primary key
• invoice_id The foreign key to the invoice table.
• isExported A string value.

When an invoice gets created, it will not have a corresponding row in my extension attribute table. I want to be able to query the database for a list of invoices that don't have a value for the extension attribute, using the API (and then insert the value for that extension attribute).

Here's the code that I have right now that adds the extension attribute to invoices.

public function afterGetList
(
    \Magento\Sales\Api\InvoiceRepositoryInterface $subject,
    \Magento\Sales\Model\ResourceModel\Order\Invoice\Collection $searchResult
) {
    /** @var \Magento\Catalog\Api\Data\InvoiceInterface $invoice */
    foreach ($searchResult as $invoice) {
        $this->addExportedFlagToInvoice($invoice);
    }
    return $searchResult;
}  

private function addExportedFlagToInvoice(\Magento\Sales\Api\Data\InvoiceInterface $invoice)
{
    $extensionAttributes = $invoice->getExtensionAttributes();

    if (empty($extensionAttributes)) {
        $extensionAttributes = $this->invoiceExtensionFactory->create();
    }

    $exportedFlag = $this->exportedFlagProvider->getExportedFlag($invoice->getEntityId());
    $extensionAttributes->setExportedFlag($exportedFlag);
    $invoice->setExtensionAttributes($extensionAttributes);

    $exportedFlag = $extensionAttributes->getExportedFlag();
    $exportedFlag->setInvoiceId($invoice->getEntityId());
    $this->entityManager->save($exportedFlag);

    return $this;
}

The problem is that this code iterates through each invoice, and adds the extension attribute one-by-one. Furthermore, it retrieves all invoices, regardless of whether or not they have a value for the extension attribute (I don't want to retrieve values that have a row in the extension attributes table).

I want to be able to retrieve all invoices in a single query. Something like this

SELECT *
FROM sales_order_invoice
LEFT OUTER JOIN extensionAttribute
   ON sales_order_invoice.entity_id = extensionAttribute.invoice_id
WHERE extensionAttribute.isExported IS NULL

How can I do this within Magento?

Answer 1

step 1) You may need to create an afterGet function in your invoice plugin class

 public function afterGet
(
    \Magento\Sales\Api\InvoiceRepositoryInterface $subject,
    \Magento\Sales\Api\Data\InvoiceInterface $invoice
) {

    $this->addExportedFlagToInvoice($invoice);
    return $invoice;
}

Step 2) you can use searchCriteria array on your API request as below

<?php
$magentoSiteUrl = 'www.yoursite.com';
$userData = array("username" => 'USERNAME', "password" => 'PASSWORD');
define('MAGENTO_SITE_URL',$magentoSiteUrl);
$ch = curl_init($magentoSiteUrl."/index.php/rest/V1/integration/admin/token");
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($userData));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json", "Content-Lenght: " . strlen(json_encode($userData)))); 
$token = curl_exec($ch); // get access Token, use it for API c



//API Search Criteria
$searchCriteria = 'searchCriteria[filter_groups][2][filters][2][field]=exported_flag&'; // change field name as per need
$searchCriteria .= 'searchCriteria[filter_groups][2][filters][2][condition_type]=neq&'; // change condition per need
$searchCriteria .= 'searchCriteria[filter_groups][2][filters][2][value]=null';          // change condition value 

$ch = curl_init(MAGENTO_SITE_URL."/index.php/rest/V1/orders/?".$searchCriteria);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json", "Authorization: Bearer " .      json_decode($accToken)));
$result = curl_exec($ch);
?>