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
– Jerodev, Commented Aug 20, 2019 at 11:00
@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. — Julian
– Julian, Commented Aug 20, 2019 at 11:05
@farooq I'm sorry but I can't find specific helpful things there... — Julian
– Julian, Commented Aug 20, 2019 at 11:09

Jerodev · Accepted Answer · 2019-08-20 11:07:58Z

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, 2019 at 11:07

Jerodev

33.3k11 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julian Over a year ago

The result is the same as zero... hmm... maybe my converting query is not right. Can you look at the query overall?

Julian · Accepted Answer · 2019-08-23 01:18:51Z

1

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');

answered Aug 23, 2019 at 1:18

Julian

4495 silver badges23 bronze badges

Comments

Nurbek Boymurodov · Accepted Answer · 2019-08-20 11:40:13Z

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, 2019 at 11:40

Nurbek Boymurodov

5423 silver badges11 bronze badges

5 Comments

Julian Over a year ago

@Boymurodov you mean the 'end_time'? It's unix timestamp. The values like 1366800497. The field type set as integer in the database table.

Nurbek Boymurodov Over a year ago

try this carbon class $time = Carbon::createFromTimestamp($timestamp); $timestamp is your unix time

Julian Over a year ago

Sorry, but where could I try the '$time'?

Nurbek Boymurodov Over a year ago

are you going to convert from unixtime to date or from date to unix

Julian Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related