How can I run this SQL query in Laravel?

Question

I am trying to run this SQL query in many ways in laravel but it shows error.

SELECT * FROM reservas WHERE cliente_id = 2201 AND dia >= '2020-10-15' GROUP BY dia

I have tried to run this query but I get a violation error

DB::select('SELECT * FROM reservas WHERE cliente_id = :id AND dia >= :date GROUP BY dia', ['id' => $id, 'date' => $date]);

The variables that I send have data, that is, the error does not come from empty variables, far from it, but the error comes from the query. It should be noted that this query does work in SQL.

Well your current attempt is a raw SQL query, so at the very least you should be using selectRaw, not select. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 15, 2020 at 11:49
It shows me the following error when substituting select for selectRaw Call to undefined method Illuminate\Database\MySqlConnection::selectRaw() — Cristian
– Cristian, Commented Oct 15, 2020 at 11:53
Why are you using GROUP BY without selecting any aggregates? Your current query does not make sense. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 15, 2020 at 11:57

VIKAS KATARIYA · Accepted Answer · 2020-10-15 12:08:15Z

1

You need to write your query like groupBy(DB::raw('dia'))

\DB::table('reservas')->where('cliente_id',$id)->where('dia',$date)->get()->groupBy('dia');

You need to change false in config/database.php Like strict => false if your groupBy not working properly.

Hope its work!!

edited Oct 15, 2020 at 12:08

answered Oct 15, 2020 at 12:01

VIKAS KATARIYA

6,0153 gold badges19 silver badges37 bronze badges

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

Comments

Jignesh Joisar · Accepted Answer · 2020-10-15 12:04:16Z

0

@Cristian you need to use DB::raw inside DB::select

using like that

DB::select(DB::raw("SELECT * FROM reservas WHERE cliente_id = :id AND dia >= :date GROUP BY dia"), ['id' => $id, 'date' => $date]);

other wise used this if you don't want to set strict type false.

DB::table('reservas')->where('cliente_id',$id)->where('dia',$date)->get()->groupBy('dia');

edited Oct 15, 2020 at 12:04

answered Oct 15, 2020 at 11:54

Jignesh Joisar

15.4k6 gold badges69 silver badges61 bronze badges

6 Comments

Tim Biegeleisen Over a year ago

But is a raw query the best option here in general? Assuming fluent builder supports parameters, wouldn't it make more sense to use that instead?

Tim Biegeleisen Over a year ago

GROUP BY with SELECT * is generally forbidden.

Deepesh Thapa Over a year ago

This is the only way to use raw sql querry in Laravel @TimBiegeleisen

Cristian Over a year ago

I get the following error

SQLSTATE[42000]: Syntax error or access violation: 1055 'propuesta.reservas.id' isn't in GROUP BY (SQL: SELECT * FROM reservas WHERE cliente_id = :id AND dia >= :date GROUP BY dia)

Jignesh Joisar Over a year ago

@TimBiegeleisen you are right but he mention in question how to run sql query .

|

Collectives™ on Stack Overflow

How can I run this SQL query in Laravel?

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related