Laravel relational database - trying to get specific participant responses to survey questions using hasMany/hasManyThrough

I'm relatively new to relation databases and I am trying to come up with a way to get the responses to a survey for that day from a specific user. My end goal is to check how far along the user is in the survey, or if the user has completed that survey. Thanks for your help.

I have a participants table, a surveys table, a survey questions table, and a survey responses table.

• Every participant has many surveys

• Every participant has many responses to the survey questions (2)

• Every survey has many questions

• Every question has one response

I am not sure if I should set up a 'hasManyThrough' relationship. As of now with my models I can find the participant, then find the corresponding survey, then gather the questions from that survey. This is where the chain breaks and I can't use the questions that I retrieve from the survey and find their responses using my "SurveyResponse" model. Can someone help me figure out if my DB structure / relationships are correct?

My controller (I can get to where it echoes the $question->body, but I can't get to check for the responses to a question using something like $question->responses()->get):

public function findResponses(Request $request)
{
    $participant_id = '1234';
    $surveyDate = new Carbon();
    $surveyDate = $surveyDate->toDateString();
    $participant = Participant::where('user_id', $participant_id)->first();

    // check if participant daily survey is initiated
    $surveyStarted = $participant->dailySurveyInitiated;

    if($surveyStarted)
    {
        $activeSurveyParticipant = Participant::where('user_id', $participant_id)->first();
        // find survey for that date corresponding to that user
        $activeSurvey = Survey::where('survey_Date', $surveyDate)->where('participant_id', $participant_id)->first();

        $surveyID = $activeSurvey->id;
        // gather the questions
        $activeSurveyQuestions = $activeSurvey->SurveyQuestions()->where('survey_id', '=', $surveyID)->get();
        // check to see what questions have been answered

        foreach($activeSurveyQuestions as $question)
        {
            echo $question->body;
            echo '<br>';

            // really want to gather the responses to each 
            // question based off the participant_id...
            // something like
            // $currentQ = $question->SurveyResponse::where('response', '!=', null)->get();
            // echo $currentQ->response;

        }

    }
    else
    {

    }
}

My participant model:

class Participant extends Model
{
    protected $table = 'participants';
    protected $primaryKey = "user_id";

    public function surveys()
    {
        return $this->hasMany('App\Survey', 'participant_id', 'user_id');
    }

    public function surveyQuestionResponses()
    {
        return $this->hasMany('App\Survey', 'App\SurveyQuestionResponses');
    }

    public function surveyResponses()
    {
        return $this->hasManyThrough('App\SurveyResponse', 'App\SurveyQuestion');
    }
}

Participants table:

class CreateParticipantsTable extends Migration
{
protected $primaryKey = 'user_id';
protected $incrementing = false;

public function up()
{
    Schema::create('participants', function (Blueprint $table) 
    {
        $table->engine = 'InnoDB';
        $table->string('user_id')->unique();
        $table->boolean('subscribed')->default(0);
        $table->boolean('dailySurveyInitiated')->default(0)->nullable();
        $table->integer('dailySurveyCompleted')->default(0)->nullable();

        $table->boolean('studyCompleted')->default(0);

        $table->timestamps();
    });
}

My survey model:

class Survey extends Model
{
    protected $table = 'surveys';

    public function SurveyQuestions()
    {
        return $this->hasMany('App\SurveyQuestion');
    }
    // Participant
    public function participant()
    {
        return $this->belongsToMany('App\Participant', 'user_id', 'participant_id');
    }
}

My survey table:

class CreateSurveysTable extends Migration
{
    public function up()
    {
        Schema::create('surveys', function (Blueprint $table) 
        {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('participant_id')->nullable();
            $table->string('survey_date')->nullable();
            $table->boolean('completed')->nullable()->default(0);
            $table->timestamps();
        });

        Schema::table('surveys', function($table) {
            $table->foreign('participant_id')->references('user_id')->on('participants');
        });
    }
}

My SurveyQuestions Model:

class SurveyQuestion extends Model
{
    protected $table = 'survey_questions';

    public function survey()
    {
        return $this->belongsTo('App\Survey', 'survey_id');
    }

    public function participant()
    {
        return $this->belongsTo('App\Participant', 'participant_id');
    }

    public function responses()
    {
        return $this->hasMany('App\SurveyResponse', 'question_id');
    }
}

My SurveyQuestions Table:

class CreateSurveyQuestionsTable extends Migration
{
    public function up()
    {
        Schema::create('survey_questions', function (Blueprint $table) 
        {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('body')->nullable();
            $table->enum('type', ['numeric', 'image'])->nullable();
            $table->integer('survey_id')->nullable();
            $table->timestamps();
        });

        Schema::table('survey_questions', function($table) {
            $table->foreign('survey_id')->references('id')->on('surveys');
        });
    }
}

My SurveyResponse model:

class SurveyResponse extends Model
{
    protected $table = 'survey_responses';

    public function question()
    {
        return $this->belongsTo('App\SurveyQuestion');
    }
    public function participant()
    {
        return $this->belongsTo('App\Participant');
    }
}

My SurveyResponse table:

class CreateSurveyResponsesTable extends Migration
{
    public function up()
    {
        Schema::create('survey_responses', function (Blueprint $table) 
        {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('participant_id')->nullable();
            $table->integer('question_id')->nullable();
            $table->integer('survey_id')->nullable();
            $table->string('session_sid')->nullable();

            $table->text('response')->nullable();
            $table->enum('type', ['numeric', 'image'])->nullable();

            $table->string('mediaSID')->index()->nullable();
            $table->string('messageSID')->index()->nullable();
            $table->string('mediaURL')->index()->nullable();
            $table->binary('media')->nullable();
            $table->string('filename')->index()->nullable();
            $table->string('MIMEType')->nullable();

            $table->timestamps();
        });

        Schema::table('survey_responses', function($table) {
            $table->foreign('participant_id')->references('user_id')->on('participants');
            $table->foreign('question_id')->references('id')->on('survey_questions');
            $table->foreign('survey_id')->references('survey_id')->on('survey_questions');
        });
    }
}

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2x6rPNN
via IFTTT