filter query for multiple checkboxes

I need to filter users depending on a market category ( the user could signup and add multiple categories in his profile )

Models USER->MarketCategoryTransactions->MarketCategory

Now I need to show users filtered on this category.

blade

          <div class="sidebar-widget">
                            <h3>Market Segment Speciality</h3>
                            <div class="tags-container">
                                @foreach ($market as $item )
                                    <div class="tag">
                                        <input type="checkbox" name="market[]" value="{{ $item->id }}" id="{{ $item->name }}"/>
                                        <label for="{{ $item->name }}">{{ $item->name }}</label>
                                    </div>
                                @endforeach
                            </div>
                            <div class="clearfix"></div>
                        </div>

Controller

    $city = $request->city;
        $locality = $request->locality;
        $market = $request->market;

        $brokers = User::when($city, function ($query) use ($city) {$query->where('city', $city);})
                    ->when($locality, function ($query) use ($locality) {$query->where('locality', $locality);})
                    ->where('status', 1)
                    ->paginate(10)
                    ->withQueryString();

What will be then controller query ?

SilenceBringer

4 years ago

Best Answer

Level 55

@founderstartup something like

        $brokers = User::when($city, function ($query) use ($city) {$query->where('city', $city);})
                    ->when($locality, function ($query) use ($locality) {$query->where('locality', $locality);})
                    ->when($market, function ($query) use ($market) {
	$query->whereHas(
		'MarketCategoryTransactions.MarketCategory',
			function ($query) use ($market) { return $query->whereIn('market_id', $market); }
	);
})
                    ->where('status', 1)
                    ->paginate(10)
                    ->withQueryString();

maybe contains some mistakes (because I don't know your fields), but at least it should give you an idea

1 like

FounderStartup

4 years ago

Level 5

@SilenceBringer Thanks Chief. The query seems to work. But its a bit more complicated query and I am not getting the desired records. Shall I discuss here or create a new post ? :)

FounderStartup

4 years ago

Level 5

@SilenceBringer

Posting the modified query :

    $city = $request->city;
        $locality = $request->locality;
        $brokermarket = $request->market;
        $brokercategories = $request->categories;
        $brokertypes = $request->types;

        // dd($market, $categories, $types, $city, $locality);

        $brokers = User::when($city, function ($query) use ($city) {$query->where('city', $city);})
                    ->when($locality, function ($query) use ($locality) {$query->where('locality', $locality);})
                    ->when($brokermarket, function ($query) use ($brokermarket) {
                        $query->whereHas('market.marketname',
                                function ($query) use ($brokermarket) { return $query->whereIn('marketcat_id', $brokermarket); }
                        );})

User model :

    public function market()
    {
        return $this->hasMany(BrokerMarketCategoryTransaction::class, 'broker_id', 'id');
    }

BrokerMarketCategoryTransaction Model

  public function marketname(){
        return $this->belongsTo(BrokerMarketCategory::class,'marketcat_id','id');
     }

Is it the correct way ?

But the results are not as desired.

FounderStartup

4 years ago

Level 5

Suppose there are two users user1 and user2

user1 is in city1 and category1 and category2 user2 is in city1 and category2

Now if I select the city as city1 and category as category1 , both the users are showing but only user 1 should show. This is because both are in city1 so both are showing , but logically its incorrect. How to do this ?

jhgjhgjh

2 years ago

Level 1

zxxzfdsdfsdf

Please or to participate in this conversation.