• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

1054 Error unknown column with all joined tables with repeat tables

I am getting an unknown column error with all tables with database join elements and associated repeat tables. I first noticed this after an update from Github, but I restored from multiple backups going back two weeks and found the same problem. I restarted my VPS and my mysql servers and cleared browser cache but the problem persists.

Any ideas?
 
Thank you for your quick response.

This is the error I get when trying to open a table that is not joined but has dbjoin elements in it:

Unknown column 'stratplanstrategies_repeat_personincharge.name' in 'IN/ALL/ANY subquery' SQL=SELECT `stratplanstrategies_repeat_personincharge`.id AS id,`stratplanstrategies_repeat_personincharge`.parent_id, `stratplanstrategies_repeat_personincharge`.`personincharge` AS value, `gecw1_users`.`name` AS text FROM `stratplanstrategies_repeat_personincharge` LEFT JOIN gecw1_users ON `gecw1_users`.`id` = `stratplanstrategies_repeat_personincharge`.`personincharge` WHERE stratplanstrategies_repeat_personincharge.`name` IN ( SELECT gecw1_users.name FROM gecw1_users, gecw1_user_usergroup_map WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id AND gecw1_user_usergroup_map.group_id = 24 AND gecw1_users.block <> 1) LIMIT 0, 160
 
I removed the following where clause on the dbjoin element, and the problem resolved:

WHERE {thistable}.`name`
IN ( SELECT gecw1_users.name FROM gecw1_users, gecw1_user_usergroup_map
WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id
AND (gecw1_user_usergroup_map.group_id = 21 OR gecw1_user_usergroup_map.group_id = 25)
AND gecw1_users.block <> 1)

I think my problem is on the first line with 'thistable', but I am still experimenting with other options.

I still need to be able to filter the list if possible.

This all worked until it didn't and then even backups that had worked fine would not work anymore. Could a mysql server change be causing this?


J3.3
Fabrik 2.2
 
Very strange. The only possibility I can see that a change in MySQL version might alter the behavior of would be the inconsistency in name quoting, where you have stratplanstrategies_repeat_personincharge.`name` with the field name quoted but not the table name. Try specifying just {thistable}.name in your WHERE clause, without the back quotes (or add back quotes around the placeholder as well) see if that works.

Not ideal, we should really detect the context {thistable} is used in and whether it has been quoted in the text, and whether it has a field name after it, and if that has been quoted, and normalize the whole thing. But I seem to recall trying that, and it turns out to be a pain in the backside, LOL! So we just replace {thistable} with the alias being used for that joined table at the time, and leave it at that.

-- hugh
 
Is there actually a name field in stratplanstrategies_repeat_personincharge? In the main part of that query, name seems to be coming from the users table, not that repeat table.

If you execute that SELECT in phpMyAdmin by hand, does it run? So this part:

Code:
SELECT gecw1_users.name FROM gecw1_users, gecw1_user_usergroup_map
WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id
AND (gecw1_user_usergroup_map.group_id = 21 OR gecw1_user_usergroup_map.group_id = 25)
AND gecw1_users.block <> 1

-- hugh
 
Oh, I think I see. Instead of {thistable}.name, try `gecw1_users`.`name`

The name field isn't selected in the main query, it uses the name field from the users table it joins to. So that's what you'll need to use in your WHERE query.

-- hugh
 
