Search by filter error

[dimoss]

Hi

I have a dbjoin element which joins to the joomla users table. I use username both as value and label. This is ok as the username is unique. I have also used this element as search dropdown filter.
Using the latest github, when I perform a search using this filter I get error 500
"Fabrik has generated an incorrect query"
Unknown column 'AFG' in 'where clause'

WHERE ( fab_plist.tournament IN (SELECT tournament_code FROM fab_organizers WHERE user_id = 33 AND code = YEAR(CURDATE())) OR fab_plist.tournament IN (SELECT id FROM fab_tournaments WHERE activation = 'Yes') ) AND ( `fab_plist`.`assoc` IN (AFG) )

In this example AFG is the username.

On the contrary when I perform a search using another dbjoin element where value and label is different then there is no problem.

In the previous github ver. (Nov. 20th) there is no problem at all.

Thanks!

 

[dimoss]

Further searching on this matter pointed out the problem of the pre-filter side because when I deleted a specific pre-filter it worked ok.
However it's not logic bec. the same pre-filter worked out of box with the previous github ver and it has nothing to do with the selected list filter.
Any help from the mods is appreciated.
Thanks!

 

[troester]

( `fab_plist`.`assoc` IN (AFG) )

I would expect "...IN ('AFG')...", it's obviously a string.
What is your prefilter code?

 

[dimoss]

Hi Troester

The prefilter code is:

Tournament(raw) IN SELECT tournament_code FROM fab_organizers WHERE user_id = {$my->id} AND code = YEAR(CURDATE())

In the above the Tournament(raw) is the field
IN is the Condition and the rest is the Value of course with Ouery selection in Type

 

[troester]

So the assoc part is not coming from your prefilter but from the dbjoin filter.

I have a dbjoin element which joins to the joomla users table. I use username both as value and label. This is ok as the username is unique.

I assume this is the reason, as the recommended value of the dbjoin is id and in case of an id ... IN (1)... would be correct without quotes.
So it looks as if the element type is not tested in case of a non-id value and so the quotes are missing.

 

[dimoss]

Nope. The recommended value of the dbjoin is the username (from the users table) which is varchar.
It has no relation as the prefilter looks in another table different than the users core Joomla one searching to test the user_id which is actually the id in the core user Joomla table.
As i wrote before this happens in the latest Github.
It worked all the time till now. Last time checked ok with the Github from 20/11/2013.

 

[cheesegrits]

Looking at this now.

-- hugh

 

[cheesegrits]

Can you post a screenshot of your filter setup for that element. The code to generate that query varies greatly depending on those settings.

-- hugh

 

[dimoss]

Hi Hugh

Below you can see the settings of the prefilter and the dbjoin element.
The problem is on the 1st prefilter.
Another thing I noticed also is that if I change the condition from 'IN' to 'EQUAL' works ok but it doesn't solve my problem as there are cases where the query returns more that 1 result. I just mentined this if it helps.





Thanks!

 

[troester]

Can you additionally post the filter settings of the dbjoin element (List view settings/Filters)?

 

[dimoss]

Hi Troester





Thanks!

 

[cheesegrits]

That's why I said "post a screen shot of the FILTER SETUP FOR THAT ELEMENT".

Anyway, will take a look, see if I can replicate this problem. But not right now, as it's 8pm on a Sunday night, which means I have to get ready for my regular Sunday night gig.

-- hugh

 

[dimoss]

Thanks Hugh!
I will wait for your comments.

 

[cheesegrits]

No progress yet.

-- hugh

 

[dimoss]

Friendly bump

 

[cheesegrits]

I'll try and find time this weekend to work on it. Corner case filter issues like are not easy to track fix.

-- hugh

 

[dimoss]

Friendly bump

 

[dimoss]

Friendly bump

 

[dimoss]

Any chance to check this out?
Friendly bump
Happy New Year!

 

[cheesegrits]

I tried replicating it again yesterday after you Skyped me, and I still can't replicate it.

What confuses is me is why it's doing an IN() rather than an =, as you are using a dropdown for your element filter. Which can only generate one value, so we should be using a simple =, which is what it does if I set up an identical join and put a dropdown filter on it. Also, we correctly quote it on my test:

WHERE ( `fab_join_test`.`user_join` = 'cheesegrits' )

I'm going to have to get on your site to get any further with this one. Catch me on Skype.

I've also tried looking through the commit list prior to the Nov 20th, but I don't see anything, even going back 3 weeks before that, which should have affected this. I'm sure there is something, but I can't find it.

Rest assured I'm not ignoring this, I've spent quite a few hours trying to work out what's going on, I just haven't yet managed to get my head round it.

-- hugh