CODEWITHSUNDEEP
phpmysqllaravel
Basic Bridge
Basic Bridge

Reputation: 1911

Count every day entry

I want to count all register users each day from past 30 days. If there is no registration return 0

Currently i'm using this

    $startDate = Carbon\Carbon::now()->subDays(30);
    $signup = User::where('created_at', '>=', $startDate)
        ->groupBy('date')
        ->orderBy('date', 'DESC')
        ->get([
            DB::raw('Date(created_at) as date'),
            DB::raw('COUNT(*) as value')
        ]);
    return $signup;

Current output

[ 
  { 
    "date" : "2014-05-29",
    "value" : 1
  },
  { 
    "date" : "2014-05-15",
    "value" : 1
  }
]

If there is no registration then display value 0 for example

[ 
  { 
    "date" : "2014-05-29",
    "value" : 1
  },
  { 
    "date" : "2014-05-28",
    "value" : 0
  },
  { 
    "date" : "2014-05-27",
    "value" : 0
  },
  { 
    .....
    .....
  },
  { 
    "date" : "2014-05-15",
    "value" : 1
  }
  { 
    "date" : "2014-05-14",
    "value" : 0
  }
]

How can I get such result.

Upvotes: 0

Views: 125

Answers (1)

GouxLord
GouxLord

Reputation: 86

Put the $result array in another array value by value, and if lastrowdate - rowdate > 1 day, use this function to fill up the empty dates: https://stackoverflow.com/a/4312491/3696548

Upvotes: 1

Related Questions