• 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.

Database Join in repeating group - not showing data

chris.paschen

Chris Paschen
[This is a bit of a follow-up from http://fabrikar.com/forums/index.ph...-where-not-working-on-repeated-groups.46363/]

After updating to the latest version per the above change, the database join element is not showing the data within the repeating group element. However, the data IS being stored in the table properly.

If the 'Show please select' is set to YES the element just shows "Please select" upon viewing the form. If that is NO then the element is blank.

(NOTE: This is also on the same form as the other repeating groups I've reported on the Kindred project)
 
Which join on which group? You have about three pages of join elements.

Remember that I deal with dozens of different sites concurrently, and it's difficult to remember specifics, so say "it's the same as ..." doesn't help me much. In each thread, give me the exact details I need.

-- hugh
 
Sorry, I thought that with professional support it included mind reading :) )

In the "Books - Book Team List" group it is happening on the "service_provider_id" element (ked_books_books_74_repeat___service_provider_id)
 
Lol. I do my best. :)

I'll take a look this evening, I'm out of the house today.

Sent from my HTC6545LVW using Tapatalk
 
I suspect the problem is the WHERE clause for building the servie_provider_id dropdown. It generates this:

Code:
SELECT DISTINCT(`ked_comprofiler`.`id`) AS value, `cb_sp_business_name` AS text FROM `ked_comprofiler` AS `ked_comprofiler` WHERE cb_sp_category LIKE "Other,Writing coach,Other,,,,,Copy editor" OR cb_sp_category LIKE "|%" OR cb_sp_category LIKE "%|Other,Writing coach,Other,,,,,Copy editor|%" OR cb_sp_category LIKE "%|Other,Writing coach,Other,,,,,Copy editor" OR cb_sp_category = "Other,Writing coach,Other,,,,,Copy editor" ORDER BY text ASC

... which I think means that the placeholder is including all the repeats.

I'll need to look at that code locally.

-- hugh
 
Yup. Looks like all those LIKE clauses are pulling in ALL of the 'cb_sp_category' entries from each item in the repeat group, instead if the immediate instance of the repeat group.
Is there any way to 'force' that to only pull in only the specific instance? (similar to {thistable} syntax)? [without the need to adjust code]
 
Nope, not at the moment. That's why I need to look at the code. In quite a few places (like calc elements) we already deal with that, so that a placeholder referencing an element in the same repeat group as itself will always have just the value of "this repeat". But it's not am entirely trivial thing to do.

I'll look at applying the same code to the WHERE clause handling in joins.

-- hugh
 
The display isn't returning the blank results, but something is still not working with this. However, I'll do some more testing so that I can tell you exactly what it is (and I'm also going to do a bit more testing of the WHERE clause in the query to make sure it isn't related to that on my end).
I'll post back as soon as I have some more specifics of the issue.
 
Initial testing is pointing to a logic problem in our WHERE clause (some values are not showing or acting 'weird'). This likely due to the fact that we are querying a CB field. I am close to a solution and will post it here (just in case anyone else wants to query against cb multi-select fields).
 
Yup, I remember looking at that query when you first built it and thinking it may not work as expected.

-- hugh
 
OK the problem was both an issue in the syntax of our WHERE clause, trying to parse out the CB data (now fixed), AND there appears to still be a problem in the element.

Here's what I've been able to learn by doing some additional, more detailed, testing:

