文章彙整

如何在Magento2中的searchCriteria filter加入”OR”條件

Astral WebBy Astral Web 2 weeks agoNo Comments
首頁  /  Magento  /  Magento-2  /  如何在Magento2中的searchCriteria filter加入”OR”條件

如何在Magento2中的searchCriteria filter加入”OR”條件

在建立module或客製化功能時,一定常常都會需要對資料庫操作,其中也不免的會需要下各種的搜尋條件,不論是單純的一個where,抑或是OR、AND,再搭配其他語法達到自己想要的結果。
那相信會點到這篇文章的各位,一定是對於M2中要如何使用”OR”條件感到有所疑惑, 當然大家可以直接編寫raw sql來query,不過以下我們還是以M2的ORM來說明及操作。

測試實作

我們會以會員當作例子並使用customerRepository及customerRepository的getList()方法傳入searchCriteria來做測試。

前置作業

由於Repository並不像Collection可以直接getSelect()拿到語法,不過我們知道Repository底層也是Collection的操作˙所以我們先將customerRepository中的getList()方法稍微修改一下讓他可以直接丟出Collection的MySQL query語法。

public function getList(SearchCriteriaInterface $searchCriteria)  
{
	 $searchResults = $this->searchResultsFactory->create();  
	 $searchResults->setSearchCriteria($searchCriteria);  
  /** @var \Magento\Customer\Model\ResourceModel\Customer\Collection $collection */  
	 $collection = $this->customerFactory->create()->getCollection();  
	//        $this->extensionAttributesJoinProcessor->process(  
	//            $collection,  
	//            CustomerInterface::class  
	//        );  
	//        // This is needed to make sure all the attributes are properly loaded  
	//        foreach ($this->customerMetadata->getAllAttributesMetadata() as $metadata) {  
	//            $collection->addAttributeToSelect($metadata->getAttributeCode());  
	//        }  
	//        // Needed to enable filtering on name as a whole  
	//        $collection->addNameToSelect();  
	//        // Needed to enable filtering based on billing address attributes  
	//        $collection->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')  
	//            ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')  
	//            ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')  
	//            ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')  
	//            ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left')  
	//            ->joinAttribute('billing_company', 'customer_address/company', 'default_billing', null, 'left');  
	 
	 //以上是將customer相關表格及extensionAttributes加入語法中,不過我們為了簡單所以全部註解掉
	 $this->collectionProcessor->process($searchCriteria, $collection);  
	 
	 echo $collection->getSelect();exit; //主要加上這一行丟出語法
}

實際測試

OK,稍微調整了repository的getList()方法後,現在再來我們來試試並比較一下以下兩種結果

searchCriteriaBuilder->addFilter

public function __construct(  
	 Session $session,  
	 Context $context,  
	 SearchCriteriaBuilder $searchCriteriaBuilder,  
	 \Magento\Customer\Api\CustomerRepositoryInterface $customerRepository,  
	 \Magento\Framework\Api\FilterBuilder $filterBuilder,  
	 \Magento\Framework\Api\Search\FilterGroupBuilder $filterGroupBuilder  
) {  
	 $this->session = $session;  
	 $this->customerRepository = $customerRepository;  
	 $this->searchCriteriaBuilder = $searchCriteriaBuilder;  
	 $this->filterBuilder = $filterBuilder;  
	 $this->filterGroupBuilder = $filterGroupBuilder;  
	 return parent::__construct($context);  
}

public function execute()  
{
	$searchCriteria = $this->searchCriteriaBuilder  
	    ->addFilter('entity_id', 1, 'eq')  
	    ->addFilter('entity_id', 2, 'eq')->create();

	$this->customerRepository->getList($searchCriteria);
	exit;
}

結果

SELECT `e`.* FROM `customer_entity` AS `e` WHERE ((`e`.`entity_id` = 1)) AND ((`e`.`entity_id` = 2))

這邊我們可以看到sql語法中間是用”AND”來連接


searchCriteriaBuilder->setFilterGroups

