Convert SQL query to Laravel way(unix timestamp problem!!)

Question

I'm trying to conver this SQL query to Laravel query.

SELECT count(*) FROM (SELECT order_id FROM table1 WHERE app_process='7' AND ( service_type='lite' OR ((end_time-".time().")/86400)>'0') ) a INNER JOIN (SELECT order_id FROM table2 WHERE process='1' AND amount>'0' GROUP BY order_id) b ON a.order_id=b.order_id

I almost success(?) to converting but I don't know how to convert the time part.

end_time-".time().")/86400

what I converted

Db::table('table1 as A')
->select('A.order_id')
->where('A.app_process', '=', '7')
->where('A.service_type', '=', 'lite') 
->orWhere('A.end_time', '>', '0')  <== problem here!!
->join(Db::raw('(select order_id from table2 where process = 1 and amount > 0 group by order_id) B'), 'B.order_id', '=', 'A.order_id')
->count();

Could someone help me to solve the time part?

What are you trying to achieve with the time part? It looks like it can be much simpler. — Jerodev, Aug 20 '19 at 11:00
Hope this will be helpful .[Stackoverflow question](https://stackoverflow.com/questions/23328301/laravel-eloquent-left-join-where-null) — farooq, Aug 20 '19 at 11:05
@Jerodev I'm not sure since I'm not the one who wrote the sql query, but the 'end_time' is unix timestamp. So, I think it could be getting days now from the end_time. — Jiwon, Aug 20 '19 at 11:05
@farooq I'm sorry but I can't find specific helpful things there... — Jiwon, Aug 20 '19 at 11:09

score 1 · Answer 1 · answered Aug 20 '19 at 11:07

1

As far as I understand the query, it just checks if end_time is in the future. So you can just do the following:

->orWhere('A.end_time', '>', now())

now() is a Laravel helper that returns the current datetime.

answered Aug 20 '19 at 11:07

Jerodev

32,252
11
87
108

The result is the same as zero... hmm... maybe my converting query is not right. Can you look at the query overall? – Jiwon Aug 20 '19 at 11:13

score 1 · Accepted Answer · answered Aug 23 '19 at 01:18

I finally found out the right query. It turns out the problem was not only just 'time()' but also the wrong converted query. I post this maybe help someone.

Db::table('table1 as A')
->leftJoin('table2 as B', 'A.order_id', '=', 'B.order_id')
->where('A.app_process', '=', '7')
->where(function($query){
  $query->where('A.service_type', '=', 'lite')->orWhere('A.end_time', '>', time());
})
->where('B.process', '=', '1')
->where('B.amount', '>', '0')
->distinct()
->count('A.order_id');

score 0 · Answer 3 · answered Aug 20 '19 at 11:40

0

try this I think it helps you

Db::table('table1 as A')
->select('A.order_id')
->where([['A.app_process', '=', '7'],['A.service_type', '=', 'lite']])
->orWhere('A.end_time', '>', now())
->join(Db::raw('(select order_id from table2 where process = 1 and amount > 0 group by order_id) B'), 'B.order_id', '=', 'A.order_id')
->count();

answered Aug 20 '19 at 11:40

Nurbek Boymurodov

542
3
11

@Boymurodov you mean the 'end_time'? It's unix timestamp. The values like 1366800497. The field type set as integer in the database table. – Jiwon Aug 20 '19 at 15:37
try this carbon class $time = Carbon::createFromTimestamp($timestamp); $timestamp is your unix time – Nurbek Boymurodov Aug 21 '19 at 06:26
Sorry, but where could I try the '$time'? – Jiwon Aug 21 '19 at 06:38
are you going to convert from unixtime to date or from date to unix – Nurbek Boymurodov Aug 21 '19 at 07:28
I don't understand what you mean. I just want to get the values exactly the same value as the original query. For example, 1413385199(end_time value) minus 1566442100(time value) and divide by 86400 is a negative 1771. But I don't know how can I subtract the time() value in the laravel query. – Jiwon Aug 22 '19 at 02:54

Convert SQL query to Laravel way(unix timestamp problem!!)

3 Answers3