One-to-many setup for sever-side SearchPanes?

dgruska

Hi,
I am trying to get a distinct list of the options in the lookup table in a SearchPane, and a comma-separated list in the DataTable. I have a Node backend with the following:

let editor = new Editor(db, 'a', 'a.id') .fields([ new Field('a.STOCKSTATUS').searchPaneOptions(new SearchPaneOptions().where(function () { this.whereNotNull('a.STOCKSTATUS') })), new Field('a.ACTIVE'), new Field('b.VACUUMRANGE').options( new Options().table('b').value('ID').label('VACUUMRANGE') ), ]) .leftJoin('b', 'b.PARTNO', '=', 'a.PARTNO') .write(false);

I am getting the following error:
field.dbField is not a function

Am I just doing something wrong, or are joins not supported in server-side SearchPanes?

Thanks,
Dave

allan

or are joins not supported in server-side SearchPanes?

This one. The Mjoin is not possible to filter with server-side processing at this time. This is for both the general filter and also SearchPanes / SearchBuilder.

The problem is that Editor uses two queries - the first for the main table, which can be filtered, and then the second to get the Mjoin'ed information. By that time it is too late to filter.

The only way to resolve this is to combine them into a single query, and I have found a good cross-database way of doing that yet I'm afraid.

Allan

dgruska

Thanks for the quick response.

How about this? In the lookup table, at most we have two options per part. We could pull these back into the main table as two fields. If we did this, would that help in any way?

Dave

allan

Left join'd fields will work with SearchPanes and server-side processing, since they are part of the main query. So, if you have two additional columns in the main table that point to the second table, you could use two left joins to get them and make it work that way.

Its a little bit of a database restructure though.

Allan

dgruska

I think I understand what you're saying, but am a little confused about how to implement it. I got the table joining on itself two times:

.leftJoin('b', 'a.PARTNO', '=', 'b.PARTNO')
.leftJoin('c', 'a.PARTNO', '=', 'c.PARTNO')

If this is correct, what do I do to put them together in the select statement and/or SearchPanes code, so that there's one distinct list in the SearchPane?

allan

You need to do an alias so you can refer to the same table twice, but with different names:

.leftJoin('secondTable aliasA', 'aliasA.id', '=', 'hostTable.partA')
.leftJoin('secondTable aliasB', 'aliasB.id', '=', 'hostTable.partB')

And then refer to the link table fields to get the information you need from the joined table.

This example doesn't exactly show that, but it is close in that it uses an alias to refer back to the same table.

Allan