Left Join only TOP 1

seotech

Hello,

I need a little unusual Join for my tables.
I want to join only the first matched row from a left join.
In MS SQL this is a functioning syntax: SELECT ..... FROM xContent OUTER APPLY (SELECT TOP 1 * FROM BELEG WHERE BELEG._CONTENTID = xContent.ContentID) BELEG

Is it possible to implement this with the editor ? Maybe with extra code for the join class?

allan

I'm afraid it isn't possible using the Editor PHP classes to perform this kind of join at the moment. They are designed to cover the 90% of use cases - for the other 10% (such as this!) you would need to query the database directly and dump the data back to DataTables in a JSON format.

Sorry I don't have a better answer!

Allan

seotech

Hello allan,

is there maybe a possibility to write a custom function like:
->customjoin('OUTER APPLY (SELECT TOP 1 * FROM BELEG WHERE BELEG._CONTENTID = xContent.ContentID) BELEG').
Then this string must only append to the query like the normal join.
For me, it is very difficult to understand the full source code structure of the Editor Extension to extend my custom query.
Do you or anyone have the appropriate places in the source code to extend this.

I would like to continue to use the editor classes . The whole functionality with validation I would not like to miss or write all by myself. Therefore, I bought a license ;)

allan

Hi,

So the problem with having something like ->customJoin() is that Editor needs to be able to understand it in order to be able to perform the update actions correctly. That's why there is only a leftJoin() method and it requires the table, two fields and combination operation to be specified, rather than being written as SQL (an SQL parser would be nice, but I don't really want to contemplate how much work it would be!). The Editor classes are basically a representation of the database schema that we can work with.

Having said that, if the information coming from your join is being used for read only information (i.e. it is displayed in the table, but on insert, edit and delete no action is required against the joined information - only the main table) then you can query the database directly and dump the data back as I suggested.

For example you could do something like:

if ( ! isset( $_POST['action'] ) ) {
  // Read data
  $data = $db->sql( 'SELECT ... complex join ... etc' )->fetchAll();
  echo json_encode( $data );
}
else {
  Editor::inst( ... )
    ->fields( ... )
    ...
}

So on read you get the data and send it back, but on create, edit or delete the Editor class does its thing.

You will likely need to add either as statements to the select or loop over the array of results and format it into the JSON parameters that the client-side expects (i.e. matching what Editor::inst() would give) - which is not shown above.

Allan