4

I have the following query but it's returning an empty array (I know for a fact that this query should return one record)

$created_at = date("Y-m");

$content = ContentModel::where('userId', $id->_id)
->where('created_at', 'like', "%{$created_at}%")
->orderBy('fav', 'DESC')
->get();

If I remove the ->where('created_at', 'like', "%{$created_at}%") it returns everything fine but I want content that was of this year and month but the query doesn't work when I put that in.

The date in the database is ISODate format "created_at" : ISODate("2015-02-03T16:29:26.965Z")

I'm taking a guess it's because of the ISODate format. How do I get the result I need?

Thanks

Gaurav Dave
  • 6,838
  • 9
  • 25
  • 39
Haseeb
  • 190
  • 1
  • 6
  • 16
  • I checked the last query that was run: `{"query":"content.find({"userId":"545b08c10f6e807b0a8b4567","created_at":{"rege‌​x":"^2015-02$","flags":"i"}}, [])","bindings":[],"time":0.04}` This seems correct but nothings being returned – Haseeb Feb 05 '15 at 19:31
  • anybody?? please help I still couldn't figure it out – Haseeb Feb 08 '15 at 01:48

1 Answers1

2

Use this code instead:

$created_at = new DateTime(date('F jS Y h:i:s A', strtotime('first day of ' . date('F Y'))));

$content = ContentModel::where('userId', $id->_id)
            ->where('created_at', '>', $created_at)
            ->orderBy('fav', 'DESC')
            ->get();

In this code $created_at variable indicates the 1st of current month.

Raviraj Chauhan
  • 655
  • 5
  • 7
  • This does work and I am getting the result that I need but can you explain why the `like query` wasn't working? Will mark this as answer – Haseeb Feb 26 '15 at 16:43
  • Because the `created_at` field stores the date in ISODate format and it's not a normal string. So that we can't use like query here. – Raviraj Chauhan Feb 27 '15 at 08:05