Basic Search fails MJOIN alias

Erik Skov

MJOIN with alias because of table reuse

I have this almost working. Except, when you type into the search box, the repsonse comes back, "No matching records found".

Response:
{"fieldErrors":[],"error":"Unknown field: office2 (index 6)","data":[],"ipOpts":[],"cancelled":[]}

Console:
DataTables warning: table id=adjusters - Unknown field: office2 (index 6)

in my .js file, I am sure the issue is this line:
{ "data": "office2", render: "[, ].legacy_code" },

The issue is I need to reference the table rr_office twice. The whole project was working fine. Each adjuster is assigned to a single office (rr_adjuster.office_id). However, they can also access multiple offices (rr_adjuster_offices.office_id). I was recently asked to include in the DTE display an additional column containing all of the legacy_codes for each office the adjuster has access to.

Abbreviated PHP code
```
Editor::inst($db, 'rr_adjusters', 'id')
->fields(

            Field::inst('rr_adjusters.office_id')
            ->options(Options::inst()
                    ->table('rr_office, rr_company')
                    ->value('rr_office.id')
                   ->label( array ('rr_company.company_name', 'rr_office.office', 'rr_office.legacy_code'))
                    ->render(function ( $row ) {
                        return  $row['rr_company.company_name'].' - '.$row['rr_office.office'].' - '.$row['rr_office.legacy_code'];
                    })
                    ->where( function($q) {
                        $q ->where( function($r) {
                            // this is the actual inner join of the tables.
                            // You need the "false" in order to avoid "table.id" being escaped as a string.
                            $r ->where('rr_company.id', 'rr_office.parent_company', '=', false);
                            $r ->where('rr_office.active', null);
                        });
                    } )
            )
            ->validator('Validate::dbValues'),


    ->leftJoin('rr_office', 'rr_office.id', '=', 'rr_adjusters.office_id')
    ->leftJoin('rr_company', 'rr_company.id', '=', 'rr_office.parent_company')

```
My new code: 1st time looking at Mjoin

        ->join(
            Mjoin::inst( 'rr_office' )
                ->name( 'office2' )
                ->link( 'rr_adjusters.id', 'rr_adjuster_offices.adjuster_id' )
                ->link( 'rr_office.id', 'rr_adjuster_offices.office_id' )
                ->order( 'legacy_code asc' )
                ->fields(
                    Field::inst( 'id' )
                        ->set(false)
                        ->options( Options::inst()
                            ->table( 'rr_office' )
                            ->value( 'id' )
                            ->label( 'legacy_code' )
                        ),
                    Field::inst( 'legacy_code' )
                )
        )

This plus the added line to the table in the js file
{ "data": "office2", render: "[, ].legacy_code" },
does produce the desire list of legacy codes. But when I enter a name in the search results it gives the "no results" comment.

How do I keep my new column AND make the search work again?
We are currently working with version 1.74

I have been here:
https://editor.datatables.net/examples/advanced/joinArray.html
https://datatables.net/forums/discussion/52039/how-combine-3-tables#latest
https://editor.datatables.net/manual/php/mjoin
https://datatables.net/forums/discussion/comment/85878/#Comment_85878
https://datatables.net/forums/discussion/comment/182479/#Comment_182479

allan

Hi,

It isn't so much of a problem with the alias, but rather a limitation with server-side processing and search. You don't explicitly say, but I assume you are using server-side processing, based on the error message?

Even without the alias, you'll get an error when attempting to search on a Mjoin column with server-side processing.

There isn't a solution other than to disable search on that column I'm sorry to say (or you could make the table client-side processing perhaps).

The issue is that the Mjoin is a second query, so the main table has already run its select. The Mjoin will then run, but it is too late for filtering on that to have any effect. In CloudTables I've addressed that by using CTEs in Postgres and a known structure, but with Editor, and its support for many different databases, it is a lot harder. It isn't currently on the roadmap I'm afraid.

Allan

Erik Skov

Thank you so much for the explanation. I have been able to implement something close to that. php

        ->join(
            Mjoin::inst( 'rr_office' )
                ->name( 'office2' )
                ->link( 'rr_adjusters.id', 'rr_adjuster_offices.adjuster_id' )
                ->link( 'rr_office.id', 'rr_adjuster_offices.office_id' )
                ->order( 'legacy_code asc' )
                ->fields(
                    Field::inst( 'id' )
                        ->set(false)
                        ->options( Options::inst()
                            ->table( 'rr_office' )
                            ->value( 'id' )
                            ->label( array ('legacy_code', 'office'))
                        ->render(function ( $row ) {
                            return  $row['legacy_code'].' - '.$row['office'];
                        })
                    ),

                    Field::inst( 'legacy_code' ),
                    Field::inst( 'office' ) // name
                )
        )

js

                { "data": "office2"
                    , "render": "[<hr/>].legacy_code"
                    , "searchable": false   // MJOIN created columns are NOT searchable.
                    , "sortable": false },  // sorting makes no sense
                { "data": "office2"
                    , "render": "[<hr/>].office"
                    , "searchable": false   // MJOIN created columns are NOT searchable.
                    , "sortable": false },  // sorting makes no sense

My internal customer asked for both of these values to be in one column, I could not find a way to concatenate each entry in the array. I proposed this 2 column solution and am waiting for feedback. Is there a way to combine the 2 arrays so I could present the two values in the same column? In either the php or the js?

allan

If you want to combine them together you'd need to use your own function for columns.render - something like:

{
  data: 'office2',
  render: (d) => d.map((i) => `${i.office} (${i.legacy_code})`).join('<hr>'),
};

or longer hand:

{
  data: 'office2',
  render: function (d) {
    return d
      .map( function (i) {
        return `${i.office} (${i.legacy_code})`;
      })
      .join('<hr/>');
  }
}

Allan

Erik Skov

That worked wonderfully! Thank you again.