CODEWITHSUNDEEP
postgresqllaravelextract
Diane Kaplan
Diane Kaplan

Reputation: 1708

Trying to get month from date in laravel (and postgres extract not working)

I'm making my first laravel project, using postgres, and I'd like to be able to access all the people with a birthday this month (my people table has a birthdate field that's a date). I can use extract to get these records from the database, like so: 

select * from people 
   where extract (month from birthdate) = 11;

But when I try a few different ways in my controller I get 'unknown column' errors: 

$birthday_people = DB::table('people')
    ->where ("extract(month from birthdate)", "=", "11")
    ->get();

(I'll ultimately adjust it to compare with Carbon::now()->month, and use the model Person::all(), but until I get some results coming through I'm going as simple as possible)

Is there a special way to get the month from a date in laravel?

Update: I'm using a scope now in my Person model. I can get person results to come through when I give it an exact date: 

public function scopeBirthdays($query)
{
    return $query->where('birthdate', '=', '1947-11-02');
}

And I can get results back for month if I do it this way, but the catch is it doesn't seem to know it's a collection of People anymore (I can't access person columns when I display it out and I can't chain other scopes): 

public function scopeBirthdays($query)
{
    return $query->whereRaw('extract(month from birthdate) = ?', ['11'])->get();
}

Laravel's query builder offers 'whereMonth'- (seems the most right), but it gave me an 'undefined function' error until I put the bool on the end, and now the current error suggests that it's interpretting number of months instead of which one(?): 

public function scopeBirthdays($query)
{
   return $query->whereMonth('birthdate', '=', Carbon::today()->month, true);
}

I get: Syntax error: 7 ERROR: syntax error at or near "month" LINE 1: select * from "people" where 1 month("birthdate") = $1 ^ (SQL: select * from "people" where 1 month("birthdate") = 11)

Final update: I was able to get results back (that were correctly interpretted as people) by using whereRaw in my scope: 

public function scopeBirthdays($query)
{
    return $query->whereRaw('extract(month from birthdate) = ?', [Carbon::today()->month])->orderBy ('birthdate', 'asc');
}

Thanks, everyone!

Upvotes: 1

Views: 2669

Answers (2)

SergkeiM
SergkeiM

Reputation: 4168

based on previous question try:

$birthday_people = DB::table('people')
    ->whereRaw("extract(month from birthdate)", "=", "11")
    ->get();

You can set is as relationship

public function custom(){
   return $this->hasMany('App\Models\People')->whereRaw("extract(month from birthdate)", "=", "11");
}

Upvotes: 3

Daniel Wade
Daniel Wade

Reputation: 46

You could try something like this. It will return as an instance of App\People so you will be able to use your eloquent functions still.

public function scopeBirthdays($query)
{
    return $query->where('birthdate', 'LIKE', Carbon::today()->year . '-'.Carbon::today()->month.'%');
}

I don't have much experience with Carbon, I'm assuming that Carbon::today() will return an object with the year, month, and date.

Upvotes: 0

Related Questions