Is it possible to access data from two tables in a single editor table?

danvanwijk

Link to test case: https://joomrp.urtestsite.com/portal/inventory/parts_by_site.html
Debugger code (debug.datatables.net):
Editor::inst( $db, 'parts_by_site' )
->fields(
Field::inst( 'ipn' )->options( Options::inst()
->table( 'parts' )
->value( 'id' )
->label( 'ipn' )
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'IPN is required' )
) ),
Field::inst( 'site' )->options( Options::inst()
->table( 'sites' )
->value( 'id' )
->label( 'name' )
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Site is required' )
) )->validator( Validate::numeric() )
->setFormatter( Format::ifEmpty(null) ),
Field::inst( 'quantity' )->validator( 'Validate::numeric' )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Quantity is required' )
) ),
)
->debug(true)
->process( $_POST )
->json();
Error messages shown: No errors
Description of problem: I need to access data from two or more table without using the joins because there is no column to match the Id or something. I was going to use UNION but I found this link https://datatables.net/forums/discussion/28945/display-datatable-with-union-select-data-source-but-editor-with-one-table which states that UNION is not possible yet. In above code you can see that I was able to access the data from second table and I have displayed it in the table select box. Which is fine. But now when the value is selected from the dropdown I want to display the name of the selected option instead of its value.
Do you think it will be possible? If so can you please share an example for this?
Thanks!

allan

At the moment, no, what you are looking for is not possible with the Editor PHP libraries I'm afraid. The editing aspect just wouldn't work with two tables since it is ground up assumed that there will be a single table to edit from the base and each row in it can be uniquely identified.

If you just want to display the data from the UNION, then that's no issue, just query the DB directly (i.e. without using the Editor server-side libraries) and return the JSON data. But editing - that will be a different story.

Allan

danvanwijk

I don't want to edit the other table as you can see here https://joomrp.urtestsite.com/portal/inventory/parts_by_site.html there are ids displaying for IPN and Site. But if you edit you can see there names. I want to display those names instead of the Ids in table. Do you think it can be possible?

allan

That's just a left join unless I'm missing something? There isn't need for a UNION there?

For example consider this table, the "Location" column shows the name, but when editing you are using the value that references the row in the second table.

More information on left joins in Editor available here.

Allan