• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

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

SoilentRed

Caaan do!
I have a calc element that generates an or clause in my form. It runs the calc on the page load.
Code:
// 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:
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:
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:
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?
 
Thanks for the response. That returns an error

Code:
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
 
Update! Still not working

Per the documentation, I've added this to the djoin elements javescript:
Code:
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.
 
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?
 
giphy.gif
 
Did you try to use a field instead of a calc and set the field via a php form plugin "onLoad"?
 
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.
 

Attachments

  • calc-method.jpg
    calc-method.jpg
    181.3 KB · Views: 83
  • data-where.jpg
    data-where.jpg
    169.9 KB · Views: 89
  • field-method.jpg
    field-method.jpg
    171.4 KB · Views: 89
  • front-end.jpg
    front-end.jpg
    231.5 KB · Views: 94
  • on-load.jpg
    on-load.jpg
    149.2 KB · Views: 89
For comparison on the front end, here it is return results with the dropdown, but not for autocomplete
 

Attachments

  • Autocomplete.jpg
    Autocomplete.jpg
    195.4 KB · Views: 103
  • dropdown.jpg
    dropdown.jpg
    271.9 KB · Views: 90
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.
 
I was referring to a form php plugin, running "onLoad" (not any JS)
https://fabrikar.com/forums/index.p...dating-a-form-s-value-before-showing-the-form
JS doing calc and doing autocomplete may have issues and/or running not in the order you are expecting.
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:
$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:
{lead_swap___newor_raw}
In the field placeholder. See attached.
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.
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.
 

Attachments

  • probably-wrong.jpg
    probably-wrong.jpg
    127.8 KB · Views: 64
  • string-loaded.jpg
    string-loaded.jpg
    184.6 KB · Views: 81
Autocomplete allows them to fill out partial data and see what comes up
Advanced dropdown does the same (and even quicker).
$formModel->data['lead_swap___newor']='{lead_swap___newor_raw}';
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.
 
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.
It still doesn't present the options with autocomplete, but it DOES now show the or clause in the fabric debugger.
Advanced dropdown does the same (and even quicker).
The advanced dropdown... I'm assuming you're referring to the advanced tab on the database join element? I'm gonna play with this.
 
The advanced dropdown... I'm assuming you're referring to the advanced tab on the database join element? I'm gonna play with this.
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.
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top