Extra Table Filters: Column and Global

In this last lesson of the Full CRUD of Posts section, let's add more filters to the posts table. We will add a filter for each column and another search input to search in all columns.

Search in Every Column

Let's start this lesson from the back-end. We need to add more condinional clauses for each column. Also, we will add a prefix search_ to each search variable.

app/Http/Controllers/Api/PostController.php:

use Illuminate\Database\Eloquent\Builder;
 
class PostController extends Controller
{
    public function index()
    {
        $orderColumn = request('order_column', 'created_at');
        if (! in_array($orderColumn, ['id', 'title', 'created_at'])) {
            $orderColumn = 'created_at';
        }
        $orderDirection = request('order_direction', 'desc');
        if (! in_array($orderDirection, ['asc', 'desc'])) {
            $orderDirection = 'desc';
        }
 
        $posts = Post::with('category')
            ->when(request('category'), function (Builder $query) { 
                $query->where('category_id', request('category')); 
            ->when(request('search_category'), function (Builder $query) { 
                $query->where('category_id', request('search_category'));
            })
            ->when(request('search_id'), function (Builder $query) {
                $query->where('id', request('search_id'));
            })
            ->when(request('search_title'), function (Builder $query) {
                $query->where('title', 'like', '%' . request('search_title') . '%');
            })
            ->when(request('search_content'), function (Builder $query) {
                $query->where('content', 'like', '%' . request('search_content') . '%');
            }) 
            ->orderBy($orderColumn, $orderDirection)
            ->paginate(10);
 
        return PostResource::collection($posts);
    }
    // ...
}

Now in the posts Composable, we need to pass those parameters.

resources/js/composables/posts.js:

import { ref, inject } from 'vue'
import { useRouter } from 'vue-router'
 
export default function usePosts() {
    const posts = ref({})
    const post = ref({})
    const router = useRouter()
    const validationErrors = ref({})
    const isLoading = ref(false)
    const swal = inject('$swal')
 
    const getPosts = async (
        page = 1,
        category = '', 
        search_category = '', 
        search_id = '',
        search_title = '',
        search_content = '', 
        order_column = 'created_at',
        order_direction = 'desc'
    ) => {
        axios.get('/api/posts?page=' + page +
            '&category=' + category + 
            '&search_category=' + search_category + 
            '&search_id=' + search_id +
            '&search_title=' + search_title +
            '&search_content=' + search_content + 
            '&order_column=' + order_column +
            '&order_direction=' + order_direction)
            .then(response => {
                posts.value = response.data;
            })
    }
    // ...
}

Next, we need to add the same variables in the PostsIndex Vue component. Also, for the updateOrdering and when watching search_category we need to pass all these parameters to the getPosts method.

And don't forget renaming: the variable category now should be search_category everywhere.

resources/js/components/Posts/Index.vue:

<template>
<div class="overflow-hidden overflow-x-auto p-6 bg-white border-gray-200">
        <div class="min-w-full align-middle">
            <div class="mb-4">
                <select v-model="selectedCategory" class="block mt-1 w-full sm:w-1/4 rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50"> 
                <select v-model="search_category" class="block mt-1 w-full sm:w-1/4 rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50"> 
                    <option value="" selected>&#45;&#45; Filter by category &#45;&#45;</option>
                    <option v-for="category in categories" :value="category.id" :key="category.id">
                        {{ category.name }}
                    </option>
                </select>
            </div>
            // ...
            <TailwindPagination :data="posts" @pagination-change-page="page => getPosts(page, selectedCategory)" class="mt-4" /> 
            <TailwindPagination :data="posts" @pagination-change-page="page => getPosts(page, search_category)" class="mt-4" /> 
        </div>
    </div>
</template>
 
<script setup>
import { onMounted, ref, watch } from "vue";
import { TailwindPagination } from 'laravel-vue-pagination';
import usePosts from "@/composables/posts";
import useCategories from "@/composables/categories";
 
const category = ref('') 
const search_category = ref('') 
const search_id = ref('')
const search_title = ref('')
const search_content = ref('')
const search_global = ref('') 
const orderColumn = ref('created_at')
const orderDirection = ref('desc')
const { posts, getPosts, deletePost } = usePosts()
const { categories, getCategories } = useCategories()
 
const updateOrdering = (column) => {
    orderColumn.value = column
    orderDirection.value = (orderDirection.value === 'asc') ? 'desc' : 'asc'
    getPosts(
        1,
        search_category.value, 
        search_id.value,
        search_title.value,
        search_content.value, 
        orderColumn.value,
        orderDirection.value
    );
}
 
onMounted(() => {
    getPosts()
    getCategories()
})
 
watch(selectedCategory, (current, previous) => { 
    getPosts(1, current)
}) 
watch(search_category, (current, previous) => { 
    getPosts(
        1,
        current
        search_id.value,
        search_title.value,
        search_content.value
    )
}) 
</script>

Now let's add search inputs for the columns. In the table head, we will add another row.

resources/js/components/Posts/Index.vue:

<template>
    <div class="overflow-hidden overflow-x-auto p-6 bg-white border-gray-200">
        <div class="min-w-full align-middle">
            <div class="mb-4">
                <select v-model="selectedCategory" class="block mt-1 w-full sm:w-1/4 rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
                    <option value="" selected>&#45;&#45; Filter by category &#45;&#45;</option>
                    <option v-for="category in categories" :value="category.id" :key="category.id">
                        {{ category.name }}
                    </option>
                </select>
            </div>
 
            <table class="min-w-full divide-y divide-gray-200 border">
                <thead>
                    <tr> 
                        <th class="px-6 py-3 bg-gray-50 text-left">
                            <input v-model="search_id" type="text" class="inline-block w-full rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50" placeholder="Filter by ID">
                        </th>
                        <th class="px-6 py-3 bg-gray-50 text-left">
                            <input v-model="search_title" type="text" class="inline-block w-full rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50" placeholder="Filter by Title">
                        </th>
                        <th class="px-6 py-3 bg-gray-50 text-left">
                            <select v-model="search_category" class="inline-block w-full rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
                                <option value="" selected>-- all categories --</option>
                                <option v-for="category in categories" :value="category.id">
                                    {{ category.name }}
                                </option>
                            </select>
                        </th>
                        <th class="px-6 py-3 bg-gray-50 text-left">
                            <input v-model="search_content" type="text" class="inline-block w-full rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50" placeholder="Filter by Content">
                        </th>
                        <th class="px-6 py-3 bg-gray-50 text-left"></th>
                        <th class="px-6 py-3 bg-gray-50 text-left"></th>
                    </tr> 
                    <tr>
                        // ...
                    </tr>
                </thead>
                // ...
            </table>
 
            <TailwindPagination :data="posts" @pagination-change-page="page => getPosts(page, search_category)" class="mt-4" />
        </div>
    </div>
</template>
 
<script setup>
// ...
</script>

Now you should see input in the table header, similar to this:

But if you try to search, it wouldn't work yet. The only filter that would work now is the category. This is because we haven't added a watch() for other inputs.

resources/js/components/Posts/Index.vue:

<script setup>
// ...
 
watch(search_category, (current, previous) => {
    getPosts(
        1,
        current,
        search_id.value,
        search_title.value,
        search_content.value
    )
})
watch(search_id, (current, previous) => { 
    getPosts(
        1,
        search_category.value,
        current,
        search_title.value,
        search_content.value
    )
})
watch(search_title, (current, previous) => {
    getPosts(
        1,
        search_category.value,
        search_id.value,
        current,
        search_content.value
    )
})
watch(search_content, (current, previous) => {
    getPosts(
        1,
        search_category.value,
        search_id.value,
        search_title.value,
        current
    )
}) 
</script>

The main thing in all of these watches is to pass the current value for the appropriate parameter. Now search should work for all the fields.

Global Search Input

The last thing, let's replace the filter by category above the table with the global search input.

So, first, let's replace this input and add a variable for it. Also, again, we need to add this search_global variable to all the watches.

resources/js/components/Posts/Index.vue:

<template>
    <div class="overflow-hidden overflow-x-auto p-6 bg-white border-gray-200">
        <div class="min-w-full align-middle">
            <div class="mb-4"> 
                <select v-model="search_category" class="block mt-1 w-full sm:w-1/4 rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
                    <option value="" selected>-- Filter by category --</option>
                    <option v-for="category in categories" :value="category.id" :key="category.id">
                        {{ category.name }}
                    </option>
                </select>
            </div> 
            <div class="mb-4 grid lg:grid-cols-4"> 
                <input v-model="search_global" type="text" placeholder="Search..." class="inline-block mt-1 w-full rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
            </div> 
 
            // ...
 
            <TailwindPagination :data="posts" @pagination-change-page="page => getPosts(page, search_category)" class="mt-4" />
        </div>
    </div>
</template>
 
<script setup>
import { onMounted, ref, watch } from "vue";
import { TailwindPagination } from 'laravel-vue-pagination';
import usePosts from "@/composables/posts";
import useCategories from "@/composables/categories";
 
const search_category = ref('')
const search_id = ref('')
const search_title = ref('')
const search_content = ref('')
const search_global = ref('') 
const orderColumn = ref('created_at')
const orderDirection = ref('desc')
const { posts, getPosts, deletePost } = usePosts()
const { categories, getCategories } = useCategories()
 
// ...
watch(search_category, (current, previous) => {
    getPosts(
        1,
        current,
        search_id.value,
        search_title.value,
        search_content.value,
        search_global.value
    )
})
watch(search_id, (current, previous) => {
    getPosts(
        1,
        search_category.value,
        current,
        search_title.value,
        search_content.value,
        search_global.value 
    )
})
watch(search_title, (current, previous) => {
    getPosts(
        1,
        search_category.value,
        search_id.value,
        current,
        search_content.value,
        search_global.value 
    )
})
watch(search_content, (current, previous) => {
    getPosts(
        1,
        search_category.value,
        search_id.value,
        search_title.value,
        current,
        search_global.value 
    )
})
watch(search_global, (current, previous) => { 
    getPosts(
        1,
        search_category.value,
        search_id.value,
        search_title.value,
        search_content.value,
        current
    )
}) 
</script>

Next, in the posts Composable, we need to add it to the getPosts method as we did with other search inputs.

resources/js/composables/posts.js:

import { ref, inject } from 'vue'
import { useRouter } from 'vue-router'
 
export default function usePosts() {
    const posts = ref({})
    const post = ref({})
    const router = useRouter()
    const validationErrors = ref({})
    const isLoading = ref(false)
    const swal = inject('$swal')
 
    const getPosts = async (
        page = 1,
        // category = '',
        search_category = '',
        search_id = '',
        search_title = '',
        search_content = '',
        search_global = '', 
        order_column = 'created_at',
        order_direction = 'desc'
    ) => {
        axios.get('/api/posts?page=' + page +
            '&search_category=' + search_category +
            '&search_id=' + search_id +
            '&search_title=' + search_title +
            '&search_content=' + search_content +
            '&search_global=' + search_global + 
            '&order_column=' + order_column +
            '&order_direction=' + order_direction)
            .then(response => {
                posts.value = response.data;
            })
    }
    // ...
}

And all that's left is to add another when in the PostController. We can use the whereAny to query all the fields.

use Illuminate\Database\Eloquent\Builder;
 
class PostController extends Controller
{
    public function index()
    {
        $orderColumn = request('order_column', 'created_at');
        if (! in_array($orderColumn, ['id', 'title', 'created_at'])) {
            $orderColumn = 'created_at';
        }
        $orderDirection = request('order_direction', 'desc');
        if (! in_array($orderDirection, ['asc', 'desc'])) {
            $orderDirection = 'desc';
        }
 
        $posts = Post::with('category')
            ->when(request('search_category'), function (Builder $query) {
                $query->where('category_id', request('search_category'));
            })
            ->when(request('search_id'), function (Builder $query) {
                $query->where('id', request('search_id'));
            })
            ->when(request('search_title'), function (Builder $query) {
                $query->where('title', 'like', '%' . request('search_title') . '%');
            })
            ->when(request('search_content'), function (Builder $query) {
                $query->where('content', 'like', '%' . request('search_content') . '%');
            })
            ->when(request('search_global'), function (Builder $query) { 
                $query->whereAny([
                        'id',
                        'title',
                        'content',
                    ], 'LIKE', '%' . request('search_global') . '%');
            }) 
            ->orderBy($orderColumn, $orderDirection)
            ->paginate(10);
 
        return PostResource::collection($posts);
    }
    // ...
}

Now the global search should also be working!