public function __construct(  
	 Session $session,  
	 Context $context,  
	 PageFactory $resultPageFactory,  
	 ApiResourceInterface $apiResource,  
	 SearchCriteriaBuilder $searchCriteriaBuilder,  
	 \Magento\Customer\Api\CustomerRepositoryInterface $customerRepository,  
	 \Magento\Framework\Api\FilterBuilder $filterBuilder,  
	 \Magento\Framework\Api\Search\FilterGroupBuilder $filterGroupBuilder  
) {  
	 $this->session = $session;  
	 $this->apiResource = $apiResource;  
	 $this->resultPageFactory = $resultPageFactory;  
	 $this->customerRepository = $customerRepository;  
	 $this->searchCriteriaBuilder = $searchCriteriaBuilder;  
	 $this->filterBuilder = $filterBuilder;  
	 $this->filterGroupBuilder = $filterGroupBuilder;  
	 return parent::__construct($context);  
}

public function execute()  
{
    $entityFilter = $this->filterBuilder  
	    ->setField('entity_id')  
	    ->setConditionType('eq')  
	    ->setValue(1)  
	    ->create();  
  
    $entityFilter2 = $this->filterBuilder  
	    ->setField('entity_id')  
	    ->setConditionType('eq')  
	    ->setValue(2)  
	    ->create();  
  
    $filterGroup1 = $this->filterGroupBuilder  
	    ->addFilter($entityFilter)  
	    ->addFilter($entityFilter2)  
	    ->create();  
  
    $searchCriteria = $this->searchCriteriaBuilder  
	    ->setFilterGroups([$filterGroup1])  
	    ->create();
	$this->customerRepository->getList($searchCriteria);
	exit;
}

結果

SELECT `e`.* FROM `customer_entity` AS `e` WHERE ((`e`.`entity_id` = 1) OR (`e`.`entity_id` = 2))

可以看到,這時候where條件用的是”OR”。

且我們還發現OR左右的兩個條件,外層還有一個括號把兩個條件包在一起,
整個語法邏輯符合且對應程式碼中先設定兩個Filter並組合成一個Filter Group。


這時候一定有人發現

 $searchCriteria = $this->searchCriteriaBuilder  
    ->setFilterGroups([$filterGroup1])  
    ->create();

這裡丟進setFilterGroup去的是array型別,那如果我們在多塞一個在array中呢?
我們現在就來實際測試看看:

public function execute()  
{
	//group1
    $entityFilter = $this->filterBuilder  
	    ->setField('entity_id')  
	    ->setConditionType('eq')  
	    ->setValue(1)  
	    ->create();  
  
    $entityFilter2 = $this->filterBuilder  
	    ->setField('entity_id')  
	    ->setConditionType('eq')  
	    ->setValue(2)  
	    ->create();  
    
    $filterGroup1 = $this->filterGroupBuilder  
	    ->addFilter($entityFilter)  
	    ->addFilter($entityFilter2)  
	    ->create();  
	    
	//group2
	$websiteFilter = $this->filterBuilder  
		->setField('website_id')  
		->setConditionType('eq')  
		->setValue(1)  
		->create();  
  
	$storeFilter = $this->filterBuilder  
		->setField('store_id')  
		->setConditionType('eq')  
		->setValue(1)  
		->create();  

	$filterGroup2 = $this->filterGroupBuilder  
		->addFilter($websiteFilter)  
		->addFilter($storeFilter)  
		->create();
  
    $searchCriteria = $this->searchCriteriaBuilder  
	    ->setFilterGroups([$filterGroup1, $filterGroup2])  
	    ->create();
	$this->customerRepository->getList($searchCriteria);
	exit;
}

結果

SELECT `e`.* FROM `customer_entity` AS `e` WHERE ((`e`.`entity_id` = 1) OR (`e`.`entity_id` = 2)) AND ((`e`.`website_id` = 1) OR (`e`.`store_id` = 1))

很聰明的你們一定也猜到了,他會把每個Group之間用AND來連接


現在就快把以上的範例實際運用在各位的實際情境中吧!
最後,別忘了把在前置作業時customerRepository中,修改的getList()方法復原唷。

喜歡我們的文章嗎?請務必訂閱訂閱歐斯瑞電子報,以及追蹤我們的臉書粉絲團Instagram喔!

以上內容由Astralweb 歐斯瑞編寫製作

 000

推薦文章

Categories:
  Magento-2MagentoMagento開發

留下回應

你的電子郵件地址不會被公開.

取得獨家電子商務祕技

建立更好的策略靈感

跟上全球的網路趨勢

絕佳的電商解決方案

電子商務戰略全指南

每月發送電商戰略指南,只要填寫E-mail即可訂閱!

請到您的信箱確認,即可完成訂閱。