1

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?

0

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);
?>
  • I added that afterGet method and I tried issuing my request like http://magento.test/rest/V1/invoices/?searchCriteria[filter_groups][0][filters][0][field]=exportedFlag&searchCriteria[filter_groups][0][filters][0][value]=null&searchCriteria[filter_groups][0][filters][0][condition_type]=neq, but I get the error Column not found: 1054 Unknown column 'exportedFlag' in 'where clause', query was: SELECT main_table.* FROM sales_invoice` AS main_table WHERE ((exportedFlag != 'null'))` – Ben Rubin Apr 11 at 13:10
  • Is there a way to tell Magento to join sales_invoice to sales_invoice_exported_flag for these queries? – Ben Rubin Apr 11 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.