Retrieve Data From Pivot Table

Are you looking for all users who share all expectations with the current user, or all users who share one particular expectation of the current user?

@tykus_ikus hello again!

I am looking for this: let's say current user has checked 2 options of interest.

so I want to show him other users that were choosing at least one option of what he picked too.

Try this (untested):

// So you have a profile:
$profile = Profile::find($id);


$mutuals = Profile::whereHas('expectations', function($query) use ($profile)
{
  $query->whereIn('expectations.id', $profile->expectations->lists('id');
})->get();

@tykus_ikus the semicolon after lists('id'); is not placed correctly. I am trying to find out why meanwhile.

@osherdo yes, there should be another closing paren there

@tykus_ikus thanks. It shows the view but nothing special appears. EDIT: I have added also the route for this view:

Route::get('hub','HubController@mutual');

and it gives me this error:

Undefined variable: id

How should I define this variable?

Which user's profile are you viewing, Auth::user()? If so, change $profile = Profile::find($id); to $profile = Auth::user()->profile;

I don't know what should be in the view, but you should be expecting to have a Collection of User objects?

I did it. Now it shows a blank page without any content at all.

Yes I do expect a collection of other similar users and another parts of the view which I don't see now. This is the function now:

 public function mutual()
    { 
      //$mutualUsers = DB::table('expectation_profile')->where(expectation_id,$user->Auth::user);
// So you have a profile:
$profile = Auth::user()->profile;

$mutuals = Profile::whereHas('expectations', function($query) use ($profile)
{
  $query->whereIn('expectations.id', $profile->expectations->lists('id'));
})->get();
    }

You are not seeing the results because you are not returning anything from your method; is this in the controller or the model?

But before that it showed data from other method but now it shows nothing.

This is all in my controller.

controller code:

http://paste.ofcode.org/397FqGjWXRyCH25n64jGSrs

It did? Not based on the code you shared in the OP.

This is basic stuff - you have to pass the data from the controller to the view; just try the following at the bottom of the mutuals() method:

return view('name_of_view', compact('mutuals'));

check out the link I pasted above http://paste.ofcode.org/397FqGjWXRyCH25n64jGSrs

there's a function called hub and it returns the view with variable.

I have changed the method:

public function mutual()
    { 
$profile = Auth::user()->profile;

$mutuals = Profile::whereHas('expectations', function($query) use ($profile)
{
  $query->whereIn('expectations.id', $profile->expectations->lists('id'));
})->get();

return view('hub', compact('mutuals'));

    }

it shows the error: Undefined variable: user

what's the value for the user in this case?

Wait, are you trying to reuse the same view as for a second controller method? Yes, you can do this but it requires that you ensure that both controller methods provide variables with the same name to the view. If your intention was to add new extra data to the original view then move the query into the original method:

  public function hub()
  {
    $user = Auth::user();
    $profile = $user->profile;

    $mutuals = Profile::whereHas('expectations', function($query) use ($profile)
    {
        $query->whereIn('expectations.id', $profile->expectations->lists('id'));
    })->get();


    if($user)
    {
      return view('hub', compact('user', 'mutuals'));
    }
    else
    {
      return redirect('auth/login');
    }
}

Iterate over the $mutuals in the view:

<ul>
@foreach($mutuals as $mutual)
  <li>{!! $mutual->name !!}</li>
@endforeach 
</ul>

Have you watched the introductory from scratch videos - they're free and will give you a good foundation in the MVC / general web development principles.

@tykus_ikus

I have written the method as you suggested above. it shows no results from the query. are you trying to query the expectation_profile (pivot table)? because I don't see it in your query and it might be the problem. http://2.1m.yt/2qmdV-.png

The thing is I have watched L5 fundamental series on Laracasts when started to learn Laravel. I will watch the from scratch as well. I was trying to find specifically how to write a bit complex sql query like you just did and it's hard to find in the videos. plus there's not so much written material for this so it's kinda hard comparing to plain php.

It looks like you are getting results - there are seven <li> bullets on the page. I assumed that a user has a name field, perhaps it doesn't; just swap name for whatever property:

<ul>
@foreach($mutuals as $mutual)
  <li>{!! $mutual->your_property_here !!}</li>
@endforeach 
</ul>

I need the user name. and the column is appearing in the users table and not in the profile table. What should I change in the query?

@tykus_ikus please help :( you're the only one I can depend on on this one..

Right, we have a collections of profiles, not users... Do you have a belongsTo relationship from your Profile model to your user? In that case, you can use:

{!! $mutual->user->name; !!}

Hey tell me what is the connection with current user_id and expection_id ?? and saw your profile table also ??? and tell me what is the output u need????

@tykus_ikus Yes I do. In the model Profile.php:

 public function user()
  {
    return $this->belongsTo('App\User');
  }

You're a legend! I have just output the associated user that match my criteria!

could you explain me your query that you've suggested earlier?

 $mutuals = Profile::whereHas('expectations', function($query) use ($profile)
    {
        $query->whereIn('expectations.id', $profile->expectations->lists('id'));
    })->get();

Also about this- do I access the user model and then access the name property? I get it right?

{!! $mutual->user->name; !!}

Thank you so much!!

@Hamelraj each user has his own expectations stored in a db. I need to output the user Another users that matches at least one of his expectations (there are 3 expectation checkboxes) . So if I am the user and I into cardio workout - the app won't show me people that are interested in weight lifting. get the idea?

@osherdo on the $mutuals = Profile::whereHas closure you'll also want a ->has('user'); to make sure you eliminate any potential profiles that have deleted user accounts.

@tykus_ikus although I have found good explanation in the documentation I would like to hear you on what I was asking you about.

@wells I did not get how do I implement this in my query and why is it good. I am sorry I might have misunderstood you.

@osherdo you can do this...

    $mutuals = Profile::whereHas('expectations', function($query) use ($profile)
    {
        $query->whereIn('expectations.id', $profile->expectations->lists('id'));
    })->has('user')->get();

@wells okay thanks. what does that make to the query? how does it improve it?

First of all whereHasdoes two things for you in one - it ensures that in the Collection of profiles being returned:

1. each profile being returned has at least one expectation, and
2. those profiles are further constrained by a where condition (in this case, that the expectation id is one of the current user profile's expectations)

@wells addition of ->has('user') is to ensure that there is also a User associated with the profile -- as a result, you don't have to worry about checking each profile has a User object before attempting to display the name property.

@tykus_ikus god know why but now I get an error:

ErrorException in 7340f90cc5faf1a298fcc646b7248b22 line 42: Trying to get property of non-object (View: /var/www/sports-application/resources/views/hub.blade.php)

It worked perfectly yesterday and now it shows me this error. any idea why?

it refers to the first line in the snippet below as the problematic line:

@foreach($mutuals as $mutual)
  <li>{!! $mutual->user->name; !!}
</li>
@endforeach 
</ul>   

Try to die and dump the $mutuals collection in the controller in inspect it before it goes to the view:

dd($mutuals->toArray());

it is very possible in your dev. environment there is a Profile that is orphaned from it's User, hence when you try to get the user associated with the profile ($mutual), it's not there throwing the error you have seen.

If you are in development, and you are happy that your migrations are sound, and you have Seeders set up; you can regularly refresh your development database (php artisan migrate:refresh --seed) to ensure the integrity of you data.

big like @tykus_ikus . I have this covered again well. also documented this error so I know where I should refer if I tackle this one again. Thanks so much..

So how do I make sure id's are not mixed when for instance 2 users registering at the same time? Does the db gets and send requests per session or it depends on an id in the database.

You don't have to worry about this, if I understand the question you are asking...

If I recall correctly you are creating the Profile through the relationship with User - this simply means that the INSERT statement for the new Profile includes the user_id from the User instance. There is no magic happening really.

@tykus_ikus okay . good description. Must buy you a beer sometime as a gesture for all your help!

Best regards.