how to select records from database with multiple selected option values

Level 1

and my query is

$properties = Properties::where('status', 1)
            ->when(request()->property_purpose, function ($query) {
                $query->where('property_purpose', request()->property_purpose);
            })
            ->when($city, function ($query) {
                // city
                $query->where('city', request()->city);
            })
            ->when($subcity, function ($query) {
                // sub city
                $query->where('subcity', request()->subcity);

            })->when(request()->get('ameneties'), function($query){
                
				$parts = request('ameneties');
                for( $i=0; $i<count($parts); $i++ ) {
                    		$query->WhereRaw( "find_in_set(".$parts[$i]." , properties.property_features)" );
                    
                }
            })

Level 104

in db data is stored comma separated like 114,45,19,20,55,21,33

There is your first problem... poor database design.

1 like

Level 1

@tykus it has been designed by someone else i m just doing some customization in it.

Level 104

@salmankhan2482 whoever designed it is irrelevant; it is still a bad design. The First Normal Form (1NF) exists precisely for this reason.

Level 1

@tykus how do you suggest to design the database for it. and then when i have to search through multiple selected option values.

like a properties table where each property have the amenities like parking, gym, security, water front etc. then how to add records with multiple amenities plus when i want to search record with multiple amenities, so how should i design the schema and how to write the search query for it

Level 1

@tykus i will design that and add a one to many relationship design but how about search with these multiple values. there is a drop down of these multiple selected amenities and the user just type a word, select a purpose like rent or sale and then select multiple amenities. so how this search query has to be made then

Level 104

@salmankhan2482 Assuming these values 114,45,19,20,55,21,33 are IDs for amenities; you have a many-to-many relationship; between Property and Amenity. You need a Pivot Table to associate the two models.

Level 1

@tykus yes i made many to many relationship but the main problem i m facing is in search query

Level 1

and the search query is actually concatenating like

$properties = Properties::where('status', 1)
            ->when(request()->property_purpose, function ($query) {
                $query->where('property_purpose', request()->property_purpose);
            })
            ->when($city, function ($query) {
                // city
                $query->where('city', request()->city);
            })
            ->when($subcity, function ($query) {
                // sub city
                $query->where('subcity', request()->subcity);

            })
            ->when($town, function ($query) {
                // town
                $query->where('town', request()->town);
            })
            ->when($area, function ($query) {
                // area
                $query->where('area', request()->area);
            })
            ->when(request('property_type'), function ($query) {
                $query->where('property_type', request('property_type'));
            })
            ->when($min_price != 0 && $max_price != 0, function ($query) {
                $query->whereBetween('price', [(int)request()->get('min_price'), (int)request()->get('max_price')]);
            })
            ->when($min_price != 0 && $max_price == 0, function ($query) {
                $query->where('price', '>=', [(int)request()->get('min_price')]);
            })
            ->when($min_price == 0 && $max_price != 0, function ($query) {
                $query->where('price', '<=', [(int)request()->get('max_price')]);
            })
            ->when($min_price == 0 && $max_price == 0, function ($query) {
                //no condition to run
            })
            ->when(request()->get('furnishings'), function ($query) {
                $query->where('property_features', 'like', '%'.request()->get('furnishings').'%');
            })
            ->when(request()->get('ameneties'), function($query){
                
                foreach(request('ameneties') as $amenity) {
                    $query->whereRaw("find_in_set($amenity,property_features)");
                }
            
            })
            ->when($min_area != 0 && $max_area != 0, function ($query) {
                $query->whereBetween('land_area', [(int)request()->get('min_area'), (int)request()->get('max_area')]);
            })
            ->when($min_area != 0 && $max_area == 0, function ($query) {

                $query->where('land_area', '>=', [(int)request()->get('min_area')]);
            })
            ->when($min_area == 0 && $max_area != 0, function ($query) {
                $query->where('land_area', '<=', [(int)request()->get('max_area')]);
            })
            ->when($min_area == 0 && $max_area == 0, function ($query) {
            })->get();

