Magento 2.2.5: how to query in observer best practice

Question

I'm looking for best practice on how to query to database in observer.

Ok so this is the idea, i use observer to catch event catalog_product_save_after (which will be fired after product is saved in admin page), after that, i get product id, and query to database to get data. Then after some code, i will update data to database.

I've searched a lot, but what i found is just some direct sql solution (which i don't think is good practice for this). Here is what i've found:

• Magento 2 direct sql query for insert not working
• magento 2 , write custom query in customer_address_entity, We can't save the address
• Magento 2: join custom table with product collection while sql query is running fine

(i was not try the third one)

Ok so here is what i do so far:

events.xml

<?xml version="1.0"?>
<!--
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Event/etc/events.xsd">
    <event name="sales_model_service_quote_submit_success">
        <observer name="sendEmailCustom" instance="Synergy\Butility\Observer\Quote\Webapi\SubmitObserver" />
    </event>
     <event name="catalog_product_save_after">
        <observer name="saveProduct" instance="Synergy\Butility\Observer\ProductSaveObserver" />
    </event>
</config>

ProductSaveObserver.php

<?php

namespace Synergy\Butility\Observer;

use Magento\Framework\Event\ObserverInterface;

class ProductSaveObserver implements ObserverInterface
{    
    public function __construct(
        \Magento\Framework\App\ResourceConnection $resource
    ) {
        $this->_resource = $resource;
    }

    public function execute(\Magento\Framework\Event\Observer $observer)
    {
        $productId = $observer->getProduct()->getId();

        $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
        $resource = $objectManager->get('\Magento\Framework\App\ResourceConnection');
        $connection = $resource->getConnection();
        $tableName = $resource->getTableName('catalog_product_flat_32');

        $product_query = "SELECT * FROM " . $tableName . " WHERE entity_id = " . $productId;

        $product = $connection->query($product_query);
        foreach ($product as $data) {
            var_dump($product->getData());
        }
        exit;
        // $sql = "UPDATE " . $tableName . " SET customer_type = '" . $this->getCustomerType() . "'  WHERE entity_id = " . $this->getEntityId($observer);
        // $connection->query($sql);
    }   
}

Thanks for reading :)

Answer 1

Ok so my solution is not really answer for this question, but it helped me, if some one is going to my situation, i hope this answer will help them
So this answer is nearly answer the question "how to update custom attribute using observer after product saved".
Take a look on this link and you will find out:
https://webkul.com/blog/mass-update-products-attribute-magento-2/
So basically if you follow this solution, you don't need to use query in observer anymore

Here is how i did:

ProductSaveObserver.php

<?php

namespace Synergy\Butility\Observer;

use Magento\Framework\Event\ObserverInterface;

class ProductSaveObserver implements ObserverInterface
{    
    public function __construct(
        \Magento\Framework\App\ResourceConnection $resource
    ) {
        $this->_resource = $resource;
    }

    public function execute(\Magento\Framework\Event\Observer $observer)
    {
        // AB-18 update news_to_day take data from news_to_date in catalog_product_flat_32 table after "saved product" in admin page
        $productId = $observer->getProduct()->getId();

        $productIdArr = [$productId];

        $news_to_date = $observer->getProduct()->getNewsToDate();
        $news_to_date = substr($news_to_date,0,-8);
        $news_to_date = $news_to_date.'00:00:00';

        $objectManager = \Magento\Framework\App\ObjectManager::getInstance();

        $productActionObject = $objectManager->create('Magento\Catalog\Model\Product\Action');
        $productActionObject->updateAttributes($productIdArr, array('news_to_day' => $news_to_date), 0);
    }
}

Answer 2

I have a bit cleaner version for you

<?php

namespace Synergy\Butility\Observer;

use Magento\Catalog\Model\Product;
use Magento\Catalog\Model\ProductRepository;
use Magento\Framework\Event\Observer;
use Magento\Framework\Event\ObserverInterface;
use Magento\Framework\Exception\CouldNotSaveException;
use Magento\Framework\Exception\InputException;
use Magento\Framework\Exception\StateException;

class ProductSaveObserver implements ObserverInterface
{
    private const NEWS_TO_DAY = 'news_to_day';
    
    /** @var ProductRepository */
    private $productRepository;

    /**
     * ProductSaveObserver constructor.
     * @param ProductRepository $productRepository
     */
    public function __construct(
        ProductRepository $productRepository
    )
    {
        $this->productRepository = $productRepository;
    }

    /**
     * @param Observer $observer
     * @throws CouldNotSaveException
     * @throws InputException
     * @throws StateException
     */
    public function execute(Observer $observer): void
    {
        /** @var Product $product */
        $product = $observer->getProduct();

        // i believe this is a custom attribute in a product, news_to_day
        $newsToDate = $product->getData(self::NEWS_TO_DAY);
        $newsToDate = substr($newsToDate, 0, -8);
        $newsToDate = $newsToDate . '00:00:00';

        $product->setCustomAttribute(self::NEWS_TO_DAY, $newsToDate);

        // a repository should save a product entity
        $this->productRepository->save($product);
    }
}

Please never use this type of code in your scripts, it is really bad

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

Please read this article as to how to create a new instance in magento2 http://www.rosenborgsolutions.com/magento-new-class-instance.php