How can I count occurrences of different types from my table using a raw query?-CodePudding

I have a table named partner. In the partner table I have one column named type where I store the type of partner: either yearly, monthly, or weekly.

I want to count the types of partner. For example, here I count the user account yearly. Similarly, I want to count the types of partner.

  $year = ['2015','2016','2017','2018','2019','2020','2021','2022'];
    $user = [];
    $type = ['Yearly','Monthly','Weekly'];
    $partner = [];
    foreach ($year as $key => $value) {
        $user[] = User::where(\DB::raw("DATE_FORMAT(created_at, '%Y')"),$value)->count();
    }

CodePudding user response：

I am not sure about the structure of Partner table . If the column type is varchar and you actual store 'Yearly' or 'Monthly' or 'Weekly' then you do not need DB::raw , just

foreach ($type as $key => $value) {
            $partner[] = Partner::where('type',$value)->count();               
        }

If you want the query to be raw then you can try this (of course, I do not recommend writing code like this, is is just an example)

foreach ($type as $key => $value) {
    $query = DB::select(DB::raw("select count(type) as count from partner where type like  '" . $value . "'"));
    if ($query) {
        $partner[] = $query[0]->count;
    }
}

You can use DB::enableQueryLog(); before the execution of the query and dd( DB::getQueryLog()); to see what query is executed.