Laravel - retrieve direct/indirect pivot table subscribers

Question

I am having a hard time figuring this out in laravel; for a subscriber relationship we have 2 pivot tables:

A subscriber can subscribe to a question:

question_subscriber
id   question_id  subscriber_id
1        2            3
2        2            4
3        3            1
4        3            2

A subscriber can subscribe to a user:

user_subscriber
id  user_id  subscriber_id
1      1         6
2      1         7
3      2         1

Normal questions table with question owner:

questions
id  question  user_id
1     abc?       1
2     xyz?       1
3     123?       2

The pivot relationships are setup correctly in their models, and I can pull out with a foreach, subscribers for a question, $question->subscribersQuestion as $subscriber, or subscribers for a user, $user->subscribersUser as $subscriber:

But how can we pull out all subscribers that belong to a user (directly or indirectly (through a question they own))?

For example, these should be all retrieved subscribers for user 1:

6 // subscribed to directly
7 // subscribed to directly
3 // subscribed to through question
4 // subscribed to through question

Any idea on the most efficient way to do this in Laravel?

Jarek Tkaczyk Jarek Tkaczyk · Accepted Answer · 2014-12-03T09:44:21

This will be the easiest way:

// assuming this setup: 
User hasMany Question
User belongsToMany User (subscribers - direct)
Question belongsToMany User (subscribers - indirect)

// we don't need this, since we pass it by reference to the closure,
// but for clarity let's initialize this variable:
$indirectSubscribers = null;

$user = User::with(['questions.subscribers' => function ($q) use (&$indirectSubscribers) {
  // get all indirect subscribers (cost of additional query)
  $indirectSubscribers = $q->get()->unique();
}])->find($userId);

// merge with directs subscribers
$allSubscribers = $user->subscribers->merge($indirectSubscribers);

Laravel - retrieve direct/indirect pivot table subscribers

1 Answers