Advance Search not Working when applying Data - where in databasejoin plugin

[tiopourtu]

Hello Rob,

I have experiencing strange behavior in my Fabrik application.

Let's say I have en element named 'Entity' using databasejoin plugin that rendering from site database. To limit the dropdown list I apply [Data - where]->[Joins where and/or order by statement (SQL)] with a command line: {thistable}.active = 1, so when I select the dropdown it will only show active 'Entity'. It was working just fine, until I do the Advance Search from the front-end List.

When I select Entity EQUALS .... in Advance Search, nothing happen.

But when I remove the statement: {thistable}.active = 1, then when I select Entity EQUALS ... I got the dropdown showing All Entities (both active & non active) and the Advance Search works fine.

Is it normal or something wrong with my setup ?

Regards,

 

[troester]

Are you running a recent GitHub update?
I think there was a fix concerning advanced search and dbjoins.

 

[tiopourtu]

Dear troester,

I'm not sure that my fabrik is the lates, The Verison is 3.1.1 and Dated March 2014. I try to update from github by downloading joomla3 branch. Is it the latest one ?

Regards,

 

[tiopourtu]

Dear troester,

This is the error message I got:

filter query error: entity You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'base_entities.active = 1 ORDER BY `base_entities`.`shortname`ASC LIMIT 0, 100' at line 5 SQL=SELECT DISTINCT(`base_entities`.`shortname`) AS `text`, `base_employees`.`entity` AS `value` FROM `base_employees` LEFT JOIN `p0rt4_users` AS `p0rt4_users` ON `p0rt4_users`.`id` = `base_employees`.`username` LEFT JOIN `p0rt4_users` AS `p0rt4_users_0` ON `p0rt4_users_0`.`id` = `base_employees`.`updateby` LEFT JOIN `base_entities` AS `base_entities` ON `base_entities`.`id` = `base_employees`.`entity` WHERE `base_employees`.`entity` IN ('5','6') base_entities.active = 1 ORDER BY `base_entities`.`shortname`ASC LIMIT 0, 100

I don't know where the IN('5','6') came from.

 

[troester]

{thistable}.active = 1

Set
WHERE {thistable}.active = 1

 

[tiopourtu]

Dear troester,

Sorry for my mistake, I think I got the wrong setup.
I just need to be confirmed on this one : In [Data-where] section of databasejoin setup, is it true that I should put WHERE before {thistable}.active = 1. Because this way, the Advance Search bahave as I expected.

So the complete text is: WHERE {thistable}.active = 1

Regards,

 

[troester]

Post crossing

 

[tiopourtu]

Dear troester,

Hahaha, when I post reply to this thread suddenly I read your post telling me to set WHERE {thistable}.active = 1. How coincidence... anyway thank's a lot for your replies...

PROBLEM SOLVED

Regards,