Mjoin search and order

nicontrols

Is it possible to search and order by a one-to-many join? I have the data displaying but get the "Unknown field" error when trying to search.

I'm confused because this example seems to work?
https://editor.datatables.net/examples/advanced/joinArray.html

        $editor->fields(
            ....
        )
        ->join(
            Mjoin::inst( 'salstkmaster' )
                ->link( 'enquiry.enquirynr', 'enqitem.enqnr' )
                ->link( 'salstkmaster.id', 'enqitem.itemmasid' )
                ->order( 'partnum asc' )
                ->fields(
                    Field::inst( 'id'),
                    Field::inst( 'partnum' )
                )
        )

JS:

        columns: [     
            ....
            { title: "Parts", fieldType: "text", data: "salstkmaster", render: "[, ].partnum"}
        ],

Error:

Unknown field: salstkmaster (index 8)

allan

Is it possible to search and order by a one-to-many join?

Not when using server-side processing. It will work automatically if you are using client-side processing, but Mjoin with server-side processing makes use of two SQL queries and then joins the data in PHP (for portability between databases).

How many rows are in your table?

Allan

nicontrols

Thanks Allan

There are 150,000+ rows in the table.

stevevance

This is something I'd like to use, too.
Users have "wish lists" and with the way things currently are they can search only the "wish list" title, and not the titles of the items within each "wish list". Screenshot shows those items; I'd like for people to be able to search this table for "BRT" and the list with that item would show.

allan

The only way to do this with server-side processing would be to construct an SQL statement that will get all of the data in a single query. A Common Table Expression would I believe be able to do this, but not with the Editor classes since they don't build CTEs.

Allan