Clever Geek Handbook

Query Builder for Laravel to count all 25-35 years that are single

Here are the columns of my member_profiles table:

ID
last_name
first_name civil_status
birthday

The meanings of civil status are "one", "married" and "divorced".

There are already thousands of records in this table. I want to calculate all members from 25 to 27 years old who are singles.

I could start with something like this, but I don't know how to continue to map it to a specific age range

$members = MemberProfile::where('civil_status', 'single')->count();

+3


source to share


2 answers


Try it,

$count = DB::table('member_profiles')
         ->select(DB::raw('floor(DATEDIFF(CURDATE(),birthday) /365) as age') ,'civil_status')
         ->where('civil_status','single')
         ->where('age','>',25)
         ->where('age','<',35)
         ->count();


Here floor

funtion is for rounding in case of decimal value. Since it is month_between

not recognized in sql, you can use DATEDIFF()

that gives you in terms of days and divide by 365 to get the age value.

I hope you understand.

+1


source


You can use carbon and whereBetween 



  use Carbon\Carbon;
  Class {
  .......
  .......
  $today = Carbon::now();
  //$today = Carbon::createFromDate(2017, 7, 2);//to make manually
  $sub25 = $today->subYears(25);
  $today = Carbon::now();
  //$today = Carbon::createFromDate(2017, 7, 2);//to make manually
  $sub35 = $today->subYears(35);


  $members = MemberProfile::where('civil_status', 'single')
  ->whereBetween("birthday",[$sub25,$sub35])
  ->count();
0


source






More articles:


All Articles