Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

dr24's avatar
Level 2

Problem with joining three tables and then displaying results in Laravel

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');
}
0 likes
8 replies
mstrauss's avatar
mstrauss
Best Answer
Level 14

Hi @gacho

Since the User has a categories relationship, why not just store their preferences as a relationship as opposed to JSON in the user_preferences table?

$user->categories()->saveMany([
   App\Category::where('name', $preferenceOne),
   App\Category::where('name', $preferenceTwo),
]);

Then you can leverage the relationship to get the desired results, something like:

Property::whereHas('category', function (Builder $query) use (Auth::user()) {
    $query->where('user_id',  Auth::user()->id);
})->get();

Or if you want to continue using the JSON field, then the query would look something like:

$userCategories = Auth::user()->user_preferences; //assuming this is in proper array format, if not cast it to an array

Property::whereHas('category', function (Builder $query) use ($userCategories) {
    $query->whereIn('category_name',  $userCategories);
})->get();
dr24's avatar
Level 2

@mstrauss It works with the third option with json, with very small changes. Thank you very very much :)

dr24's avatar
Level 2

@mstrauss I just have one small problem now if you are willing to help me. When I logout I get error 'Trying to get property 'user_preferences' of non-object' where that line $userCategories = Auth::user()->user_preferences; is

mstrauss's avatar

Hmm... Perhaps the route (or controller) should be protected by auth middleware? Do you feel that non-authenticated users should be able to view this URL?

dr24's avatar
Level 2

@mstrauss Yes. They should be able to view this url, but they sholudn't be able to view this section prefered results for which you helped me in answer above. In other words, I want these prefered results to only show for current authenticated user.

mstrauss's avatar

Would you mind sharing the routes/web file and the view?

mstrauss's avatar

Try wrapping that section with the @auth blade directive

@auth

<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])}}">
                        @if ($preferedAdsResult->active == 'A')
                        <button class="btn btn-success" disabled="dissabled">Verified</button>
                        @endif

                        <img  class="img-fluid" src="/storage/{{$preferedAdsResult->main_photo['original_src']}}/{{$preferedAdsResult->main_photo['filename']}}" alt="Card image cap">
                        <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->quadrature}} m<sup>2</sup></button>
                        <button type="button" class="btn btn-lg btn-primary" disabled style="margin-top:10px; margin-bottom:10px;">{{$preferedAdsResult->city}}</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>

@endauth
1 like
dr24's avatar
Level 2

@mstrauss something similar like your reply. I solved it. I put in my controller index method Auth::check() with if statement to see everything if it is and else statement to show everything except prefered results. Thank you anyway, you helped me a lot as it is :)

1 like

Please or to participate in this conversation.