filter records via query where date is greater than some date

Hi @ramniksingh

What's the column data type of appdate column in the database? Is it a datetime, a date, a timestamp or is is stored as something unconventional like varchar?

I just tried the statement with a timestamp column and it works just fine:

// Create our model database schema
Schema::create('flights', function ($table) {
    $table->bigIncrements('id');
    $table->string('name');
    $table->string('number');
    $table->boolean('active')->default(true);
    $table->timestamps();
});

class Flight extends Illuminate\Database\Eloquent\Model {

}

Flight::forceCreate([
    'name' => 'Inactive Flight',
    'number' => 'AB6650',
    'active' => false,
]);

Flight::forceCreate([
    'name' => 'Active Flight',
    'number' => 'AB6650',
    'active' => true,
]);

// Retrieve models
$flights = DB::table('flights')->whereDate('created_at', '>=', '2020-06-28')->get();

sounds like its just a string/varchar column

@guybrush_threepwood @snapey

It is of datatype 'date'.

When I run the below in phpmyadmin, It works

select monthyear,appdate FROM `appcal` WHERE appdate>= '2018-04-01'

Why is it so hard to run simple queries through laravel ?

check the query

DB::connection()->enableQueryLog();

$qry = DB::table('appcal')
                    ->select('monthyear','appdate')
                    ->whereDate('appdate','>=', '2018-04-01')
                    ->get(); 

dd(DB::getQueryLog());

If it's a date then there's no need to use a whereDate statement:

$qry = DB::table('appcal')
                    ->select('monthyear','appdate')
                    ->where('appdate','>=', '2018-04-01')
                    ->get(); 

@snapey Below output is given in query log

array:1 [
  0 => array:3 [
    "query" => "select `monthyear`, `appdate` from `appcal` where date(`appdate`) >= ?"
    "bindings" => array:1 [
      0 => "2018-04-01"
    ]
    "time" => 6.0
  ]
]

And When I run below query in phpmyadmin, query works fine :

select `monthyear`, `appdate` from `appcal` where date(`appdate`) >= "2018-04-01" 

@guybrush_threepwood It doesn't change anything whether to use where or whereDate.

@snapey

This is the actual code I run

DB::connection()->enableQueryLog();

        foreach($activities as $activity){
            $qry[] = DB::table('appcal')
                    ->select('monthyear','appdate')
                    ->where('appdate','>=', $activity->actualStart)
                    ->get(); 
                    dd(DB::getQueryLog());
        }

And query log results in

array:1 [
  0 => array:3 [
    "query" => "select `monthyear`, `appdate` from `appcal` where `appdate` >= ?"
    "bindings" => array:1 [
      0 => "2017-10-20"
    ]
    "time" => 8.0
  ]
]

But no output after running query or using

dd($qry);

are you looking at the same database with phpmyadmin?

@snapey That's right I am looking at the exact database. I have only one . so no chance of confusion.

@snapey I think I have found the issue. Please see below:

This works

$test = DB::table('appcal')
                    ->select('monthyear','appdate')
                    ->whereDate('appdate','>=', '2018-04-01')
                    ->get();

        dd($test);

But this does not work

foreach($activities as $activity){
            $qry[] = DB::table('appcal')
                    ->select('monthyear','appdate')
                    ->whereDate('appdate','>=', '2018-04-01')
                    ->get(); 
                    //dd(DB::getQueryLog());
        }
            dd($qry);

But this work

foreach($activities as $activity){
            $qry[] = DB::table('appcal')
                    ->select('monthyear','appdate')
                    ->whereDate('appdate','=', '2018-04-01')
                    ->get(); 
                    //dd(DB::getQueryLog());
        }
            dd($qry);

Can you suggest what is the issue here? Am I doing something wrong ?

If you put dd(DB::getQueryLog()); after the end of the loop, not inside it, does the query log contain one query for every activity?

@snapey No, If I put dd(DB::getQueryLog()); outside the loop, It doesn't show anything .

Same is the case when dd($qry) is written after the loop . If dd($qry) is written inside the loop it shows the output.

No, If I put dd(DB::getQueryLog()); outside the loop, It doesn't show anything

that would indicate its not actually doing any queries?

Try this;

$qry = DB::table('appcal')
            ->select('monthyear','appdate')
            ->whereDate('appdate','=', '2018-04-01')
	    ->orWhereDate('appdate','>', '2018-04-01')
            ->get(); 

