Query multiple, nested relationships in Laravel / Eloquent

Question

I am trying to create a simple news feed for posts in Laravel with Eloquent.

Basically I want to get all Messages ...

• where i am the author
• where the people I stick with are authors (possible)
• where the people I stick to have commented
• where people with the same name field_id are
• where poeple with the same school_id are the author

in one request.

Since I have never worked intensively with combined / combined SQL queries, any help on this is greatly appreciated!

My tables

users table

+----+
| id |
+----+

posts table

+----+-----------+-------------+
| id | author_id | author_type |
|----|-----------|-------------|
|    | users.id  | 'App\User'  |
+----+-----------+-------------+

comments table

+----+----------------+------------------+-----------+-------------+
| id | commentable_id | commentable_type | author_id | author_type |
|----|----------------|------------------|-----------|-------------|
|    | posts.id       | 'App\Post'       | users.id  | 'App\User'  |
+----+----------------+------------------+-----------+-------------+

schoolables table

+---------+-----------+----------+
| user_id | school_id | field_id |
+---------+-----------+----------+

followables table

+-------------+---------------+---------------+-----------------+
| follower_id | follower_type | followable_id | followable_type |
|-------------|---------------|---------------|-----------------|
| users.id    | 'App\User'    | users.id      | 'App\User'      |
+-------------+---------------+---------------+-----------------+

My models

class Post extends Model
{
    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphTo
     */
    public function author()
    {
        return $this->morphTo();
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphMany
     */
    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

class Comment extends Model
{
    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphTo
     */
    public function author()
    {
        return $this->morphTo();
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphTo
     */
    public function commentable()
    {
        return $this->morphTo();
    }
}

class User extends Model
{
    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphMany
     */
    public function posts()
    {
        return $this->morphMany(Post::class, 'author')->orderBy('created_at', 'desc');
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphMany
     */
    public function comments()
    {
        return $this->morphMany(Comment::class, 'author')->orderBy('created_at', 'desc');
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function schoolables()
    {
        return $this->hasMany(Schoolable::class);
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphMany
     */
    public function following()
    {
        return $this->morphMany(Followable::class, 'follower');
    }
}

Answer 1

, , leftJoins, Where

$posts = Post::leftJoin('..', '..', '=', '..')
  ->where(function($query){
    $query->where('author_id', Auth::user()->id); // being the author
  })->orWhere(function($query){
    // second clause...
  })->orWhere(function($query){
    // third clause...
  .....
  })->get();

, , UNIONS, http://laravel.com/docs/5.1/queries#unions

, - ..

$written = Auth::user()->posts();
$following = Auth::user()->following()->posts();

, , .

$posts = $written->union($following)->get();

,

Answer 2

, , 5 . , , .

$query = Post::query();

, , $me.

, ,

$followingUserIds = $me
    ->following()
    ->where('followable_type', User::class)
    ->lists('followable_id');

,

$myFieldIds = $me->schoolables()->lists('field_id');
$sharedFieldUserIds = Schoolable::whereIn('field_id', $myFieldIds)->lists('user_id');

, ,

$mySchoolIds = $me->schoolables()->lists('school_id');
$sharedSchoolUserIds = Schoolable::whereIn('school_id', $mySchoolIds)->lists('user_id');

:

$query->where(function($inner) use ($me) {
    $inner->where('posts.author_type', User::class);
    $inner->where('posts.author_id', $me->id);
});

, , ()

$query->orWhere(function($inner) use ($followingUserIds) {
    $inner->where('posts.author_type', User::class);
    $inner->whereIn('posts.author_id', $followingUserIds);
});

, , ,
: ->whereHas, , .

$query->orWhereHas('comments', function($subquery) use ($followingUserIds) {
    $subquery->where('comments.author_type', User::class);
    $subquery->whereIn('comments.author_id', $followingUserIds);
});

.

,

$query->orWhere(function($inner) use ($sharedFieldUserIds) {
    $inner->where('posts.author_type', User::class);
    $inner->whereIn('posts.author_id', $sharedFieldUserIds);
});

,

, poeple school_id

$query->orWhere(function($inner) use ($sharedSchoolUserIds) {
    $inner->where('posts.author_type', User::class);
    $inner->whereIn('posts.author_id', $sharedSchoolUserIds);
});

,

$posts = $query->get();

, , - . , , Post Comments . , , , -, , Post::authoredBySomeoneFollowedByUser($me), , -, .