missing 'where'

[Fred04]

in my databasejoin element I filter the data

where company.id IN (SELECT user.idcomp
FROM user WHERE user.idjuser = {$my->id})

it used to work perfect but my databasejoin element no longer returns anything. I have the following warning:

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 '.idjuser = 42) ORDER BY text ASC' at line 3 SQL=SELECT DISTINCT(`company`.`id`) AS value, descr AS text FROM `company` AS `company` WHERE company.id IN (SELECT user.idcomp FROM user user.idjuser = 42) ORDER BY text ASC

the 'WHERE' is missing; why??

based on my analysis:
c9c3d0bb7224bb6aa80c6f1e6b734cf78a96f062 is the first bad commit

 

[cheesegrits]

Can you test this fix for me:

Edit ./plugins/fabrik_element/databasejoin/databasejoin.php, around line 680 is:

		else
		{
			$where = JString::str_ireplace('WHERE', '', $where);
			$query->where($where);
			return $query;
		}

Change that str_ireplace line to this:

			$where = FabrikString::ltrimword($where, 'WHERE');

-- hugh

 

[cheesegrits]

Ah, OK, that won't work as-is because of the leading whitespace on the $where.

I've committed a fix which should resolve this:

https://github.com/Fabrik/fabrik/commit/264a98937aef7dfc62dd0810adecfc97258cf80c

-- hugh

 

[cheesegrits]

Ah, OK, that won't work as-is because of the leading whitespace on the $where.

I've committed a fix which should resolve this:

https://github.com/Fabrik/fabrik/commit/264a98937aef7dfc62dd0810adecfc97258cf80c

-- hugh

 

[Fred04]

I'm using the latest Github
The problem of the missing WHERE is fixed... but now I have too many WHERE

Here is the error I have

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 'where company.id IN (SELECT user.idcomp FROM user WHERE user.idjuser = 42) ORDE' at line 3 SQL=SELECT DISTINCT(`company`.`id`) AS value, descr AS text FROM `company` AS `company` WHERE where company.id IN (SELECT user.idcomp FROM user WHERE user.idjuser = 42) ORDER BY text ASC

thanks for the support

 

[Fred04]

I updated with the latest Githun and the problem no longer exists.