How to make specific queries in sql using Laravel?

Question

How to make specific queries in sql using Laravel?

Navigation

#1 by (3 votes)

1

I need to make an appointment at the bank to see the birthday of the current month. Such a query, see:

SELECT * FROM membros WHERE day(dataNasc) = day(CURRENT_DATE) and month(dataNasc) = month(CURRENT_DATE);

How could I make this query in Laravel?

I tried to do this way but he is returning dates that are not today today that in this case today 03/09/2017 in> ). Because it returns date as 04/09 , 05/09 , etc.

See how I'm trying:

$date = Membro::all();
foreach ($date as $d) {
    $explode = explode('-', $d->dataNasc);
}
$query = DB::table('membros')
           ->whereDay('dataNasc', $explode[2])
           ->whereMonth('dataNasc', $explode[1])
           ->get(['nome', 'imagem']);
dd($query);

It returns me 4 values. where only 3 values are according to the current date at the moment, but if I go to phpmyadmin and put the command

SELECT * FROM membros WHERE day(dataNasc) = day(CURRENT_DATE) and month(dataNasc) = month(CURRENT_DATE);

Does it return only the 3 correct values of the current date and the current month? How can I resolve this?

laravel-eloquent laravel-5 laravel-5.3

asked by anonymous 04.09.2017 / 04:37

1 answer

How to optimize this query in MySQL that is very slow? Error: no implicit conversion of Integer into String (TypeError)

score 3 · Accepted Answer

How could I make this query in Laravel?

I'll try to answer with some solutions:

SQL

SELECT * FROM membros 
   WHERE day(dataNasc) = day(CURRENT_DATE) 
     and month(dataNasc) = month(CURRENT_DATE);

Query Builder

1) With the current date of the server

\DB::table('membros')
    ->whereDay('dataNasc', date('d'))
    ->whereMonth('dataNasc', date('m'))
    ->get();

2) With the current date of the database

$where = 'day(dataNasc) = day(CURRENT_DATE)  and month(dataNasc) = month(CURRENT_DATE)';
\DB::table('membros')
    ->whereRaw($where)
    ->get();

Eloquent :

1) With the current date of the server

Membro::whereDay('dataNasc', date('d'))
    ->whereMonth('dataNasc', date('m'))
    ->get();

2) With the current date of the database

$where = 'day(dataNasc) = day(CURRENT_DATE)  and month(dataNasc) = month(CURRENT_DATE)';
Membro::whereRaw($where)->get();

Running Raw SQL Queries :

1) With the current date of the database

$sql = ' SELECT * FROM membros WHERE day(dataNasc) = day(CURRENT_DATE) ';
$sql .= ' and month(dataNasc) = month(CURRENT_DATE) '
\DB::select($sql);

That is, in this answer there are 5 examples that output the same result, according to sql of the question, just check which one is best with the points: server where the application or database server runs, because they can have date and time difference.