Problem with displaying json results from database column?

Question

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, 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 I insert json values in user_preferences column in users table, for example, ["house", "flat"]. I want that logged in user to display all properties on startpage that have values that he checked in form. With current code I get this error

Column not found: 1054 Unknown column 'user_preferences' in 'where clause' (SQL: select count(*) as aggregate from properties where active = Q or active = A and user_preferences = garage)

I need help to write correct query in my controller.

CategoryController.php

public function index(Category $category, Property $property, User $user)
{
    $preferedAdsResults = $property->where('active', 'Q')
    ->orWhere('active', 'A')
    ->orderBy('page_views', 'desc')
    ->with('category', 'user')
    ->where('user_preferences', 'garage')
    ->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])}}">
                        @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>

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)->orderBy('priority', 'asc');
}

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')->whereIn('active', ['Q','A']);
}

So what is the problem you are asking us to help you solve. This sounds more like a requirement than a coding issue — RiggsFolly
– RiggsFolly, Commented Jul 26, 2019 at 13:05
To display all properties that have those checked values, I will edit post, I get error. — mrmar
– mrmar, Commented Jul 26, 2019 at 13:07
did you try use whereIn? laravel.com/docs/5.8/queries#where-clauses — Roman Meyer
– Roman Meyer, Commented Jul 26, 2019 at 13:08
Simple user_preferences column does not exist in the properties table !!! — RiggsFolly
– RiggsFolly, Commented Jul 26, 2019 at 13:10

Ghiffari Assamar · Accepted Answer · 2019-07-26 16:08:04Z

1

use whereHas if you want do eloquent join

public function index(Category $category, Property $property, User $user)
{
    $preferedAdsResults = $property->where('active', 'Q')
    ->orWhere('active', 'A')
    ->whereHas('user_preferences', function($q)use($variable){
    $q->where('column',$variable)})->paginate(5);

    return view('startpage', compact('preferedAdsResults'));
}

docs about wherehas laravel

and if you have dynamically where inside whereHas you need to pass it using use method like in my example

answered Jul 26, 2019 at 16:08

Ghiffari Assamar

6614 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Problem with displaying json results from database column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related