Level 122
once you know category, you can query all posts that have that category, and then get distinct cities from posts in order to create the second dropdown
Dec 30, 2020
11
Level 4
Dynamic Cascading Dropdown with Livewire
I'm trying to implement Snapey's dynamic cascading dropdown with livewire, but my table relations are not like the one in his example. So there is no direct relation between my categories table and the cities table. But they both have a relation with my posts table.
- categories table ID, NAME
- cities table ID, NAME
- posts table ID, NAME, category_id, city_id
The first selection should be the category and the second should show the cities related to that category. The goal is to filter the posts with a certain category and a certain city.
How can i show only the categories in the first dropdown that has posts, and than in second dropdown only cities that are in the selected category.
<?php
namespace App\Http\Livewire;
use App\Models\City;
use App\Models\Category;
use Livewire\Component;
class FilterResults extends Component
{
public $category;
public $cities=[];
public $city;
public function render()
{
if(!empty($this->category)) {
$this->cities = City::where('category_id', $this->category)->get();
}
return view('livewire.filter-results')
->withCategories(Category::orderBy('name')->get());
}
}
Level 4
@snapey I'm struggling with the query. Can you give me an example?
I tried this but that's not working.
public function render()
{
if(!empty($this->category)) {
$this->cities = City::has('posts')->where('category_id', $this->category)->get();
}
return view('livewire.filter-results')
->withCategories(Category::orderBy('name')->get());
}
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category_id' in 'where clause' (SQL: select * from `cities` where exists (select * from `posts` where `cities`.`id` = `posts`.`city_id`) and `category_id` = 4)
Level 122
Your query is applying the category to the cities table and not to the posts table. You need to add the where to the posts
Something like this, but I don't know if has supports this technique
$this->cities = City::has('posts', function($query) {
$query->where('category_id', $this->category);
})->get();
Level 4
@snapey I'm getting an error, does that mean it's not supporting this technique as you said?
strtolower() expects parameter 1 to be string, object given
Level 122
Where does this error occur? Just the message is usually not useful
Level 4
@snapey as soon as i select a category from the dropdown
vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:826
protected function invalidOperator($operator)
{
return ! in_array(strtolower($operator), $this->operators, true) &&
! in_array(strtolower($operator), $this->grammar->getOperators(), true);
}
Livewirecomponent. })->get();
public function render()
{
if(!empty($this->category)) {
$this->cities = City::has('posts', function($query) {
$query->where('category_id', $this->category);
})->get();
}
Level 4
@snapey Any idea what could cause this?
Level 4
Anyone else a suggestion?
Level 4
I changed the query a little bit, and to me it looks like the query is ok, but the second select (cities) wont update.
- The first select is doing exactly as supposed, get only those categories that are attached to a post. -The second select is not showing any results, but in the queries i see that the correct cities are being queried
<?php
namespace App\Http\Livewire;
use App\Models\City;
use App\Models\Category;
use Livewire\Component;
class FilterResults extends Component
{
public $categories;
public $category;
public $cities=[];
public $city;
public function mount()
{
$this->refreshData();
}
public function refreshData()
{
$this->categories = Category::has('posts')->orderBy('name')->get();
if(!empty($this->category)) {
$this->cities = City::whereHas('posts', function($query) {
$query->where('category_id', $this->category);
})->with('posts')->get();
}
}
public function render()
{
$this->refreshData();
return view('livewire.filter-results');
}
}
select * from `categories` where `categories`.`id` in (4, 2)
select * from `categories` where exists (select * from `posts` where `categories`.`id` = `posts`.`category_id`) order by `name` asc
select * from `cities` where exists (select * from `posts` where `cities`.`id` = `posts`.`city_id` and `category_id` = '4')
select * from `posts` where `posts`.`city_id` in (3, 4)
select * from `categories` where `categories`.`id` in (4)
select * from `cities` where `cities`.`id` in (3, 4)
I have 3 posts:
- 1 post with cat_id 2 and city_id 2
- 1 post with cat_id 4 and city_id 3
- 1 post with cat_id 4 and city_id 4
Level 122
Sorry, I have been out.
From the docs
If you need even more power, you may use the whereHas and orWhereHas methods to define additional query constraints on your has queries, such as inspecting the content of a comment:
$this->cities = City::whereHas('posts', function($query) {
$query->where('category_id', $this->category);
})->get();
Level 4
@snapey No need to apologise, i don't expect anyone to help me out all day. But i appreciate the help all the more.
But i did exactly that, as you can see in my previous reply (well i tried it like your example and after that i added ->('posts')->get();. But both ways did not do the work. The cities dropdown stays empty.
Please or to participate in this conversation.