Query to count all participants of each registration type is not working

I want to have a table in the view that shows all the registration types associated with a conference, the price of each one and the number of registrations sold of each registration type.

For example if the conference with id "1" has two registration types with this columns:

rtype1 (name), 0.00$ (price), 20 (capacity)
rtype2 (name), 5.00$ (price), 10 (capacity)

And there are 20 participants registered in the rtype1 and 5 registered in the rtype 2 I want to show a table like:

    Registration Type                  Price           Sold/capacity 
    rtype1                              0.00$              20/20
    rtype2                              5.00$              5/10

Im not understanding how to properly achieve that. For now I have this query to get each registration type and the count of each registration type:

    $participantsOfEachType = Participant::
     select(DB::raw('registration_type_id, count(registration_type_id)'))
    ->groupBy('registration_type_id')->get();

This query returns the results below. But is not considering a specific conference. Do you know what is necessary to consider a specific conference id so is possible to show the results in a table in view like its in the above table example?

    Collection {#271 ▼
      #items: array:2 [▼
        0 => Participant {#282 ▼
        ...
          #attributes: array:2 [▼
            "registration_type_id" => 1
            "count(registration_type_id)" => 2
          ]
        ...
        }
        1 => Participant {#279 ▼
        ...
          #attributes: array:2 [▼
            "registration_type_id" => 2
            "count(registration_type_id)" => 2
          ]
          ... 
        }
      ]
    }

adamjhn

7 years ago

Level 3

The Participant model:

public function registration(){
    return $this->belongsTo('App\Registration');
}

public function registration_type(){
    return $this->belongsTo('App\RegistrationType');
}

Registration model:


// user that did the registration
public function customer(){
    return $this->belongsTo(User::class, 'user_that_did_registration', 'id');
}
 public function participants(){
    return $this->hasMany('App\Participant');
}

public function conference(){
    return $this->belongsTo('App\Conference');
}

adamjhn

7 years ago

Level 3

The query to get the count of eah registration type is like

SELECT registration_id,
       registration_type_id,
       count(*)
       FROM participants
       GROUP BY registration_id,
                registration_type_id;

But do you know how to get that result in eloquent?

Cronix

7 years ago

Level 67

Seems pretty straightforward. It just involves a single model. What have you tried? All query builder methods can be used on Eloquent models.

Selecting: https://laravel.com/docs/5.6/queries#selects

Counting: https://laravel.com/docs/5.6/queries#raw-expressions

Grouping: https://laravel.com/docs/5.6/queries#ordering-grouping-limit-and-offset

1 like

adamjhn

7 years ago

Level 3

Thanks, I have this:

$participantsOfEachType = Participant::with('registration_type')->groupBy('registration_type_id')->count('*');

But it shows only "2". But the result should shows the registration type and the total of each like:

registration_type_id    count(*)
1                                   2
2                                   2

Please or to participate in this conversation.