Mysql query to Model, Resource Model, Collection - Magento 2

Question

I can easily get or set value using ORM. I felt difficult to convert mysql query logic to Model, Resource Model, Collection(ORM).

for example I am getting last one hour data using the below query

select * from chennai_cancel_order where cancel_date >= DATE_SUB(NOW(),INTERVAL 1 HOUR)

but I don't have idea about how to apply above logic to ORM.

Similarly more way of query is possible like order by, descending, limit..... etc.

please give some clarity about how to handle Mysql query in ORM.

yes I have created, I can able to get and set value. While getting I don't know how to apply condition like mysql query for fetching particular data — Bilal Usean
– Bilal Usean, Commented May 17, 2016 at 6:24
Why do yon not calculate DATE_SUB(NOW(),INTERVAL 1 HOUR) at client side (in php)? — KAndy
– KAndy, Commented May 17, 2016 at 6:30
do you mean using object manager use the query directly in php? — Bilal Usean
– Bilal Usean, Commented May 17, 2016 at 6:33
i mean that you can write code like $chennaiCancelOrderCollection->addFilter('cancel_date', ['gt' => new DataTime()->modify('-1 hour')->format(DateTime::ISO8601)]) — KAndy
– KAndy, Commented May 17, 2016 at 7:19

Fabian Schmengler · Accepted Answer · 2016-05-24 20:49:21Z

3

Magento 2 still can work with Zend_Db_Expr for arbitrary SQL expressions in filters. Given a collection, use addFilter for the where condition:

$chennaiCancelOrderCollection->addFilter(
    'cancel_date',
    ['gt' => new \Zend_Db_Expr('DATE_SUB(NOW(),INTERVAL 1 HOUR)']);

answered May 24, 2016 at 20:49

Fabian Schmengler

66.2k25 gold badges192 silver badges423 bronze badges

1 Answer 1