Prefilter "NOT IN" Condition broken

[rackem]

I have a prefilter on a list that uses a "NOT IN" condition. When I try to go to my list I get a 500 error that contains "Subquery returns more than 1 row SQL="

After troubleshooting in phpMyAdmin, I found the error is in the WHERE clause.

WHERE ( `mps_votes`.`player` = (SELECT `id` FROM `mps_players` WHERE `active` = 0) )

If I replace "=" with "NOT IN", the query works. So it seems that the NOT IN condition in my prefilter is being wrongly inserted as "=".

This is for F! 3.1.

 

[rackem]

*friendly bump*

The issue is present in the front end but not the back end. I also tried the "IN" condition, that is broken too.

As a workaround, I deleted the pre-filter. Not pleasant but reverting back to a prior state is really an option as I just migrated to J3/F3.1.

 

[rob]

I tested both IN and NOT IN in 3.0 and 3.1 and they both seem to work fine. Did you have the prefilter type set to 'query' ?

 

[rackem]

Did you have the prefilter type set to 'query' ?

Yes.

I tried re-creating the pre-filter as well as trying different ACLs. This is a GitHub joomla3 from last night. No luck - same result as before. This was working in F3 prior to my migration to F3.1.

Here is a screenshot of my prefilter settings and the error. I tried other conditions as well but the error message always shows the resulting query to have an "=". Not sure what else to try or other info I could provide?

 

[troester]

Can you set any other condition? or are all reverted to "="? (fabrikdebug)
Is this with all elements (element types)?

IN/NOT IN is working on my 3.1 site (GitHub just now, element=dbjoin)
But if I provoke a "more than 1 row" error (by setting conditon ="EQUALS") I don't see back-ticks around the table.element name
WHERE ( test3.dbjoint3 = (select id from test2) )

so not sure if there's a difference in element types or versions.

 

[rackem]

troester, thank you very much for the helpful advice. In times like these where it appears the problem is unique to a site, it is very encouraging to have thoughtful assistance.

I turned on fabrikdebug but for some reason its sections would not open up to reveal the details when the list displayed the error. Perhaps the error occurs before the debug info is populated? Anyways, according to the 500 error, all prefilter conditions revert to "=" in the query, regardless of my selection.

Then I tried changing the field option of my prefilter to a different element in my list - so instead of my dbjoin I chose id. Of course that doesn't provide data with any meaning but it is a valid query. The query performed without error. Fabrikdebug did have data then, but honestly I wasn't able to find the details of the query within it. I also had another dbjoin element in my list to choose from - that worked as well. Then I tried changing the query condition - the condition I selected was reflected on the site.

Interestingly enough, I have another list with pretty much the same setup. It is functioning properly. The only real difference is that the list with the problem was created at an earlier point in time.

I opened and re-saved that element but it had no effect. As I said before, I have re-created the prefilter several times. I tried looking at the #_fabrik_lists and #_fabrik_elements tables but nothing jumped out as odd for the pertinent entries.

It seems that the problem is my specific dbjoin element interacting with the prefilter to prevent the selected condition from being put into the query.

 

[rackem]

OK, I got it working by deleting and re-creating the element!