DBJoin dropdown element with where - set as dropdown list filter

[joomlamate]

I have a dropdown dbjoin element with a WHERE clause.
I am trying to create a dropdown filter for the list, but the filter will generate a wrong sql statement in the list.

Like:

SELECT DISTINCT(`webxcon_cstypes`.`software_type`) AS `text`, `webxcon_casinos`.`type` AS `value` FROM `webxcon_casinos` LEFT JOIN `webxcon_cstypes` AS `webxcon_cstypes` ON `webxcon_cstypes`.`id` = `webxcon_casinos`.`type` LEFT JOIN `#__content` AS `#__content` ON `#__content`.`id` = `webxcon_casinos`.`review_article` WHERE `webxcon_casinos`.`type` IN ('2','3','1') `webxcon_cstypes`.id > 0 AND `webxcon_cstypes`.published = 1 LIMIT 0, 100

WHERE `webxcon_casinos`.`type` IN ('2','3','1') `webxcon_cstypes`.id

Isn't possible to achieve this, is it a bug, or it is a matter of something I am doing wrong?

 

[cheesegrits]

What is the exact text of your WHERE clause?

-- hugh

 

[joomlamate]

Simple column name and value

Initially I had :
published=1

Later I made it:
{thistable}.published = 1

At the moment, it is:

{thistable}.id > 0 AND {thistable}.published = 1.

Whatever it is, if I want to make dropdown filter for this field, the list will break. If you notice the above sql error, it's creating wrong syntax, by adding the WHERE clause of the dbjoin element after the :
WHERE `webxcon_casinos`.`type` IN ('2','3','1')
without connecting it with an AND.

It;s actually missing the AND

 

[cheesegrits]

Yup, I can obviously see the malformed query, I just needed to check the exact text of you WHERE clause so I can replicate it.

Looking at it now.

-- hugh

 

[cheesegrits]

Should be fixed as of this commit ...

https://github.com/Fabrik/fabrik/commit/adf64ed9d13c77dfc28b0cacf5ae2f248774946e

It was a corner case of using "recorded data" for the filter (so the query adds that IN() clause), and not having WHERE on the front of the join's WHERE clause (which is optional, and we automagically changed it to AND if we had out own WHERE clause).

-- hugh