Get data from multiple sql tables using eloquent with laravel

Question

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

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

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`");

Do you want to achieve get user's pacts - smt likeUser::pacts? You need to setup hasManyThrough relationship so that you can easily use User::pacts — senty
– senty, Commented Dec 28, 2019 at 17:25
Just indicate anyhow whether the current logged in user following the particular pact. No data is kept in users table regarding pacts the user is following. Everything is in pacts_follwers and pacts table. please see the images. What I have created is a lousy solution. I need something better along with the eloquent code. — Chirag Arora
– Chirag Arora, Commented Dec 28, 2019 at 17:27
@ChiragArora you should really look into Eloquent Relationships, read through it and try to understand and implement it (might fail a few times, but once you learn it, it'll definitely be worth it) — Arun A S
– Arun A S, Commented Dec 28, 2019 at 17:31

senty · Accepted Answer · 2019-12-28 17:45:20Z

2

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);
}

edited Dec 28, 2019 at 17:45

answered Dec 28, 2019 at 17:33

senty

12.9k31 gold badges142 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chirag Arora Over a year ago

"if pact is followed by user". I will learn this eloquent relationship and then i will re-attempt this. Thanks anyways.

Collectives™ on Stack Overflow

Get data from multiple sql tables using eloquent with laravel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related