Error in sql query on Add

[dimoss]

Hi

I have a dbjoin element with the folowing join query:

INNER JOIN entr_user_usergroup_map ON {thistable}.id = entr_user_usergroup_map.user_id
WHERE entr_user_usergroup_map.group_id = 2

What is I want to achive is to select only the users who belong to the registered group with group_id = 2
The query works ok in phpmyadmin and it brings also the registered users when I make it filtered.

However when I click to add a new record on this form, I get the following:

1064 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 'INNER JOIN entr_user_usergroup_map ON entr_users.id = entr_user_usergroup_map.us' at line 3 SQL=SELECT DISTINCT(`entr_users`.`username`) AS value, `username` AS text FROM `entr_users` AS `entr_users` WHERE INNER JOIN entr_user_usergroup_map ON entr_users.id = entr_user_usergroup_map.user_id WHERE entr_user_usergroup_map.group_id = 2 ORDER BY text ASC

What is the problem?

Thanks!

 

[cheesegrits]

I have a dbjoin element with the folowing join query:

You mean in the "Joins WHERE and/or ORDER BY" query, or the "Additional JOIN Statemenets"?

-- hugh

 

[dimoss]

Hi Hugh

I tried in both positions and I got the error in both.
Thanks.

 

[Sophist]

There is an extra WHERE keyword in the query for some reason:

1064 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 'INNER JOIN entr_user_usergroup_map ON entr_users.id = entr_user_usergroup_map.us' at line 3 SQL=SELECT DISTINCT(`entr_users`.`username`) AS value, `username` AS text FROM `entr_users` AS `entr_users` WHERE INNER JOIN entr_user_usergroup_map ON entr_users.id = entr_user_usergroup_map.user_id WHERE entr_user_usergroup_map.group_id = 2 ORDER BY text ASC


S

 

[dimoss]

Hi Sophist

I haven't put any extra WHERE in the query. The query is exactly as I wrote in my 1st post. I don't know why the extra WHERE is on the query.
Thanks.

 

[Sophist]

Yes - I think it is a bug.

 

[cheesegrits]

The reason I asked which box it was in, as you have both a WHERE and a JOIN, so it wasn't clear which one you might choose.

What I would expect you to have to do is put the JOIN part in the "Extra joins" box, and the WHERE part in the "Where" box.

However, if we're putting it in the wrong place (after our WHERE) regardless, I suspect having an INNER JOIN is tickling a bug in our code anyway.

I'll go have a look.

-- hugh

 

[cheesegrits]

Seems to work for me if I put the WHERE part in the WHERE box, and the JOIN part in the JOIN box. The query Fabrik then produces is:

SELECT DISTINCT(`y7hij_users`.`id`) AS value, `id` AS text FROM `y7hij_users` AS `y7hij_users` INNER JOIN #__user_usergroup_map ON y7hij_users.id = #__user_usergroup_map.user_id WHERE #__user_usergroup_map.group_id = 2 ORDER BY text ASC

... which produces exacty what I'd expect on the element's dropdown.

-- hugh

 

[cheesegrits]

Here's a screenshot of the settings:



-- hugh

 

[dimoss]

Hi Hugh

You are correct. It's different than in F. 2.x
It worked as you wrote!
Thanks!