Level 104

@salmankhan2482 what is the problem with the query; you don't constrain using the relationship yet?

Level 1

@tykus its the old one but now i am confused like how i can query using relationships ?

Level 12

Level 104

@salmankhan2482 assuming you have an amenities relationship defined on the Property model; you can use the whereHas Builder method to query the relationship:

//...
->when(request()->get('amenities',), function ($builder, $amenities) {
    $builder->whereHas('amenities', fn ($builder) => $builder->whereIn('id', $amenities));
})	
//...

You can get count of the matching amenities also to rank the results by most matching amenities:

->withCount(['amenities' => fn ($builder) => $builder->whereIn('id', $amenities))
->when(request()->get('amenities',), function ($builder, $amenities) {
    $builder->->whereHas('id',  fn ($builder) => $builder->whereIn('id', $amenities))
})	
->orderBy('amenities_count', 'desc')

Level 1

@tykus thank you so much sir but its getting complex for me as i m not getting it. but thank you so much for the reply

Level 104

@salmankhan2482 what is complex?

Level 1

@tykus

$properties = Properties::where('status', 1)
            ->when(request()->property_purpose, function ($query) {
                $query->where('property_purpose', request()->property_purpose);
            })
            ->when($city, function ($query) {
                // city
                $query->where('city', request()->city);
            })
            ->when($subcity, function ($query) {
                // sub city
                $query->where('subcity', request()->subcity);

            })
            ->when($town, function ($query) {
                // town
                $query->where('town', request()->town);
            })
            ->when($area, function ($query) {
                // area
                $query->where('area', request()->area);
            })
            ->when(request('property_type'), function ($query) {
                $query->where('property_type', request('property_type'));
            })
            ->when($min_price != 0 && $max_price != 0, function ($query) {
                $query->whereBetween('price', [(int)request()->get('min_price'), (int)request()->get('max_price')]);
            })
            ->when($min_price != 0 && $max_price == 0, function ($query) {
                $query->where('price', '>=', [(int)request()->get('min_price')]);
            })
            ->when($min_price == 0 && $max_price != 0, function ($query) {
                $query->where('price', '<=', [(int)request()->get('max_price')]);
            })
            ->when($min_price == 0 && $max_price == 0, function ($query) {
                //no condition to run
            })
            ->when(request()->get('furnishings'), function ($query) {
                $query->where('property_features', 'like', '%'.request()->get('furnishings').'%');
            })
            ->when(request()->get('amenities',), function ($builder, $amenities) {
                $builder->whereHas('amenities', fn ($builder) => $builder->whereIn('id', $amenities));
            })
            ->when($min_area != 0 && $max_area != 0, function ($query) {
                $query->whereBetween('land_area', [(int)request()->get('min_area'), (int)request()->get('max_area')]);
            })
            ->when($min_area != 0 && $max_area == 0, function ($query) {

                $query->where('land_area', '>=', [(int)request()->get('min_area')]);
            })
            ->when($min_area == 0 && $max_area != 0, function ($query) {
                $query->where('land_area', '<=', [(int)request()->get('max_area')]);
            })
            ->when($min_area == 0 && $max_area == 0, function ($query) {
            });

i am getting this error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select count(*) as aggregate from properties where status = 1 and property_purpose = Rent and exists (select * from property_amenities inner join amenity_property on property_amenities.id = amenity_property.amenity_id where properties.id = amenity_property.property_id and id in (112)))

Level 1

@tykus i created the many to many relationship and the crud is going all well but when i do search it throws this error. following is the realtionship defined in the associated models

App\Properies.php

public function amenities()
 {
     return $this->belongsToMany(PropertyAmenity::class, 'amenity_property', 'property_id', 'amenity_id');
 }

App\PropertyAmenity.php

public function properties()
{
    return $this->belongsToMany(Properties::class, 'amenity_property', 'amenity_id', 'property_id');
}

Level 1

@tykus sir ?

lat4732