Search with date in Laravel

Question

Search with date in Laravel

Asked 5 years, 9 months ago

Viewed 144 times

0

Devs, good afternoon I’m having the following problem, I have the sequinte query I’m running with DB::raw() From Laravel, I need the date to be passed to query as a string with simple quotes only so I will be able to execute the query.

SELECT SUM(N.primeiro_turno + N.segundo_turno + N.terceiro_turno)
      ,SUB.* 
  FROM notes N, (SELECT SUM(NS.total_dia) AS TOTAL_DIA
                       ,NS.sequencing_id 
                   FROM notes NS 
                  WHERE NS.sequencing_id IN (SELECT NT.sequencing_id 
                                               FROM notes NT 
                                              WHERE NT.data_apontamento = 2018-09-25 
                                              GROUP BY NT.sequencing_id) 
                  GROUP BY NS.sequencing_id) AS SUB 
  INNER JOIN sequencings ON sequencing_id          = sequencings.id 
  INNER JOIN products    ON sequencings.product_id = products.id 
  INNER JOIN machines    ON sequencings.machine_id = machines.id 
  WHERE N.data_apontamento = 2018-09-25 
    AND N.sequencing_id    = SUB.sequencing_id 
  GROUP BY N.sequencing_id, SUB.sequencing_id, SUB.TOTAL_DIA

It does not bring any results, I did some tests and I came to the conclusion that is because of the date, I would like someone to give me a light of how to solve this problem, in Workbench step the data as string, I have tried to make this option, unsuccessfully.

What kind of fields NT.data_apontamento and N.data_apontamento?

– Matheus Ribeiro

2018/10/02 at 18:31
You are using the query in what way?

– Alvaro Alves

2018/10/02 at 18:32
Matheus and date type

– PaulinhoCaP

2018/10/02 at 18:35
Alvaro am using DB::raw()

– PaulinhoCaP

2018/10/02 at 18:35
You said you did tests and the problem is because of the date... So if you remove the date filters works correctly? Note: Use @and the person’s name to reference them in the comments, this is how a notification is generated! Example: @Paulinhocap

– Matheus Ribeiro

2018/10/02 at 18:40
double quotes on dates does not work? NT.data_pointing = "2018- 09-25", the date you are searching for is in the default mysql format?

– Alvaro Alves

2018/10/02 at 18:42
@Alvaroalves The date it is informing is correct for mysql standard, just need to put single or double quotes as you suggested

– Matheus Ribeiro

2018/10/02 at 18:48
@Correct Matheusribeiro works

– PaulinhoCaP

2018/10/02 at 18:52
@Alvaroalves this I need to know how to put simple quotes in DB:RAW()

– PaulinhoCaP

2018/10/02 at 18:53
@Matheusribeiro what happens is the following as I’m using DB:raw() the Laravel does not put simple quotes, I made a test creating variable $sql and the query worked

– PaulinhoCaP

2018/10/02 at 18:56
$Results = DB::select('SELECT HORA(created_at) as hora, COUNT(*) as contagem FROM visitas WHERE created_at >= DATE_SUB(NOW(),INTERVAL ? DAY) GROUP BY HOUR(created_at)', [16]);

– Alvaro Alves

2018/10/02 at 18:57
@Paulinhocap Edit your question and implement her little face, stating that the problem is in relation to DB:RAW() and show us how you are assembling SQL in your source, I will try to help you but I do not understand much of

– Matheus Ribeiro

2018/10/02 at 18:57

Show 7 more comments

1 answer

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.

by Alvaro Alves • **1,065** points · Answer 1 · 2018-10-02T19:00:31+00:00

Try to use Binding:

DB:RAW('SELECT SUM(N.primeiro_turno + N.segundo_turno + N.terceiro_turno)
      ,SUB.* 
  FROM notes N, (SELECT SUM(NS.total_dia) AS TOTAL_DIA
                       ,NS.sequencing_id 
                   FROM notes NS 
                  WHERE NS.sequencing_id IN (SELECT NT.sequencing_id 
                                               FROM notes NT 
                                              WHERE NT.data_apontamento = DATE_FORMAT(?,"%Y-%m-%d") 
                                              GROUP BY NT.sequencing_id) 
                  GROUP BY NS.sequencing_id) AS SUB 
  INNER JOIN sequencings ON sequencing_id          = sequencings.id 
  INNER JOIN products    ON sequencings.product_id = products.id 
  INNER JOIN machines    ON sequencings.machine_id = machines.id 
  WHERE N.data_apontamento = DATE_FORMAT(?,"%Y-%m-%d") 
    AND N.sequencing_id    = SUB.sequencing_id 
  GROUP BY N.sequencing_id, SUB.sequencing_id, SUB.TOTAL_DIA', [$data1, $data2]);