* The filtering of data in the 2nd database lookup (GROUP: Books - Book Team List; ELEMENT: ked_books_books_74_repeat___service_provider_id) seems to work fine via ajax/js (i.e. when you select an option in the 'parent' field (ked_books_books_74_repeat___role_id) this element (the child element) is properly filtered based on that selection.
* The data IS properly stored to the database (repeat table) upon clicking Save & Close
* When the page is re-displayed (either after re-opening the main record, or just by clicking the 'apply' button) the 'child' element (ked_books_books_74_repeat___service_provider_id) has reverted back to "Please select" and no option appears to have been selected (even though it IS presently stored in the table) [appears that the element doesn't get the initial value on load from the table]
* After reloading the form (and the 'child' elements have gotten reset to 'Please select') and you press Save & Close (or apply) all those items are then saved to the database as 'NULL' values

So it looks like we have one more 'issue' with this element to making it actually work as designed.

NOTE: When I went back to fix the WHERE clause I noticed that the actually 'WHERE' statement wasn't there. Not sure if that is something that I did or if for some reason it isn't supposed to be there. I've added it back in to the where clause box as it was before.

SO close!!! (almost there!) :)
 
Are you positive you did a complete github update?

If you append &fabrikdebug=1 to that page, so Fabrik shows it's queries, you can see that the service_provider_id query ("service_provider_iddatabasejoin element: get options query") is only being run once

Code:
SELECT DISTINCT(`ked_comprofiler`.`id`) AS value, `cb_sp_business_name` AS text FROM `ked_comprofiler` AS `ked_comprofiler` WHERE (FIND_IN_SET( 'Writing coach', REPLACE( `ked_comprofiler`.`cb_sp_category`, '|*|', ',' ) ) > 0) AND `ked_comprofiler`.id <> 784 AND `ked_comprofiler`.cb_sp_listing_approved = 1 ORDER BY text ASC

... for the first repeat. Which is one of the things I fixed in this commit:

https://github.com/Fabrik/fabrik/commit/4fa01fae9c8d8bf492a870fae607e7360f06f6a6

... at line 584, where I add the $repeatCounter to the $opts array, which is then used in the buildQuery() ...

https://github.com/Fabrik/fabrik/bl...ik_element/databasejoin/databasejoin.php#L837

... at line 837, to build the cache key. Prior to this change, we would never re-run a query for a repeated join, so instances of the repeat would use the same cached results. But obviously if you are using an element value from that repeat instance in your WHERE, it needs to re-run it.

So I'm puzzled as to why it's only running once.

If you can confirm that your file has all those changes, as an experiment, try commenting out line 843:

https://github.com/Fabrik/fabrik/bl...ik_element/databasejoin/databasejoin.php#L843
...

Code:
            //return $this->sql[$sig];

... and see if that makes the query run as many times as you have repeats.

-- hugh
 
One other minor issue that I noticed.

If you have saved the form and you had a value in the 'parent' element (ked_books_books_74_repeat___role_id) then you click the drop-down on the 'child' element (ked_books_books_74_repeat___service_provider_id), that child element is NOT filtered any longer (based on the stored value of the 'parent' element) which is the 'usual' funtionality of elements like this.

I'm guessing that this ties in to the same issue noted above - that the 'child' element doesn't seem to be displaying the data stored (or responding to the filtering WHERE clause) based on saved data - just ajax data.

Let me know if you need me to test anything further.
 
Let me try the full update one more time (in case there was a 'glitch' in the upload somehow). I'll report back after I upload and test again.
 
I tried again the full update and confirmed that the changed lines are present. But still not working.

I did fix one issue, the WHERE clause was trying to match against the label not the value so I added '_raw' and it appears to be at least using the value. The new WHERE clause is:

WHERE (FIND_IN_SET( '{ked_books_books_74_repeat___role_id_raw}', REPLACE( {thistable}.`cb_sp_category`, '|*|', ',' ) ) > 0)
AND {thistable}.id <> 784
AND {thistable}.cb_sp_listing_approved = 1​

I did a test without the first clause of the WHERE (just the <>784 and _approved = 1 lines and it worked fine (minus the obvious properly filtering of the join). So that would seem to mean that what you indicated is still the problem - it isn't firing each time.

I tested by commenting out that one line at 843 and still it does the same thing (and only appears to run the query one time).

So at least it looks like the problem is repeatable.

If you have any other things for me to try please let me know.
 
Any ideas on this yet?
We're needing a solution to getting the database join to fully work in a repeating group (with a WHERE clause), and it's still not working.
 
Not much point bumping posts within a 24 hours window. If you need faster than 24 hour responses, or just faster than subscription support can provide on an issue in general, it'll need to be done as hourly billed - I get an Aeeba archive of your site, install it here, and step through the code to see what's going on. That's a minimum $100 charge for the additional time.

Can you edit ./plugins/fabrik_element/databasejoin/databasejoin.php and put some debug code in ...

Around line 841, in buildQuery() where we check to see if the sig is cached ...

Code:
        if (isset($this->sql[$sig]))
        {
FabrikHelperHTML::debug($sig, 'fabrikdatabasejoin buildQuery got sig: ');
            return $this->sql[$sig];
        }

... and around line 584 in _getOptionVals(), where we check to see if the actual SQL returned by buildQuery() is cached:

Code:
        if (isset($this->optionVals[$sqlKey]))
        {
FabrikHelperHTML::debug($sqlKey, 'fabrikdatabasejoin _getOptionVals got sql: ');
            return $this->optionVals[$sqlKey];
        }

Let me know when that's in there.

-- hugh
 
Sorry, client getting 'anxious' (because it's been almost a week since we started this and still not resolved).

Those changes are in, and the debug is now showing those results (looks like it's just always using '1' as the key).
 
Hmmm, hard to make sense of it without the name, can you add that ...

$this->getElement()->name

Code:
        if (isset($this->sql[$sig]))
        {
FabrikHelperHTML::debug($sig, $this->getElement()->name . ' fabrikdatabasejoin buildQuery got sig: ');
            return $this->sql[$sig];
        }

Code:
        if (isset($this->optionVals[$sqlKey]))
        {
FabrikHelperHTML::debug($sqlKey, $this->getElement()->name . ' fabrikdatabasejoin _getOptionVals got sql: ');
            return $this->optionVals[$sqlKey];
        }

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top