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

Pre-filter on dbjoin not working?

rackem

Well-Known Member
I am having a problem getting a pre-filter to work on a dbjoin element. This is a local setup so I can't show you but here is my setup.

I have two lists - Players and Votes. My Votes list is linked to the player list using a dbjoin element. Users can vote on players. However some players are inactive so I want to be able to hide the votes for inactive players.

So my pre-filter is set to the following:

WHERE
Field: Player(raw)
Condition: IN
Value: SELECT `id` FROM `mps_wi_ranking` WHERE `active` = 1
Type: Query

All the values are shown in my list. I have verified the query works in phpMyAdmin. I have tried "Player" and "Player(raw"). I have even tried a simple EQUALS pre-filter set to a single value but that didn't work either.
 
Have you loaded the page with fabrik debug (add &fabrikdebug=1 to the url), and looked at the main getData query, see if we're adding that to the WHERE clause?

Are you sure the access level you have set on the pre-filter is correct, for your logged in user?

-- hugh
 
(remembering that in 3.x, prefilters apply only to that J! 2.5 access level, not "at or beneath group membership level" as in f2 / J! 1.5)
 
Are you sure the access level you have set on the pre-filter is correct, for your logged in user?

Not sure how I did not add that to the post. :( Yes, I have tried several ACL including Public without success.

Have you loaded the page with fabrik debug (add &fabrikdebug=1 to the url), and looked at the main getData query, see if we're adding that to the WHERE clause?

I did try this and I don't remember seeing anything. However, I was not really sure where to look to be honest. I cannot access my local setup at the moment so I will have to get back to you on this.
 
My dbjoin was using a Concat for the label. Out of curiosity, I removed it and now I receive the following white screen error.

getData:Subquery returns more than 1 row SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `mps_test`.`id` AS `mps_test___id`, `mps_test`.`id` AS `mps_test___id_raw`, `mps_test`.`date_time` AS `mps_test___date_time`, `mps_test`.`date_time` AS `mps_test___date_time_raw`, `mps_test`.`player` AS `mps_test___player_raw`, `mps_wi_ranking`.`first_name` AS `mps_test___player`, `mps_test`.`user` AS `mps_test___user_raw`, `btxz_users`.`username` AS `mps_test___user`, `mps_test`.`Vote` AS `mps_test___Vote`, `mps_test`.`Vote` AS `mps_test___Vote_raw`, `mps_test`.`last_vote_date` AS `mps_test___last_vote_date`, `mps_test`.`last_vote_date` AS `mps_test___last_vote_date_raw`, `mps_test`.`id` AS slug , `mps_test`.`id` AS `__pk_val` FROM `mps_test` LEFT JOIN `mps_wi_ranking` AS `mps_wi_ranking` ON `mps_wi_ranking`.`id` = `mps_test`.`player` LEFT JOIN `btxz_users` AS `btxz_users` ON `btxz_users`.`id` = `mps_test`.`user` WHERE ( mps_test.player = (SELECT `id` FROM `mps_wi_ranking` WHERE `active` = 1) ) ORDER BY `mps_test`.`last_vote_date` DESC LIMIT 0, 10

So, my first question is whether what I am trying to do is even possible. My query will return a set of values that I want to display. I thought, perhaps incorrectly?, that the IN condition would tell the pre-filter to only display those records that matched one of the values of the query.

My next question is why adding a Concat label would prevent the above error from being displayed.
 
Without seeing what the query was with the concat, I can't answer the 'next question'.

The problem with the above query is you are using an = condition on a subquery that returns more than one result. I'm assuming that's coming from a pre-filter?

That's where the IN condition is needed, which tests against multiple values. Either as literals, like "WHERE foo IN (1,2,3)", or against the result of a query which returns a single selected field, but potentially multiple rows, "WHERE foo IN (SELECT id FROM bar)"

-- hugh
 
Without seeing what the query was with the concat, I can't answer the 'next question'.

The problem with the above query is you are using an = condition on a subquery that returns more than one result. I'm assuming that's coming from a pre-filter?

That's where the IN condition is needed, which tests against multiple values. Either as literals, like "WHERE foo IN (1,2,3)", or against the result of a query which returns a single selected field, but potentially multiple rows, "WHERE foo IN (SELECT id FROM bar)"

-- hugh
 
Hmm. Hugh, it really sounds like it is set up properly but just not working.:(

I will try to explain my setup again. Some of my list names might have been throwing you off. In general terms, I am trying to have a setup where my users can "vote" on "players". I use an "active" yesno element to pre-filter my "player" list to only show those players that are active. I also need to pre-filter my "votes" list so that any votes on in-active players are not shown.

So, I have two lists - Players and Votes. My Votes list is linked to the player list using a dbjoin element.

Player list: id, date_time, active, first_name, last_name
Votes list: id, date_time, player (dbjoin element linked to player___id), user_id, vote

The dbjoin element in my Votes list has a concat label set to the following: {thistable}.first_name, " ", {thistable}.last_name

The pre-filter in my Vote list is set to the following:

WHERE
Field: Player(raw)
Condition: IN
Value: SELECT `id` FROM `players` WHERE `active` = 1
Type: Query
Apply to: Guest

Here are the problems I am reporting.

1. Using the setup above as a Guest user I see all the records in my Votes list (pre-filter not working)
2. If I remove the concat label setup from my dbjoin element, then I receive the following error.

getData:Subquery returns more than 1 row SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `mps_test`.`id` AS `mps_test___id`, `mps_test`.`id` AS `mps_test___id_raw`, `mps_test`.`date_time` AS `mps_test___date_time`, `mps_test`.`date_time` AS `mps_test___date_time_raw`, `mps_test`.`player` AS `mps_test___player_raw`, `mps_wi_ranking`.`first_name` AS `mps_test___player`, `mps_test`.`user` AS `mps_test___user_raw`, `btxz_users`.`username` AS `mps_test___user`, `mps_test`.`Vote` AS `mps_test___Vote`, `mps_test`.`Vote` AS `mps_test___Vote_raw`, `mps_test`.`last_vote_date` AS `mps_test___last_vote_date`, `mps_test`.`last_vote_date` AS `mps_test___last_vote_date_raw`, `mps_test`.`id` AS slug , `mps_test`.`id` AS `__pk_val` FROM `mps_test` LEFT JOIN `mps_wi_ranking` AS `mps_wi_ranking` ON `mps_wi_ranking`.`id` = `mps_test`.`player` LEFT JOIN `btxz_users` AS `btxz_users` ON `btxz_users`.`id` = `mps_test`.`user` WHERE ( mps_test.player = (SELECT `id` FROM `mps_wi_ranking` WHERE `active` = 1) ) ORDER BY `mps_test`.`last_vote_date` DESC LIMIT 0, 10

Note that
"mps_wi_ranking" = my Player list
"mps_test" = my Votes list
 
I'm going to have to see the page. Obviously that = doesn't look right, if your pre-filter uses IN. But I'll need to see the fabrikdebug output on your page, to work out where that is happening. Also to see what the queries look like when you have a concat.

-- hugh
 
Sorry about the delay. :( I was going to make up a simple example on my live site for debugging but got caught up in other things. Still swamped so I will post again when I have it ready.
 
Well, not sure what the difference was but this setup is working on my live site. So I will close this thread...
 
OK, re-opening this thread as I found the difference! Now I get the same behavior on my live site as I saw on my local test setup. The "trigger" so to say, is that a filter must be set on the element (my dbjoin).

Here is the behavior with each of the filter settings. "OK" means the pre-filter is applied properly.

With concat set
None: OK
Field: pre-filter not applied to list
Dropdown: OK
Autocomplete: pre-filter not applied to list

With no concat
None: OK
Field: pre-filter not applied to list
Dropdown: OK
Autocomplete: get the error noted previously

Hope this helps. Let me know if you need access.
 
OK, that helps. It's Sunday night and I have a gig in 20 mins, so it'll be tomorrow (at least) before I can get to it. Might be Tue, as I think we have to put out a 3.0.6.2 tomorrow, to fix a validation issue we only noticed just after we put up 3.0.6.1, ::sigh::.

If I don't respond by Tue, please bump.

-- hugh
 
I'm going to have to raise this as github issue for Rob to look at if I don't get time. I'm in last 24 hour panic mode before leaving for Europe, won't have much time for work till I get settled where I'm going on Sunday.

-- hugh
 
Sorry Im joining this one late - rackem could you PM me

* an ftp account
* an admin user login
* the name / id of the list with the issue

thx
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top