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