That is resulting in:
Code:
[CENTER][FONT=NotoSansRegular][COLOR=#000000]UNKNOWN COLUMN 'GECW1_USERS.NAME' IN 'IN/ALL/ANY SUBQUERY' SQL=SELECT DISTINCT(`STRATPLANSTRATEGIES_REPEAT_PERSONINCHARGE`.`ID`) AS VALUE, `NAME` AS TEXT FROM `GECW1_USERS` AS `STRATPLANSTRATEGIES_REPEAT_PERSONINCHARGE` WHERE `GECW1_USERS`.`NAME` IN (SELECT GECW1_USERS.NAME FROM GECW1_USERS, GECW1_USER_USERGROUP_MAP WHERE GECW1_USERS.ID = GECW1_USER_USERGROUP_MAP.USER_ID AND (GECW1_USER_USERGROUP_MAP.GROUP_ID = 21 OR GECW1_USER_USERGROUP_MAP.GROUP_ID = 25) AND GECW1_USERS.BLOCK <> 1) ORDER BY TEXT ASC[/COLOR][/FONT][/CENTER]
 
You may have to use `text` for that, then. Or STRATPLANSTRATEGIES_REPEAT_PERSONINCHARGE.text, which is what the users table is getting aliased to in that query.

Not sure why everything is capitalized in that quote, you'll have to capitalize it correctly.

But I have a feeling that may not work everywhere, as that WHERE clause will be applied in other queries, where we may not be using that table naming, and we'll be using the actual field name, and not aliasing it to the generic 'text'.

Let me know. It may be that we'll have to add some more aliasing in that query, so a WHERE clause can be contructed which will work in all cases.

Let me know.

 
I used:

Code:
SELECT stratplanstrategies_repeat_personincharge.text FROM gecw1_users, gecw1_user_usergroup_map
WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id
AND (gecw1_user_usergroup_map.group_id = 21 OR gecw1_user_usergroup_map.group_id = 25)
AND gecw1_users.block <> 1

And received the following error:

Code:
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 'SELECT stratplanstrategies_repeat_personincharge.text FROM gecw1_users, gecw1_us' at line 4 SQL=SELECT `stratplanstrategies_repeat_personincharge`.id AS id,`stratplanstrategies_repeat_personincharge`.parent_id, `stratplanstrategies_repeat_personincharge`.`personincharge` AS value, `gecw1_users`.`name` AS text FROM `stratplanstrategies_repeat_personincharge` LEFT JOIN gecw1_users ON `gecw1_users`.`id` = `stratplanstrategies_repeat_personincharge`.`personincharge` WHERE SELECT stratplanstrategies_repeat_personincharge.text FROM gecw1_users, gecw1_user_usergroup_map WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id AND (gecw1_user_usergroup_map.group_id = 21 OR gecw1_user_usergroup_map.group_id = 25) AND gecw1_users.block <> 1 LIMIT 0, 160


I then used:

Code:
SELECT `name` FROM gecw1_users, gecw1_user_usergroup_map
WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id
AND (gecw1_user_usergroup_map.group_id = 21 OR gecw1_user_usergroup_map.group_id = 25)
AND gecw1_users.block <> 1

And I received the following error:

Code:
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 'SELECT `name` FROM gecw1_users, gecw1_user_usergroup_map WHERE gecw1_users.id =' at line 4 SQL=SELECT `stratplanstrategies_repeat_personincharge`.id AS id,`stratplanstrategies_repeat_personincharge`.parent_id, `stratplanstrategies_repeat_personincharge`.`personincharge` AS value, `gecw1_users`.`name` AS text FROM `stratplanstrategies_repeat_personincharge` LEFT JOIN gecw1_users ON `gecw1_users`.`id` = `stratplanstrategies_repeat_personincharge`.`personincharge` WHERE SELECT `name` FROM gecw1_users, gecw1_user_usergroup_map WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id AND (gecw1_user_usergroup_map.group_id = 21 OR gecw1_user_usergroup_map.group_id = 25) AND gecw1_users.block <> 1

The example in post #5 above was working at one point before upgrades.
 
I'm going to have to login and take a look, I'm losing the plot. Which element / form / list is this on?

-- hugh
 
Hugh:

I would love to dive back into this one. Without the filter, the checkbox list is too long.

To answer your question:

Form: Strategic Plan: Strategies
Element: strategygroupmembers

I have created login credentials in my sites. Please let me know.
 
Given that I can run the command in phpmyadmin as described in post #8 above, I just created a view based on:

Code:
SELECT gecw1_users.name FROM gecw1_users, gecw1_user_usergroup_map
WHERE gecw1_users.id = gecw1_user_usergroup_map.user_id
AND (gecw1_user_usergroup_map.group_id = 21 OR gecw1_user_usergroup_map.group_id = 25)
AND gecw1_users.block <> 1

And then used the dbjoin element with the newly created view to accomplish the task. Problem solved.
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top