1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

[SOLVED] Placeholder in data - where for database join element is not working

Discussion in 'Community' started by SoilentRed, Apr 21, 2021.

  1. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    I have a calc element that generates an or clause in my form. It runs the calc on the page load.
    Code (Text):

    // Get a list of the member's jomsocial group ids
    $db = JFactory:: getDbo();
    $db->setQuery('SELECT groupid FROM #__community_groups_members WHERE memberid = {$my->id}');
    $rows = $db->loadObjectList();
    // Get a list of group leader ids
    foreach ($rows as $row) {
        $db->setQuery('SELECT ownerid FROM #__community_groups WHERE id = '.$row->groupid);
        $leaders = $db->loadObjectList();
        foreach ($leaders as $leader) {
            $groupleaders[] = $leader->ownerid;
        }
    }
    // remove duplicate group leader ids
    $scrubLeaders = array_unique($groupleaders);
    // Get list of groups belonging to the member's group leader
    foreach ($scrubLeaders as $gl) {
        $db->setQuery('SELECT id FROM #__community_groups WHERE ownerid = '.$gl);
        $accessableGroups = $db->loadObjectList();
        // load accessible groups into array
        foreach ($accessableGroups as $ag) {
            $groups[] = (int)$ag->id;
        }
    }
    foreach ($groups as $group) {
        $orClause[] = ' OR cm1.groupid = '. $group;
    }
    $final = implode('', $orClause);
    return $final;
     
    I see it returning the result successfully on the form. Yay!
    Code (Text):
    OR cm1.groupid = 35 OR cm1.groupid = 36 OR cm1.groupid = 38
    I'm trying to put that result in the data - where statement for a dropdown element. Here is the Joins where and/or order by statement (SQL)
    Code (Text):

    WHERE {thistable}.id IN
    (
    SELECT cm1.memberid FROM jomla_community_groups_members cm1 WHERE cm1.groupid = 99999999999 {lead_swap___groups_or_clause_raw}
    )
    AND {thistable}.id <>{$my->id}
    AND {thistable}.block = '0'
     
    But here is what I'm getting with the fabrik debugger
    Code (Text):

    SELECT DISTINCT(`jomla_users`.`email`) AS value, `name` AS text
    FROM `jomla_users` AS `jomla_users`
    WHERE  `jomla_users`.id IN
    (
    SELECT cm1.memberid FROM jomla_community_groups_members cm1 WHERE cm1.groupid = 99999999999
    )
    AND `jomla_users`.id <>2643
    AND `jomla_users`.block = '0' AND 7 = -7
    ORDER BY text ASC
     
    It's not taking the placeholder and the dropdown isn't loading anything.

    I've played a little bit with the ajax option, and tried some of the tips you mentioned on the wiki, but I'm not sure if I am understanding or doing it correctly.

    Any thoughts?
     
  2. startpoint

    startpoint Active Member

    Level: Community
    Try with:
    PHP:
    '{lead_swap___groups_or_clause_raw}'
     
  3. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    Thanks for the response. That returns an error

    Code (Text):

    joins
    Array
    (
       [0] => stdClass Object
           (
               [id] => 37
               [list_id] => 0
               [element_id] => 140
               [join_from_table] => lead_swap
               [table_join] => jomla_users
               [table_key] => recipient
               [table_join_key] => email
               [join_type] => left
               [group_id] => 12
               [params] => {"join-label":"name","type":"element","pk":"`jomla_users`.`id`"}
               [canUse] => 1
               [table_join_alias] => jomla_users
               [keytable] => lead_swap
           )

    )

    form:data
    Array
    (
       [fabrikdebug] => 1
       [Itemid] => 230
       [option] => com_fabrik
       [view] => form
       [formid] => 12
       [ark_inine_enabled] => 1
       [arkoption] => com_fabrik
    )

    1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' ) AND `#__users`.id <>2643 AND `#__users`.block = '0' AND 7 = -7 ORD' at line 5
     
     
  4. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    Update! Still not working

    Per the documentation, I've added this to the djoin elements javescript:
    Code (Text):

    loadDBList(this);
    function loadDBList(el) {
        var usedID = jQuery('#lead_swap___groups_or_clause' );
        usedID[0].fireEvent('change');
    }
     
    It looks like it's doing what it's supposed to as I see a brief loading animation on the database join element, but it still doesn't load what's in the placeholder.
     
  5. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    UPDATE! Progress.

    It seems to be working when rendered as a dropdown, radio list, checkbox, or multiselect dropdown. But I need it to work with Autocomplete and it isn't.

    Something I feel is worth noting is that even though it is returning results, the or statements are still not shown in the fabrik debugger. Presumably, because it loads first?
     
  6. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    No more progress. Are placeholders incompatible with autocomplete? What's the way around this?
     
  7. SoilentRed

    SoilentRed Caaan do!

    Level: Community
  8. SoilentRed

    SoilentRed Caaan do!

    Level: Community
  9. SoilentRed

    SoilentRed Caaan do!

    Level: Community
  10. troester

    troester Well-Known Member Staff Member

    Level: Community
    Did you try to use a field instead of a calc and set the field via a php form plugin "onLoad"?
     
  11. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    I've now tried both calc and field and can report that using either method doesn't work with Autocomplete. It only returns options when rendered as a dropdown, radio list, checkbox, or multiselect dropdown.

    See attached. Note that for the databasejoin element, in the javascript onload, I've tried pointing to both the calc and the field, same goes for the data-where section of the element. The attached screencaps only show one so as to not be redundant.
     

    Attached Files:

  12. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    For comparison on the front end, here it is return results with the dropdown, but not for autocomplete
     

    Attached Files:

  13. troester

    troester Well-Known Member Staff Member

    Level: Community
    SoilentRed likes this.
  14. juuser

    juuser Well-Known Member

    Level: Community
    This needs a lot of time to get into, but just a thought, why aren't you using an advanced (chosen) dropdown? It has basically the same behavior (imho much better) and I personally haven't faced a lot of cases where autocomplete would be preferred over advanced dropdown.
     
    SoilentRed and troester like this.
  15. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    Forgive my ignorance here. Okay, I'm using the FORM PHP plugin for onLoad.
    Here's my stupid guess at what I think it wants:
    Code (Text):

    $formModel->data['lead_swap___newor']='{lead_swap___newor_raw}';
    $formModel->data['lead_swap___newor_raw']='{lead_swap___newor_raw}';
     
    But all this is doing is putting
    Code (Text):
    {lead_swap___newor_raw}
    In the field placeholder. See attached.
    We actually have a very strong use case for autocomplete in this instance. The people using the forms are members of networking groups. They pass business referrals to one another. Sometimes they need to pass a referral, but they may not remember the name or the profession of the person they want to refer. Autocomplete allows them to fill out partial data and see what comes up.
     

    Attached Files:

  16. troester

    troester Well-Known Member Staff Member

    Level: Community
    Advanced dropdown does the same (and even quicker).
    No, do your calc code here:
    $db ...
    $formModel->data['lead_swap___newor'] = $final;
    $formModel->data['lead_swap___newor_raw'] = $final;

    It's worth a try if you don't want to use the advanced dropdown.
     
  17. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    It still doesn't present the options with autocomplete, but it DOES now show the or clause in the fabric debugger.
    The advanced dropdown... I'm assuming you're referring to the advanced tab on the database join element? I'm gonna play with this.
     
  18. juuser

    juuser Well-Known Member

    Level: Community
    It's Layout -> Enhanced dropdown.

    And you can add {"search_contains":true} into Fabrik's global options -> Forms -> Enhanced Dropdowns options" field to search the options also from the middle of the string.

    Try it and I'm quite sure you'll prefer this to autocomplete.
     
  19. troester

    troester Well-Known Member Staff Member

    Level: Community
    ok, it's "Enhanced Dropdowns"
    upload_2021-4-30_20-47-59.png
     
  20. troester

    troester Well-Known Member Staff Member

    Level: Community
    :D
     
    juuser likes this.

Share This Page