rhaudenschilt's avatar

Laravel database structure for relationships

I'm having trouble setting up relationships between my tables.

First Question: I've tried to use topclaudy/compoships to do the relationships between form_questions and user_form_question_responses but that didn't work. Not sure if it's possible with my table structure?

public function userFormQuestionResponses()
{
    return $this->hasMany(UserFormQuestionResponse::class, ['user_form_id', 'form_question_id'], ['user_forms.id', 'id']);
}

Second Question: What should be the table structure to get this to work the 'Laravel Way'.

0 likes
2 replies
ianflanagan1's avatar

Ohhhhh, it took me ages to understand your question!

This is a 'many-to-many' relationship between (many) UserForm's and (many) FormQuestion's, with UserFormQuestionResponse as the junction table (or seems to be called "pivot" table in the Laravel world)

The documentation has a good example with many podcasts and many listeners connected by the pivot table subscriptions (https://laravel.com/docs/11.x/eloquent-relationships#many-to-many)

So you can create this relationship with:

class UserForm extends Model
{
    public function formQuestions()
    {
        return $this->belongToMany(FormQuestion::class)
            ->as('user_form_question_responses')
    }
}

class FormQuestion extends Model
{
    public function userForms()
    {
        return $this->belongsTo(UserForm::class)
            ->as('user_form_question_responses')
    }
}

You named the tables and foreign keys in the standard way (except for user_form_question_responses), so you don't need to specify them.

Since you're also JOINing user_form_question_responses with form_question_options, it might get messy. So if you find that the many-to-many relationship cannot work, then you might try replacing it with 2 relationships instead (and then a third to bring in form_question_options).

If you start with a user_form.id and you want to find the user_form_question_responses and the connected form_questions, then you search the database for a user_form record, and the relationships are:

UserForm (hasMany) -> UserFormQuestionResponses (belongs to one) -> FormQuestion

Or if you're going the reverse direction, you start with a form_question.id and you want to see all the various responses from different users then:

FormQuestion (hasMany) -> UserFormQuestionResponses (belongs to one) -> UserForm

Long term, the best solution is to learn SQL, not Laravel/Eloquent, then these problems become so much clearer.

Please or to participate in this conversation.