Skip to content
Advertisement

Laravel orWherehas filtering relationship with multiple columns

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: enter image description here

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
                            });
                        });
                });
        }
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement