How to Efficiently Join Three Tables in Laravel to Retrieve Posts from Followed Users?

Laravel: Joining Three Tables for Data Retrieval

In this scenario, you're building a Twitter-like application where you need to display posts from users whom the current user is following. Since you have three tables, namely Users, Followers, and Shares, understanding how to join them effectively is crucial for retrieving the desired data.

The goal is to retrieve all Shares where the user_id from the Shares table matches the follower_id from the Followers table, and the user_id from the Followers table matches the id from the Users table.

Using the Database Query Builder

You tried using the following query:

<code class="php">$shares = DB::table('shares')
    ->leftjoin('followers', 'shares.user_id', '=', 'followers.follower_id')
    ->leftjoin('users', 'followers.user_id', '=', 'users.id')
    ->where('users.id', 3)
    ->where('shares.user_id', 'followers.follower_id')
    ->get();</code>

However, the issue with this query is in the join condition for shares and followers. The correct join should be:

<code class="php">->leftjoin('followers', 'shares.user_id', '=', 'followers.user_id')</code>

Using Models for Enhanced Functionality

Instead of using the database query builder, it's recommended to use Laravel models for a more structured and efficient approach to database operations.

Here's how the models can be defined:

<code class="php">class User extends Model {
    public function shares() {
        return $this->hasMany('Share');
    }
    public function followers() {
        return $this->belongsToMany('User', 'follows', 'user_id', 'follower_id');
    }
    public function followees() {
        return $this->belongsToMany('User', 'follows', 'follower_id', 'user_id');
    }
}
class Share extends Model {
    public function user() {
        return $this->belongsTo('User');
    }
}</code>

Query Execution Using Models

Once the models are defined, you can execute queries like this:

<code class="php">$my = User::find('my_id');

// Retrieves all shares by users that I follow
// eager loading the "owner" of the share
$shares = Share::with('user')
    ->join('follows', 'follows.user_id', '=', 'shares.user_id')
    ->where('follows.follower_id', '=', $my->id)
    ->get('shares.*'); // Notice the shares.* here

// prints the username of the person who shared something
foreach ($shares as $share) {
    echo $share->user->username;
}</code>

In this example, the query retrieves all Shares where the user_id from the Shares table matches the follower_id from the Followers table, and the user_id from the Followers table matches the current user's id stored in the $my variable.

The above is the detailed content of How to Efficiently Join Three Tables in Laravel to Retrieve Posts from Followed Users?. For more information, please follow other related articles on the PHP Chinese website!