@meeshal No, the result still same.

Actually the problem is when I use the query inside a loop .

foreach($activities as $activity){
            $qry[] = DB::table('appcal')
                    ->select('monthyear','appdate')
                    ->whereDate('appdate','>=', '2018-04-01')
                    ->get(); 
                    //dd(DB::getQueryLog());
        }

            dd($qry);

Outside the loop it works fine. Am I doing anything wrong in storing the results in array ? I want to use the data for each activity , so I try to stored it in qry[] array

@ramniksingh Can you share the output of the query inside and outside the loop?

@meeshal When I Put dd($qry) inside the loop below is the output :

array:1 [▼
  0 => Illuminate\Support\Collection {#5589 ▼
    #items: array:2101 [▼
      0 => {#5591 ▼
        +"monthyear": "Apr-2018"
        +"appdate": "2018-04-01"
      }
      1 => {#5592 ▼
        +"monthyear": "Apr-2018"
        +"appdate": "2018-04-02"
      }
      2 => {#5593 ▼
        +"monthyear": "Apr-2018"
        +"appdate": "2018-04-03"
      }
      3 => {#5595 ▼
        +"monthyear": "Apr-2018"
        +"appdate": "2018-04-04"
      }
.............................. Total 2102 Records

When I put dd($qry) outside the loop, Nothing happens. Blank Screen with no error or anything else.

@ramniksingh

You said this...

Outside the loop it works fine.

Then you said...

When I put dd($qry) outside the loop, Nothing happens.

Your problem is the query or the loop? Please describe it clearly because I am not able to understand it here.

@meeshal

Actually the problem is when I use the query inside a loop . Outside the loop query works fine.

When I put dd($qry) outside the loop, Nothing happens. ----- It means when I run query inside the loop, and write dd($qry) outside the loop nothing happens, But When I put dd($qry) inside the loop , it shows me raw results.

Hope it is clear now.

Okay, first of all just dd(the_whole_query), inside and outside of the loop, and let me know the result.

Secondly, try a simple query, like DB::table('xyz')->all();, inside the loop and dd($qry) outside.

and lastly, define the array $qry = [] before the array.

@meeshal What is dd(the_whole_query) ? the_whole_query .

I have done dd($qry) inside and outside above. Kindly check.

Sorry I couldn't understand this --------- and lastly, define the array $qry = [] before the array.

@meeshal

Secondly, try a simple query, like DB::table('xyz')->all();, inside the loop and dd($qry) outside.

When I do this :

foreach($activities as $activity){
            $qry[] = DB::table('appcal')->get();
                    //dd(DB::getQueryLog());
        }

        dd($qry);

Same thing happens as before - Inside the loop, dd($qry) shows the output and outside the loop dd($qry) shows nothing just a blank screen.

Try this

$qry = [];
foreach($activities as $activity){
            array_push($qry, DB::table('appcal')->all());
                    //dd(DB::getQueryLog());
        }

        dd($qry);

@meeshal Same behaviour as before

This does not produce any output and blank screen occurs without any error

$qry = [];
        foreach($activities as $activity){
                array_push($qry, DB::table('appcal')->get());
        }

        dd($qry);

But this produces output as raw array

$qry = [];
        foreach($activities as $activity){
                array_push($qry, DB::table('appcal')->get());
 		dd($qry);
        }

Getting back to your initial problem. I've tested this solution in Tinkerwell and it seems to work for me. The problem you have is you are comparing a string equivalent in your whereDate. Try the following:

return DB::table('appcal')
    ->select('monthyear','appdate')
    ->whereDate('appdate', '>=', Carbon::createFromFormat('Y-m-d', '2018-04-01'))
    ->get();

You will need to import Carbon for this to work

@lancashireman Thanks for your input, But datatype is date already.

Actually if you read the full conversation after that, the problem came out to be is of storing the query result in an array from inside the loop.

Why would you perform the exact same query multiple times and try to append the results to an array?

Your initial issue was filter records via query where date is greater than some date but we've since established that's not your problem. What are you trying to achieve but cannot?

@lancashireman I want to store results in an array and then perform some calculations with that array.

Yes Initial question was a bit different but that issue still remains as well. And somehow another question of storing query results in array from within a loop came out.

I think this issue needs a new question/discussion so I am starting a new one :

https://laracasts.com/discuss/channels/laravel/running-a-query-multiple-times-inside-foreach-loop-and-storing-the-results-in-an-array