I am coded into a corner and need help

So you just need to know how to filter the query?

Sorry, not sure what your question is?

The other things that occurs are that you could

a) just use count on the actual tab rather than creating separate properties

b) use groupBy on the status field so organise your data into sets

c) if the search applies to all tabs, then it should be presented outside of the tabbed area

d) use query builder rather than raw so that you can easily add additional where constraints

OK @snapey I am making some progress and thank you for your time!

https://share.getcloudapp.com/JrubryKz

The question is definitely (at least now) how to filter ...

Unfortunately this has become one of those god object things that grew and morphed into a beast. Every customer has to complete this every year and as different admins have assumed control, additional requirements have just stacked on.

a) Done c) Done

d) I originally had a query builder then moved to an API Resource and finally settled on Raw ... The dataset size using the 210+ datapoints and the potential size of the overall number of records was causing the page load to either approach 4 seconds OR in some case, time-out. -- The API RESOURCE doesn't seem to work well with LARAVEL-LIVEWIRE as I was just alternating between has to be an array and public error and you can't use a class as an array.

So as you can see from the screen shot, now I get all of the records but that is just it, I get all of the records regardless if I have any value in $this->search OR have a tab selected. The question now is how to filter this.

public function render()
    {
        $agreements = DB::select( DB::raw("SELECT caf.id, cmp.company_code, cmp.legal_name, cmp.city, cmp.state, cmp.country, cmp.denied_party_checked, cmp.denied_party_cleared, cmp.last_denied_party_check, cmp.phone, caf.status_id, caf.reporter, caf.reporter_email, con.accounting_name, con.accounting_email FROM company_customer_agreements caf LEFT JOIN companies cmp ON caf.company_id = cmp.id LEFT JOIN company_contacts con ON cmp.id = con.company_id WHERE cmp.company_code IS NOT NULL ORDER BY cmp.legal_name"));

        $this->invited = collect($agreements)->filter(function($i) {
            return $i->status_id === 1;
        })->all();
        $this->initiated = collect($agreements)->filter(function($i) {
            return $i->status_id === 2;
        })->all();
        $this->submitted = collect($agreements)->filter(function($i) {
            return $i->status_id === 3;
        })->all();
        $this->verified = collect($agreements)->filter(function($i) {
            return $i->status_id === 4;
        })->all();
        $this->approved = collect($agreements)->filter(function($i) {
            return $i->status_id === 5;
        })->all();
        $this->processed = collect($agreements)->filter(function($i) {
            return $i->status_id === 6;
        })->all();
        $this->completed = collect($agreements)->filter(function($i) {
            return $i->status_id === 7;
        })->all();

        $this->agreements = $agreements;

        return view('livewire.customers.customer-portal');
    }

You could create a livewire component for each tab, but you still need to know how many items in the other tabs in order to display the totals, so this is probably not applicable.

You raw query looks like a simple join for which eloquent would be fine?

Not sure why you would consider an API resource as you are not using an API - think of it like just rendering a regular view. You don't need to do anything special with the data or even cast it to Livewire public properties. Just get the data in the render() method and pass to the layout?

The only 'live' element would be the search box where, as you type, you would re-render, adding where statement to the query using the livewire search property.

So, scrap this line;

        $this->agreements = $agreements;

and pass the agreements to the view as you would a regular blade view.

LOL, I have never used Eloquent for a join before so I think I need to explore that. I agree that once this is an eloquent query then filtering it would be easier.

Thanks again for your insight.

Don't use a join...

$agreements = Agreements::with('companies.contacts')->get()

or whatever your relationships are called

but then if I use regular relationships I am back to dealing with the 210+ datapoints AND I still have no way to "find" a record by legal_name which is in the companies table and not in the company_customer_agreements table.

Isn't that backtracking?

If you want to find an agreement that has a company with a specific legal name, use the whereHaseloquent method?

What do you mean by 210+ datapoints?

The companies table has 92 columns, the company_customer_agreements table has 72 columns, the company_contacts table has 47 columns.

With over 1100 companies in the database, that dataset takes several seconds to load but I only use 15 datapoints.

Further research got me this far using Tinkerwell.

$agreements = DB::select( DB::raw("SELECT caf.id, cmp.company_code, cmp.legal_name, cmp.city, cmp.state, cmp.country, cmp.denied_party_checked, cmp.denied_party_cleared, cmp.last_denied_party_check, cmp.phone, caf.status_id, caf.reporter, caf.reporter_email, con.accounting_name, con.accounting_email FROM company_customer_agreements caf LEFT JOIN companies cmp ON caf.company_id = cmp.id LEFT JOIN company_contacts con ON cmp.id = con.company_id WHERE cmp.company_code IS NOT NULL ORDER BY cmp.legal_name"));

