Mar 24, 2021

Level 1

Convert SQL raw query to Laravel ORM or Query Builder

Hello,

I have some raw queries I'm getting stuck while converting them into Laravel query builder or in ORM. If someone can please convert them thank you.

		$data = DB::select("select

        to_char(submit_date, 'YYYY-MM-DD') AS sent_at,

        status as stat,

        count(username) as t

        from logs

        where username=?

        and (submit_date between (LOCALTIMESTAMP - INTERVAL '6 DAYS')  and (LOCALTIMESTAMP))

        GROUP BY sent_at,stat

        ORDER BY sent_at", [$this->currentUser()->username]);

bugsysha

5 years ago

Level 61

Something like this should work.

Log::query()
	->selectRaw('to_char(submit_date, 'YYYY-MM-DD') AS sent_at, status as stat, count(username) as t')
	->where('username', $this->currentUser()->username)
	->whereBetween('submit_date', [$start, $end])
	->groupBy(['sent_at', 'stat'])
	->orderBy('sent_at);

If you need more control over submit_date then try:

Log::query()
	->selectRaw('to_char(submit_date, 'YYYY-MM-DD') AS sent_at, status as stat, count(username) as t')
	->where('username', $this->currentUser()->username)
	->where(function ($query): void {
		$query->whereBetween('submit_date', [$start, $end])->whereRaw('LOCALTIMESTAMP');
	})
	->groupBy(['sent_at', 'stat'])
	->orderBy('sent_at);

1 like

VimKanzo

5 years ago

Level 1

Thanks, @bugsysha for the swift response. I tried the first query which seems to run perfectly, but unfortunately, it's not returning any data.

I checked the raw query being displayed via the DEBUGBAR package and I copied the query which is below and I run that in my SQL management tool and it showed the data. I have 119 records in total.

The query displayed by the package is as below:

select status, count(*) as total from logs where user_id = 2 and submit_date between '2021-03-01 00:00:00' and '2021-03-31 23:59:59' group by status

and it shows:

DELIVRD 11 EXPIRED 3 UNDELIV 5

I don't know what I'm doing wrong.

VimKanzo

5 years ago

Level 1

Here is the images of the query. One is the raw sql query and another is an api call to that query

https://ibb.co/FmKRhMR

https://ibb.co/vcgN7n4

bugsysha

5 years ago

Level 61

Show what have you pasted in your code cause order statement is missing from the query DEBUGBAR is reporting. I assume that you've changed some stuff around.

1 like

VimKanzo

5 years ago

Level 1

So with the first query, the only thing I added was the start and the end date using carbon and also I had to use double quotes on the date format. But there is what I have in my Controller currently.

	$start = Carbon::now()->subDays(6);
	$end = Carbon::now();
    
    $data = Log::query()
    ->selectRaw('to_char(submit_date, "YYYY-MM-DD") AS sent_at, status as stat, count(username) as t')
    ->where('username', $this->currentUser()->username)
    ->whereBetween('submit_date', [$start, $end])
    ->groupBy(['sent_at', 'stat'])
    ->orderBy('sent_at');

bugsysha

5 years ago

Level 61

@vimkanzo it is OK that you've added dates and replaced quotes. What is missing is ->get() at the end.

Try:

$data = Log::query()
    ->selectRaw('to_char(submit_date, "YYYY-MM-DD") AS sent_at, status as stat, count(username) as t')
    ->where('username', $this->currentUser()->username)
    ->whereBetween('submit_date', [$start, $end])
    ->groupBy(['sent_at', 'stat'])
    ->orderBy('sent_at')
	->get();

1 like

VimKanzo

5 years ago

Level 1

@bugsysha Thanks once again for the assistance, but adding a get threw an error. Did you see the latest one I posted which is the full function?

bugsysha

5 years ago

Level 61

Full function is not relevant if it is not breaking after the query. Post the error message.

1 like

VimKanzo

5 years ago

Level 1

Here is the sql error i got after adding the ->get()

SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION hellio_v3.to_char does not exist (SQL: select to_char(submit_date, "YYYY-MM-DD") AS sent_at, status as stat, count(username) as t from logs where username = helliosms and submit_date between 2021-03-18 19:02:43 and 2021-03-24 19:02:43 group by sent_at, stat order by sent_at asc)

bugsysha

5 years ago

Best Answer

Level 61

@vimkanzo you see the error. It is saying that to_char() function doesn't exist. Try replacing it with date_format(submit_date, '%Y-%m-%d').

1 like

VimKanzo

5 years ago

Level 1

Thanks very much, @bugsysha for the time and assistance. Really appreciate it greatly. It worked perfectly. Below is a link to how it looks like.

https://ibb.co/bWpv0sM

bugsysha

5 years ago

Level 61

You are very welcome. Thanks for the "Best Answer". Looks nice. Keep on coding.

1 like

Please or to participate in this conversation.