I have project for ads/properties in Laravel. I have has many relationship between user and property and user and category, also many to many relationship between property and category. Here are my tables.
users (id, first_name, last_name, user_preferences)
properties (id, user_id, location, price)
categories (id, category)
category_property (id, category_id, property_id)
As logged in user when I check one or multiple checkboxes in my edit profile page and submit form I insert json values in user_preferences column in users table, for example, ["house", "flat"]. And those are values from category column in categories table. I want that logged in user to display all properties that have those values that he checked in form. I am having trouble writing query in my controller that will join those tables and get those filtered properties. With current code I get all properties. Any help would be greatly appreciated. Here is my code.
CategoryController.php
public function index(Category $category, Property $property, User $user)
{
$preferedAdsResults = $property
->orderBy('page_views', 'desc')
->paginate(5, ['*'], 'preferedAdsResults');
return view('startpage', compact('preferedAdsResults'));
}
edit.blade.php
<div class="row page-hero d-flex align-items-center justify-content-center">
<label for="preferences" class="text-center">Select your preferences</label>
</div>
<div class="row">
<div class="col-md-1" style="margin-right:15px; margin-left:60px;">
<div class="custom-control custom-checkbox">
<input id="house" name="user_preferences[]" value="house" type="checkbox" class="custom-control-input" @if(is_array(old('user_preferences', $user->user_preferences)) && in_array('house', old('user_preferences', $user->user_preferences))) checked @endif>
<label class="custom-control-label" for="house">house</label>
</div>
</div>
<div class="col-md-1" style="margin-right:15px;">
<div class="custom-control custom-checkbox">
<input id="flat" name="user_preferences[]" value="flat" type="checkbox" class="custom-control-input" @if(is_array(old('user_preferences', $user->user_preferences)) && in_array('flat', old('user_preferences', $user->user_preferences))) checked @endif>
<label class="custom-control-label" for="flat">flat</label>
</div>
</div>
<div class="col-md-1" style="margin-right:50px;">
<div class="custom-control custom-checkbox">
<input id="room" name="user_preferences[]" value="room" type="checkbox" class="custom-control-input" @if(is_array(old('user_preferences', $user->user_preferences)) && in_array('room', old('user_preferences', $user->user_preferences))) checked @endif>
<label class="custom-control-label" for="room">room</label>
</div>
</div>
</div>
startpage.blade.php
<div class="col-2">
<h1>Prefered ads</h1>
@if (isset($preferedAdsResults))
@foreach ($preferedAdsResults as $preferedAdsResult)
<div class="row">
<a href="{{route('property.show',['id'=>$preferedAdsResult->id])}}">
<button type="button" class="btn btn-lg btn-primary" disabled style="margin-top:10px;">{{$preferedAdsResult->price}} eur</button>
<button type="button" class="btn btn-lg btn-primary" disabled style="margin-top:10px;">{{$preferedAdsResult->location}}/button>
<hr>
</a>
</div>
@endforeach
<div class="row">
<div class="col"></div>
<div class="col">{{ $preferedAdsResults->links() }}</div>
<div class="col"></div>
</div>
@endif
</div>
Category.php
public function property()
{
return $this->belongsToMany(Property::class);
}
public function users()
{
return $this->belongsTo(User::class);
}
Property.php
public function category()
{
return $this->belongsToMany(Category::class);
}
public function user()
{
return $this->belongsTo(User::class);
}
User.php
public function categories()
{
return $this->hasMany(Category::class);
}
public function property()
{
return $this->hasMany('App\Property', 'user_id', 'id');
}
UserController.php (where I update user profile)
public function update(StoreUserInfo $request, User $user)
{
if ( !($user->id == Auth::user()->id)) {
abort(404);
}
$request->validated();
if ($request->has('user_preferences')) {
$request->get('user_preferences');
}
$user_preferences = $request->input('user_preferences');
$query = Property::query();
if (!empty($user_preferences)) {
$query->whereHas('category', function ($query) use ($user_preferences) {
$query->whereIn('category', $user_preferences);
});
$user->where('id', $user->id)->update(
[
'user_preferences' => json_encode($request->user_preferences)
]
);
}
return redirect()->back()->with('message', 'User information updated');
}