$cafs = CompanyCustomerAgreement::select('id', 'status_id', 'reporter', 'reporter_email')->get();

however if I try to add the company relationship

$cafs = CompanyCustomerAgreement::with('company')->select('id', 'status_id', 'reporter', 'reporter_email')->get();

I get

Illuminate\Database\Eloquent\Collection {#1696
     all: [
       App\Models\CompanyCustomerAgreement {#1705
         id: 1,
         status_id: 3,
         reporter: "Jon Gravois",
         reporter_email: "[email protected]",
         company: App\Models\Company {#1708},
       },

and with

$cafs = CompanyCustomerAgreement::with('company:id, legal_name')->select('id', 'status_id', 'reporter', 'reporter_email')->get();

I get

Aliasing 'CompanyCustomerAgreement' to 'App\Models\CompanyCustomerAgreement' for this Tinker session.
Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column ' legal_name' in 'field list' (SQL: select `id`, ` legal_name` from `companies` where 0 = 1)'

you will see a space before legal name

SQL: select `id`, ` legal_name` 

remove spaces from

with('company:id, legal_name')

Also, you know you can specify table and column names in get()?

working on it

@snapey I think I solved it by flip-flopping it

$cmps = Company::has('caf')
  ->with( 'caf:id,company_id,status_id,reporter,reporter_email', 'contacts:id,company_id,accounting_name,accounting_email' )
  ->select('id', 'company_code', 'legal_name', 'city', 'state', 'country', 'denied_party_checked', 'denied_party_cleared', 'last_denied_party_check')->get();

now I should be able to search for LIKE %legal_name% OR LIKE%company_code%

thanks again for your help and patience ... I have always just reverted to RAW SQL whenever the Eloquent got complicated but this has been a learning experience.

great

So, something like

$query = Company::has('caf')
  ->with( 'caf:id,company_id,status_id,reporter,reporter_email', 'contacts:id,company_id,accounting_name,accounting_email' )
  ->select('id', 'company_code', 'legal_name', 'city', 'state', 'country', 'denied_party_checked', 'denied_party_cleared', 'last_denied_party_check');

if($this->search) {
    $term = "%{$this->search}%";

    $query->where('legal_name','like',$term)
        ->orwhere('company_code','like',$term)
}

$companies = $query->get();

assuming $this->search is a public property on the livewire class

@snapey THANKS AGAIN!!!

This sucker loads like lightning!

@snapey even though I thought I had the winner, 2 hours later I am still fighting this and hope you would help me win this battle.

The filter bar works great with your suggested reply but the tabs are not used.

According to my research since the tabs actually filter the results by the relationship, I need to be put something like this

$authors = Author::with(['books' => function($query) {
  $query->whereYear('created_at', date('Y'));
}])->get();

into our

$query = Company::has('caf')
            ->with( 'caf:id,company_id,status_id,reporter,reporter_email', 'contacts:id,company_id,accounting_name,accounting_email' )
            ->select('id', 'company_code', 'legal_name', 'city', 'state', 'phone', 'country', 'denied_party_checked', 'denied_party_cleared', 'last_denied_party_check');

        if($this->search) {
            $term = "%{$this->search}%";

            $query->where('legal_name','like', $term)
                ->orwhere('company_code','like', $term);
        } else {
            //TODO DO THE TABS HERE
        } // end if

        $this->agreements = $query->get();

I have tried many permutations but they haven't been pretty

Think of it like this. Your tabs are all just sections down a long page. You pass to that page (view) the results of your search.

Now, in each section, you only want the records that have the correct status

In each section you have a @foreach loop to iterate over the records. All you need to do is filter the collection by the records that match the status which you can do with a collection with a simple where condition on the agreement.

eg

@foreach($companies->where('company.caf.status',1)->get() as $invitee)

and then loop over the invitees. Note that you are doing this with the collection, not a separate database query.

do the same for each section and each status. Same for the counts

 {{ $companies->where('company.caf.status',1)->count() }}

If holding all the records for each tab in memory at once is too much, then you would have to break it down with a livewire component on each tab, each doing its own query when the user switches to that tab, but you will need to do extra work to have a count on each tab.