Eloquent ManyToMany relationship and Pivot table accessors

Hi !

My question can be hard to explain, so here is an example. Imagine you have a system where users can answers to some quiz. You'll have a "quizs" table, a "users" table and a pivot table "quizs_users" containing the answer from one user to one quiz. With Eloquent, a User belongsToMany Quiz and a Quiz belongsToMany User.

But in my case the pivot table "quizs_users" contains a column "answers" that is a JSON with all the answers to the quiz. And on the Quiz table I have a column "questions" that is also a JSON with all the questions. So to retrieve all the questions from one Quiz I have a simple accessors like this :

public function getQuestionsAttribute($value)
{
    return $value ? json_decode($value) : [];
}

But when it comes to the answers, as they are only in the pivot table I don't know where/how to create this kind of accessors ? My aim is to be able to return all the question an their answer from my query.

Thanks !

MarkRedeman

11 years ago

Level 10

Instead of having a User have a belongsToMany relationship with a Quiz and vice versa you could create a new model Participant which belongsTo a User and a Quiz. The User and Quiz model will then have a HasMany relationship with the Participant model. You then can use a similar getAnswersAttribute($value) function on the Participant model.

Then to get the answers of a participant, and the questions belonging to the quiz, you can do the following.

$user = Auth::user();
$participant = $user->participants()->with('quiz')->where('quiz_id', Input::get('quiz_id'))->get();
$answers = $participant->answers;
$questions = $participant->quiz->questions;

A lot of times instead of using a pivot it will be easier to create a pivot mode. Ryan Tablada has a nice post about this topic.

MarkRedeman

11 years ago

Level 10

If you do want to use the belongsToMany relastionships you can also define your own pivot model and add the getAnswersAttribute($answers) function to that pivot. Check the docs (Defining A Custom Pivot Model) on how to do that.

Mushr00m

11 years ago

Level 3

@MarkRedeman : In your example of switching to a pivot model, you end up having an ID for the participants as a PK and not the a composite one from the User and Quiz ?

MarkRedeman

11 years ago

Level 10

I would have the following tables:

users
-----
id
email

quizes
------
id
questions

participants
------
user_id
quiz_id
answers

Tough it could be that I didn't fully understand how your quizzes work.

My participants table is exactly the same as your quizs_users table except for the name. The participants don't necessarily have to have a primary key since you can find a participant based on his user id and quiz id.

You probably won't have to change your current database schema if you're using Eloquent's newPivot() method.

1 like

Mushr00m

11 years ago

Level 3

But if you use Eloquent, you can't have a class that extends Eloquent, to become a model, where you don't have a PK. Or am I wrong ?

MarkRedeman

11 years ago

Level 10

That might be right, I'm not sure. Most of the time I do keep a auto-incremented id in my tables since these are automatically generated in my migrations. I thinks the models should still be able to work without the primary key, but you wouldn't be able to use methods such as find($id), since it would throw an exception telling you that it can't find a primary key.

However pivot models work a bit differently. See this question on stackoverflow on how to implement such a custom pivot model.

Please or to participate in this conversation.