5

How to get case sensitive search result using addFieldToFilter() of collection object in magento.

I want to get search result based on case sensitive search results. Like ABCDE12 is not equal to ABCde12. I am trying to get case sensitive search result from collection.

$couponCode = 'ABCde12';
$getCoupon = $this->couponFactory->create()->getCollection()
                ->addFieldToSelect(array('coupon_code','description'))
                ->addFieldToFilter('coupon_code',array('eq' => $couponCode))->getFirstItem();

In database ABCde12 and ABCDE12 both entry are created. I just want to fetch case sensitive record from table.

Any help would be Appreciated.

4

Yeah I faced the issue one day in Magento 1.

The problem is that, with MySQL:

String comparisons normally are case-insensitive, so you can specify the name as 'bowser', 'BOWSER', and so forth. The query result is the same.

Source: MySQL documentation

In order to avoid that you need to use the BINARY operator

However, I haven't found a way to handle it properly with Magento, so here's the hacky way of doing it:

$getCoupon = $this->couponFactory->create()->getCollection()
                ->addFieldToSelect(array('coupon_code','description'))
                ->addFieldToFilter('coupon_code',array('eq' => $couponCode));

Don't get the first item. You first retrieve the where part of the query

$where = $getCoupon->getSelect()->getPart('where');

Then you create a new where part and you inject the BINARY operator before the value that you're searching.

$newWhere = str_replace('\''.$couponCode.'\'','BINARY \''.$couponCode.'\'', $where);

Then you set the where part with the new where part you just created

$getCoupon->getSelect()->setPart('where', $newWhere);

Finally you can get the first item

$coupon = $getCoupon->getFirstItem();
  • getPart('where') i doesn't understand what to do with collection? – Rakesh Jesadiya Jun 1 '16 at 12:30
  • @Rakesh getPart('where') retrieves the WHERE statement of the SQL query that retrieves the collection. Example: For the following query: select * from table where column = 'value' calling getPart('where') will give you column = 'value' – Raphael at Digital Pianism Jun 1 '16 at 12:32
  • we must have use getPart('where') always OR other are acceptable, like getTest('where')? – Rakesh Jesadiya Jun 1 '16 at 12:36
  • @Rakesh the parts are automatically set via the addFieldToSelect() / addFieldToFilter methods – Raphael at Digital Pianism Jun 1 '16 at 12:38
  • Why you don't use $getCoupon->getSelect()->where('BINARY coupon_code = (?)', $couponCode); – Pavel Adrian Feb 20 '18 at 13:33

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.