CODEWITHSUNDEEP
phpmysqllaraveleloquent
Chirag Arora
Chirag Arora

Reputation: 357

Get data from multiple sql tables using eloquent with laravel

What i want to achieve?

Show user which pacts he is following.

What I am trying

I have designed two tables which are 'pacts' & 'pacts_follwers'

Table 'pacts' has the details of all the pacts

Table 'pacts_follwers' has details of users following a particular pact.

These links will give you the images of both the tables

For Schema Refer Images Pacts Table

pacts_follwers

So how to get pacts that user is following.

What I have tried?

Sql Query

SELECT pacts.*, (SELECT pactsid FROM pacts_follwers WHERE pacts.id = pacts_follwers.pactsid
and pacts_follwers.userid = 2 ) as pactID FROM `pacts`

Sql query Result enter image description here

This query will give pactId some value, where the value is null means the user is not following that pact. If this is the solution then i would need Eloquent for this which i am unable to make.

1st table pacts

id
title
about
created_at
updated_at
pactsImage

2nd table pacts_follwers

id
pactsid
userid
created_at
updated_at

Controller Code

$pacts = DB::select("SELECT pacts.*, (SELECT pactsid FROM pacts_follwers WHERE pacts.id =
pacts_follwers.pactsid and pacts_follwers.userid = ".Auth::id()." ) as pactID FROM `pacts`");

Upvotes: 0

Views: 82

Answers (1)

senty
senty

Reputation: 12847

You need to setup hasManyThrough relationship for User and Pact.

class User extends Model {

   public function pacts() {
      return $this->hasManyThrough(
           Pact::class,
           PactFollower::class
           'userid',
           'pactsid'
      );
   }
}

I don't fully understand if you want to achieve "get user's all pacts" or "if pact is followed by user". Either way, you need to setup related relationships.

Or really simple (and not efficient way)

class Pact extends Model {

    public function followers() {
       return $this->hasMany(PactFollower::class, 'pactsid')
    }
}

Now you can use something like 

$userIdsForPact = Pact::followers()->pluck('userid');

if ($userIdsForPact->has($user->id)) {
 // your operation
}

Edit: For "if pact is followed by user", you need to setup belongsToThrough relationship. It doesn't come out of the box with Laravel but staudenmeir/belongs-to-through package should serve you well.

After setting the relationship properly, you can use something like this.

Pact::with('user')->get();

Or add some methods in your Pact model:

public function followedByUser($user) {
    return $this->users->has($user);
}

Upvotes: 2

Related Questions