如何正确获取特定国会中所有注册的信息？

Question

I have a registration form for a user to register in a congress.

For example, if the user John is doing a registration in a congress and a registration with 2 participants (him and his colleague Jake) and John is registering him in ticket type tt1 and Jake in the ticket type tt2 in the database is inserted an entry in the Registrations table to store which user did the registration and in which congress. Then, it is also inserted in the participants table an entry for each registered participant by the user that did the registration (main_participant). So the tables stay like:

registrations table

id | status | congress_id | main_participant_id
---+--------+-------------+--------------------
7  | C      | 1           | 1

participants table

id | registration_id | ticket_type_id | name | surname
---+-----------------+----------------+------+--------
12 | 7               | 1              | John | W
13 | 7               | 2              | Jake | Y

Now I have a ParticipantController that has the index method, that gets the info of a specific Congress:

class ParticipantController extends Controller
{
    public function index($id)
    {
        $congress = Congress::find($id);
        return view('participants.index')->with('congress', $congress);
    }
}

In this participants.index view I want to list in a table the registrations deails:

1. all users that did a registration in this specific congress;
2. the quantity of tickets that they acquired;
3. the ticket types; and
4. the status of the registration (Complete, Incomplete).

So, is it possible to have a table in the following image?

What is necessary to properly get this information? I'm not understanding how to get this. Both tables in the image above are the same, the only difference is:

• the first part of the image is when all_participant is 1 in the congresses table, which means in the congress registration form was necessary to collect info (name and surname) of each participant, so the user that did the registration needed to introduce the name and surname of each participant. Then, the name and surname of each participant should appear in the modal, as in the image.
• in the case when all_participant is 0 in the congresses table the name and surname of each participant are blank (''). That's because in the registration form was not necessary to collect info of each participant.

Related to the Ticket type(s) column in the table: a user can do a registration with multiple participants. For example, John can select 2 ticekts, one of the ticket type tt1 and other of the ticket type tt2. If the registration includes 1 or more ticket types it should appear in the Ticket type(s) column the ticket types associated with that specific registratation, as in the image above for the first registration (for the user John W).

With this code in the ParticipantController:

 $allRegistrations = $congress->registrations()->get();

And then in the view:

@foreach ($allRegistrations as $r)
    {{ $r }}
@endforeach

I got:

{"id":10,"status":"C","congress_id":1,"main_participant_id":1}

The status can be gotten like {{ $r->status }}. But, how to get the other necessary info that is in the image? The quantity, ticket types and the name of the user that did the registration? And the info in the modal: the name and surname of each participant, or show "" if all_participant is 0, and also the ticket type associated to each participant.

To show all registrations and the necessary info as in the image, I don't know if the database structure I have allows to get that information properly. For now, the database looks like this:

Laravel models:

// Congress model
class Congress extends Model
{ 
    // A conference has one creator
    public function creator(){
        return $this->belongsTo('App\User', 'user_id');
    }
    public function ticketTypes(){
        return $this->hasMany('App\TicketType', 'congress_id');
    }
    public function registrations(){
        return $this->hasMany('App\Registration', 'congress_id');
    }
}

// User model
class User extends Authenticatable
{
    public function congresses(){
        return $this->hasMany('App\Congress', 'user_id');
    }

    // A user can register in many conferences
    public function registrations(){
        return $this->hasMany('App\Registration','main_participant_id');
    }
}

// Registration model
class Registration extends Model
{
    // a registration has one user that do the registration
    public function customer(){
        return $this->belongsTo('App\User');
    }

    // a registration can have many participants
    public function participants(){
        return $this->hasMany('App\Participant');
    }

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

// Participant Model
class Participant extends Model
{
    // a participant belongs to a registration
    public function registration(){
        return $this->belongsTo('App\Registration');
    }
}

// Ticket Type model
class TicketType extends Model
{
    public function congress(){
        return $this->belongsTo('App\Congress');
    }

    public function questions(){
        return $this->belongsToMany('App\Question', 'ticket_type_questions')->withPivot(['required']);;
    }
}

// Question model
class Question extends Model
{
    public function ticket_type(){
        return $this->belongsToMany('App\TicketType', 'ticket_type_questions')
            ->withPivot('required');
    }
}

// Answer model
class Answer extends Model
{
    public function question(){
        return $this->belongsTo('Question');
    }
    public function participant(){
        return $this->belongsTo('Participant');
    }
}

// TicketTypeQuestion model
class TicketTypeQuestion extends Model
{
}

Finally, a ticket type may have custom questions associated to it. For example, in the image below both ticket types tt1 and tt2 have the custom question "Whats your phone?" associated to them. In that case, the answers to these questions, answered by the use that did the registration, should appear in the modal when the user clicks in the + to see the details. How to get these answers? In this image examples the phone of the participant 1 is 002.. and the phone of the participant 2 is 003...

Answer 1

The answer was updated based on information provided in the comments.

First, you have to fix the relation between Registration and User class. It is using the default column naming, but your schema uses a different one:

class Registration extends Model
{

    // ...

    public function customer()
    {
        return $this->belongsTo('App\User', 'main_participant_id');
    }

    // ...

}

If you have access to do that, I would suggest you to change that column's name to something more clear. The current name takes one to assume it references the participants table.

With that relation fixed, you are now able to get the main participant by calling its magic property. This will return a User model instance:

$registration->customer;

Additionaly, you have to add a relation between participants and ticket types to your Participant class:

class Participant extends Model
{

    // ...

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

    // ...

}

This relation will allow you to get the ticket type details, including its name, from any participant model instance.

Then, on the view, you should be able to do the following:

<table>
    <tr>
        @foreach ($congress->registrations as $registration)

            <!-- User that did the registration -->
            <td>{{ $registration->customer->name }}</td>

            <!-- Email.
                 Although, I don't see an email field in your DB schema -->
            <td>{{ $registration->customer->email }}</td>

            <!-- Ticket types -->
            <td>
                {{
                    $registration->participants
                        ->map(function ($participant) {
                            return $participant->ticket_type->name;
                        })
                        ->implode(', ')
                }}
            </td>

            <!-- Quantity -->
            <td>{{ $registration->participants->count() }}</td>

            <!-- Status -->
            <td>{{ $registration->status === 'C' ? 'Confirmed' : 'Waiting' }}</td>

            <!-- Date. Considering a Carbon instance.
                 Although, I don't see a created_at field in your DB schema -->
            <td>{{ $registration->created_at->format('m/d/y') }}</td>

        @endforeach
    </tr>
</table>

As you can see, I used a "trick" to get all the types within a single method chain. That's possible because Eloquent results, except for when a single instance is returned and other special cases, are usually Collections. You can learn more about Eloquent collections and the available methods on the Laravel docs. Also, notice my comments above on missing fields.

Finally, for the second view, you can iterate through all participants of a specific registration:

<!-- User that did the registration -->
<dl>
    <dt>Name</dt>
    <dd>{{ $registration->customer->name }}</dd>
    <dt>Email</dt>
    <dd>{{ $registration->customer->email }}</dd>
</dl>

@foreach ($registration->participants as $participant)

    <!-- Participant N -->
    <dl>
        <dt>Name</dt>
        <dd>{{ $participant->name }}</dd>
        <dt>Surname</dt>
        <dd>{{ $participant->surname }}</dd>
        <dt>Ticket Type</dt>
        <dd>tt{{ $participant->ticket_type_id }}</dd>
    </dl>

@endforeach