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

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

dgruskadgruska Posts: 27Questions: 8Answers: 0

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

Answers

  • allanallan Posts: 61,584Questions: 1Answers: 10,084 Site admin

    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

  • dgruskadgruska Posts: 27Questions: 8Answers: 0

    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

  • allanallan Posts: 61,584Questions: 1Answers: 10,084 Site admin

    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

  • dgruskadgruska Posts: 27Questions: 8Answers: 0

    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?

  • allanallan Posts: 61,584Questions: 1Answers: 10,084 Site admin

    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

Sign In or Register to comment.