Pass parameter into options query

rf1234

I have dynamic field options and the options depend on a value of the "outer query": Each category has a different value range and hence the options are different for each table row. This would require to pass a value of a different field into the options query. I have not seen a way how to do this. I tried with a session variable but it didn't work: It only held the last category id read from the database.

For every data table row the value of "ctr_category_id" needs to be passed into the options query of "ctr_has_ctr_category.value_range_id". How do I get this into the where clause of the options instance?

Editor::inst( $db, 'ctr_has_ctr_category' ) 
->field(
    Field::inst( 'ctr_category.id AS ctr_category_id' )->set( false )
        ->getFormatter( function ( $val, $data, $opts ) {
            $_SESSION['ctr_category_id'] = $val;
            return $val;                      
        } ),
    Field::inst( 'ctr_has_ctr_category.category_value_range_id' )->set(Field::SET_EDIT)
        ->options( Options::inst()
            ->table('category_value_range')
            ->value('id')
            ->label( 'value' )
            ->order( 'value asc' )
//                    where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                $q ->where( function($r) {
                    $categoryId = 0;
                    if ( isset($_SESSION['ctr_category_id'] ) ) {
                        $categoryId = $_SESSION['ctr_category_id'];
                    }
                    $r ->where('category_value_range.ctr_category_id', $categoryId );
                });
            } )
        ),

rf1234

Nobody? Ok, I found a work around in the mean time at least for the "edit only" options scenario.
1. I use a getFormatter to read the options (my own SQL)
2. On "initEdit" I fill the options individually for the respective record.

This works great - but only for an "edit only" scenario. Doesn't work for "create". For "edit only" it is even a lot simpler than the options instance I think. But unfortunately it doesn't work for create! When I edit a row I can replace the options with the ones read from the server with the getFormatter. But if I then want to create a new record the field options are still overwritten with the "edit" opitons and I can't retrieve the original "create" options from the options instance on "initCreate" - simply because they are not passed into that event handler!

@allan , @colin
Do you have any solution for this? I have also found out that options are global in Editor. You are not supposed to have record specific options. I think this is wrong. You should be able to choose between global options (for new records) and indiviudal options for edited records.

server side:

Field::inst( 'ctr_category.id AS categoryOptions' )->set( false ) //rendered like in options instance
    ->getFormatter( function ( $val, $data, $opts ) {
        return getCategoryOptions($val);          
    } ),
Field::inst( 'ctr_has_ctr_category.category_value_range_id' )->set(Field::SET_EDIT),


function getCategoryOptions($categoryId) {
    global $dbh;
    
    $dbh->query('SELECT DISTINCT value AS label, id AS value 
                   FROM category_value_range 
                  WHERE ctr_category_id = :categoryId 
               ORDER BY 1 ASC');
    $dbh->bind(':categoryId', $categoryId); 
    
    return $dbh->resultsetAssoc();     
}

client side "initEdit"

editor
    .on('initEdit', function ( e, node, data, items, type ) {
        this.field('ctr_has_ctr_category.category_value_range_id').update(data.categoryOptions);
    })

colin

Hi @rf1234 ,

It was a public holiday here in the UK yesterday, we're catching up now with the forum and support. I've passed this one onto Allan to take a look at.

Cheers,

Colin

rf1234

thanks colin, forgot that you have your May holiday on the first Monday of May instead of May 1st. Sorry!

allan

To check my understanding of:

For every data table row the value of "ctr_category_id" needs to be passed into the options query of "ctr_has_ctr_category.value_range_id". How do I get this into the where clause of the options instance?

So basically each row in the table can have different options?

The way to do that is to use initEdit to get the list of options from the server when you trigger editing on a new row. The Options class will not help with that I'm afraid.

Allan

rf1234

Right, that's what I thought, too.

Only one question: If I use the options instance in parallel, how can I access it? I would like to access it on "initCreate" and replace the (potentially modified field options) by the options from the Editor options instance but they don't get passed into the event! Is there any way to access them otherwise?

rf1234

@allan I would like to ask for a new feature in Editor:

Please pass the options object into the initEdit and initCreate events.

That will make it much easier to use individual options per record avoiding additional ajax calls (details see above).

allan

If I use the options instance in parallel, how can I access it?

You can't. Its currently not designed for this use case I'm afraid. It is something I've thought about (talking about new features), but I'm not certain how clean an implementation I can make it.

At the moment you need to make an Ajax call to get the options (if they change per row). See this blog post for how that might be done (slightly different setup, but still applies).

Please pass the options object into the initEdit and initCreate events.

The issue with this is that it is effectively an SQL query per row. If you have 10'000 rows in the table that 10'001 queries just to display the table. Doing it on-demand is much more efficient.

Allan

rf1234

That's not what I mean:
I mean "Please pass the global options object into the initEdit and initCreate events".

You don't need to change your approach of having just one options object which is not per record but global.

See what I am doing now:

on initEdit: I replace the field options (that are global by nature) with individual options per record that I read from the server in a "pseudo"-field. Which means: simple and no extra ajax call to the server! (see my code above). But this approach does not work for new records because the row doesn't exist yet.

But after editing the options are replaced = overwritten!! Which means I have a problem if I want to create a new record and the global options read from the server are already overwritten! I need to be able to rewrite the global options from the server (that are only valid for newly created records in my case) to the respective field.

I can't do this if the object isn't passed into initCreate.

rf1234

an alternative solution could be to add a new method:
field().restore()
which restores the original options to a field after an update with field().update().
If that was possible there'd be no need to access the options object.

allan

I'm with you now - thanks. You can use ajax.json() to get the data that was loaded by the DataTable - so table.ajax.json().options should give you the loaded options.

Allan

rf1234

thanks for taking a look!
will give it a try tomorrow; right now I am doing ajax.reload to deal with the issue which isn't very efficient.

rf1234

@allan
Started implementing this morning - and found out I had the solution already ... and had forgotten about it ... Too bad and sorry for bothering you with this.

This works too even without ajax.json().

reportTable
    .on('xhr', function( e, settings, json, xhr ) {
        //we need to get the editor drop down options with the selectable filter ids
        if ( json != null ) {
            //json.options["report.report_type_id"] is an array of objects like this:
            //"label" is the label of the filtr plus the rendered field type
            //"value" is the filter's id
//                serverReportTypeOptions = 
//                [                    
//                    {label: "00: Darlehensübersicht", value: "50"},
//                    {label: "01: Übersicht Einlagen und Ausleihungen", value: "51"},
//                    {label: "02: Übersicht Derivate", value: "52"},
//                ];
            if ( typeof json.options !== 'undefined' ) {
                serverReportTypeOptions = json.options["report.report_type_id"];
            }
        }
    })       

Thanks again for your support!

rf1234

Everything works fine now, thanks again!

BUT: Now I found out that the field().update() method does not work with field type "selectize". There is probably a bug in the plugin somewhere around here:

_fieldTypes.selectize = {
    _addOptions: function ( conf, options ) {
        var selectize = conf._selectize;

        selectize.clearOptions();
        selectize.addOption( options );
        selectize.refreshOptions(false);
    },

The options are properly replaced with field().update() but the value of the field is lost: When you select a record and open Editor in edit-mode there is no selection as if the record was just being created.

All of this works with field type "select" but I cannot use "select" here for a number of reasons. Can you help with this please.

colin

Hi @rf1234 ,

Yep, I'm seeing the behaviour you described - see here. I've raised it internally (DD-900 for my reference) and we'll report back here when there's an update.

Cheers,

Colin