Autocomplete field with join

Autocomplete field with join

carrarachristophecarrarachristophe Posts: 120Questions: 27Answers: 2

Hello,
I am aiming to replace Select and Select2 fields by the autocomplete field.
I amended the field type in the Javascript side from:

        fields: [
            {
                "label": "Type d'article:",
                "name": "fac_articles_natures.articletype",
                type: 'select',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            }
        ]

To:

        fields: [
            {
                label: 'Type d\'article:',
                name: 'fac_articles_natures.articletype',
                type: 'autocomplete'
            }
        ]

it works in the sense that:
- the "labels" are listed in the fields
- when editing, the displayed "label" is correct
- when editing, I can pick another "label" and when saving, the correct "value" is stored in the database

BUT: when editing, when I click on the "label" I want, the "label" is automatically replaced by the "value" which is different due to the use of a leftjoin

    ->fields(
        Field::inst( 'fac_articles_natures.articletype' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' )))
            ->options( Options::inst()
                ->table( 'fac_articles_types' )
                ->value( 'articletype_id' )
                ->label( 'articletype' ) ),
        Field::inst( 'fac_articles_types.articletype' )
    )
    ->leftJoin( 'fac_articles_types', 'fac_articles_types.articletype_id', '=', 'fac_articles_natures.articletype' )

Does anyone know how I can trick the code to avoid that?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,831Questions: 1Answers: 10,518 Site admin
    Answer ✓

    The value of the input field in autocomplete, which is why you are seeing what you are.

    What you describe is the use case for tags, where you can have a label indicating a value - example here. So it isn't quite the same as Select2's UI in this regard, but it is close.

    Allan

  • carrarachristophecarrarachristophe Posts: 120Questions: 27Answers: 2

    Thanks Allan,
    It was indeed perfectly stated in the example
    QUOTE
    If you wish to limit the end user to specific values, consider using an input type such as tags , select or datatable, which are more suited for joined data (i.e. where the value and label are not the same).
    UNQUOTE
    It is just that in terms of behaviour, I find the autocomplete field more user friendly than the tag for my need.

  • rf1234rf1234 Posts: 3,028Questions: 88Answers: 422
    edited January 19

    It is just that in terms of behaviour, I find the autocomplete field more user friendly than the tag for my need.

    Not sure whether you ever heard of "typeahead"
    https://twitter.github.io/typeahead.js/

    I am using that and it is a lot like "autocomplete". I get the options from the server.
    You can configure after how many letters it starts searching. In this case it is three.

    Looks like this:

    Here is some code:

    Editor client side

    }, {
        label: lang === 'de' ? 'Vertragspartner:' : 'Partner:',
        name:  "ctr.ctr_partner",
        className: "also-tc-only",
        attr: {
            class: 'typeAheadPartner',
            autocomplete: 'off',
            placeholder: lang === 'de' ? 'Vertragspartner eingeben und ggfs. einen Vorschlag auswählen' 
                                       : 'Enter partner name and possibly select a suggestion'
        }
    },  {
    .... 
    editor 
        .on('opened', function(e, type, action) {
            $('.typeAheadPartner').typeahead( {
                source: function (query, process) {
                    $.ajax({
                        type: 'POST',
                        url: 'actions.php?action=typeAheadPartner',
                        //only one parameter to post is allowed by typeahead!!
                        data: { query: $('.typeAheadPartner').val() },
                        dataType: 'JSON',             
                        success:    function(data) {                      
                                    process(data);
                        }
                    });
                },
                items: 10,     //you would only retrieve up to 10 items
                minLength: 3   //you would only start querying after typing at least 3 letters
            });
    

    server code to retrieve the options "typeAheadPartner":

    $partnerLike = "%" . $p['queryString'] . "%";  
    
    $dbh->query('SELECT DISTINCT c.ctr_partner 
                   FROM ctr_has_ctr_installation a 
             INNER JOIN ctr_installation b  ON a.ctr_installation_id = b.id 
             INNER JOIN ctr c               ON a.ctr_id = c.id
                  WHERE a.ctr_id IN 
                        ( SELECT DISTINCT a.ctr_id
                            FROM ctr_has_ctr_govdept a
                      INNER JOIN ctr_govdept b                   ON a.ctr_govdept_id = b.id    
                      INNER JOIN ctr_govdept_has_user_complete c ON b.id = c.ctr_govdept_id 
                      INNER JOIN user_has_selected_ctr_govdept d ON b.id = d.ctr_govdept_id    
                            WHERE c.user_id = :userId 
                              AND d.user_id = :userId 
                           ) 
                    AND c.ctr_partner LIKE :partnerLike 
                  LIMIT 10');
    $dbh->bind(':userId', $_SESSION['id']);
    $dbh->bind(':partnerLike', $partnerLike);
    return array_column($dbh->resultsetAssoc(), 'ctr_partner');
    

    I am saving the options as values in the database. If you don't want to do that: No problem. You can use a setFormatter in the server side Editor to adjust the values saved.

    Summary of my requirement:
    - I don't want to save selectable options separately; this is only to simplify data entry
    - The field values entered so far ARE the options to choose from
    - Additional values / options can be entered on the fly

  • carrarachristophecarrarachristophe Posts: 120Questions: 27Answers: 2

    Thanks rf1234.
    I will have a look.

  • allanallan Posts: 63,831Questions: 1Answers: 10,518 Site admin

    I wonder if I should introduce a new field type, whereby it has a searchbox that you type into, and it completes a tag. Basically the same as tags, but with the searchbox at the top level, rather than needing to click the button to add a new option. Would that would for you?

    Allan

  • carrarachristophecarrarachristophe Posts: 120Questions: 27Answers: 2

    I personnaly like the tag field when several items can be picked.
    But have a preference for a Auto-complete-like field when a single item can/must be selected. That's a pity it cannot handle join

  • allanallan Posts: 63,831Questions: 1Answers: 10,518 Site admin

    Fairly sure I can do a hybrid. Tags inside an input element, which is more or less what Select2 does. Possibly an option for the tags field to let you decide if you want an input element, or the add button.

    Allan

Sign In or Register to comment.