I’m facing a problem with search filtering, Project is with Laravel, Inertia js, Vue js
I want to show all invoices with Recipient Information on it which has a foreign key on it to make this possible, also I have relationships on my model Data on Front-end end are showing successfully but when I try to filter it show’s me an error Details:…
there are two tables:
Recipients:
id (PRIMARY KEY) Auto Increment, foreign key
name
customer_number
…/(not necessary)
Invoices:
invoice_nr (PRIMARY KEY) Auto Increment
recipient_id -> foreign key {with id of recipients}
i also have relationships on my models :
Invoices.php Model
//relationship public function Recipients() { return $this->belongsTo(Recipients::class,'recipient_id','id'); }
Recipients.php Model
//relationship public function invoices() { return $this->hasMany(Invoices::class); }
data came on front-end it’s all good, but when I try to search filtering, on that time its shows me this error:
here is my Invoices Controller code function show_Invoices
use AppModelsInvoices; use AppModelsInvoicesDetails; use IlluminateSupportFacadesAuth; use IlluminateSupportFacadesRedirect; use IlluminateSupportFacadesRequest; use IlluminateSupportCarbon; use InertiaInertia; use AppModelsRecipients; use IlluminateSupportStr; use DB; public function show_invoices(){ $q = Invoices::with('recipients')->get()->all(); if(request('search')){ $search_keyword = "%".request('search')."%"; $q ->where(function ($query) use ($search_keyword){ $query->where('invoice_nr', 'LIKE', $search_keyword) ->orwhere('recipients.name', 'LIKE', $search_keyword) ->orwhere('recipients.customer_number', 'LIKE', $search_keyword) ->orwhere('recipients.created_at', 'LIKE', $search_keyword); }); }; request()->validate([ 'direction' =>['in:asc,desc'], 'field' =>['in:invoices.invoice_nr,recipients.name,recipients.customer_number,recipients.created_at'] ]); if(request()->has(['field','direction'])){ $test->orderBy(request('field'), request('direction')); } return Inertia::render('Show_Invoices', [ 'filters' =>request()->all(['search','field','direction']), 'invoices' => $q ]); }
data from backend came on invoices object successfully, but when I try to search it doesn’t work
in Frontend .. .vue script here it is
<script> import BreezeAuthenticatedLayout from '@/Layouts/Authenticated' import { pickBy, throttle } from 'lodash'; import Pagination from '@/Components/Pagination' export default { metaInfo: { title: 'Invoices' }, components: { BreezeAuthenticatedLayout, Pagination, }, props: { invoices: Object, filters: Object, }, data() { return { params:{ search:this.filters.search, field:this.filters.field, direction:this.filters.direction, } } }, methods:{ sort(field){ this.params.field = field; this.params.direction = this.params.direction === 'asc' ? 'desc' : 'asc'; } }, watch:{ params:{ handler:throttle(function(){ let params = pickBy(this.params); this.$inertia.get(this.route("invoices.show"), params , {replace: true, preserveState: true}); },150), deep:true, } } }
Advertisement
Answer
with a bit of help from John Lobo, and by myself I found solutions for my problem.
Tip: This is a Solution if you want to filter multiple columns of relationship and parent table also, in laravel, it’s a bit nested but it makes a solution if you had the same problem with me.
$q = Invoices::with('recipients'); if(request('search')){ $search_keyword = request('search'); $q->where(function ($q) use ($search_keyword) { $q->where('invoice_nr', 'like', '%'.$search_keyword.'%') ->orwhereHas('recipients', function ($subq) use ($search_keyword) { $subq->where(function ($subq2) use ($search_keyword) { $subq2->orWhere('name', 'like', '%'.$search_keyword.'%'); $subq2->orWhere('customer_number', 'like', '%'.$search_keyword.'%'); //you can access many columns as you want }); }); }); }