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

3
votes

Here are the columns of my member_profiles table:

id
last_name
first_name
birthday
civil_status

Civil status values are "single", "married", and "divorced".

This table already have thousands of entries. I want to count all members that are 25 to 27 years old that are single.

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

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

2
phpmysqllaravelquery-builderlaravel-query-builder
sounds great, so perhaps try something – user8011997

2 Answers

2
votes

Try this one,

$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 down in case of decimal value. Since month_between is not recognized in sql you can use DATEDIFF() which gives you in terms of days and divide it by 365 to get age value.

Hope, you understand.

0
votes

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();