Level 102
Can you show the code?
Nov 15, 2021
11
Level 2
Merge two collections that are dependent on one another
So I have a table called 'communities' and I want to know whether a particular auth user is subscribed that that community or not. So I have created another table to keep track called 'Subscriptions' to keep track whether a auth user have subscribed to that community or not. The subscription table have two columns: community_id and profile_id each are primary keys of community and profile tables. I want to render a view where a the card of the community need to show subscribe or unsubscribe based on their subscription. The fresh approach I took is that I did gather a collection of communities from the controller and wrote another query in the view to know whether a user is subscribed or not. I know this is not the way of doing it. I want to pass a value from the controller itself like attaching a variable to show subscription. But to do that I may need to merge the two tables but the thing its the second query that checks in the subscription table needs to find a row if that particular row in the collection has a row inside the subscription along with that auth user. How do I solve this merge? is joins the correct approach?
Level 2
public function search(Request $request){
$search = request('search');
$communities = DB::table('communities')
->where('name', 'LIKE', "%{$search}%")
->leftJoin('subscriptions', 'communities.id', '=', 'subscriptions.community_id')
->where('subscriptions.community_id','=', 'communities.id')
->where('subscriptions.profile_id','=', Auth::user()->profile->user_id)
->get();
return view('search.index',['communities' => $communities]);
}
Level 102
Level 2
@Sinnbeck done...thanks
Level 102
That is a very fine way of doing it if you dont need any model/eloquent magic :) And I assume there can be multiple communities for each user?
Level 2
@Sinnbeck yes right user can have multiple communities under them. I will do it in eloquent too if you can show, also it will be better if you can show in sql
Level 102
@dhrubanka Well to get it as eloquent/models you just need to replace the start
This will return Community models :)
$communities = Commmunity::query()
->where('name', 'LIKE', "%{$search}%")
->leftJoin('subscriptions', 'communities.id', '=', 'subscriptions.community_id')
->where('subscriptions.community_id','=', 'communities.id')
->where('subscriptions.profile_id','=', Auth::user()->profile->user_id)
->get();
Level 2
@Sinnbeck unfortunately not working sir, still returning null collection
Level 102
@dhrubanka use either debugbar or clockwork to get the actual query and test it in your database manager
Level 102
What do you mean by a null collection?
Level 2
@Sinnbeck I think my logic is somewhat wrong. If I skip the where part after left join, the join returns as expected but I need the rows matched with the profile_id too, not only with community_id of subscriptions table.
Please or to participate in this conversation.