Parisi
Member
I am getting this error when running an advanced search on a list in Fabrik. Here is short version of the scenario:
The Ben table is a "lookup" tool for the elements in the Abe table. In other words, some of the columns in the Abe table store a standardized shortcode, and the Ben table contains those shortcodes and their corresponding meanings in human-readable English. Databasejoin elements to Ben in the Abe list allow Fabrik to display the English to Fabrik users, rather than displaying the shortcodes.
We will call the three utilized columns from Ben:
Here is the error + query Fabrik gives from an advanced search on the Abe list. This search is run on one the elements in Abe that is databasejoined to Ben (the search would be: WHERE abe.column2 EQUALS somerealvalue):
Need some help here. Not sure if this is because Fabrik is not giving specificity where it is needed or because of a way I have joins set up.
- I have multiple databasejoin elements in the main list ("Abe") joined to another, single table ("Ben"), in order to display values from Ben in the Abe list.
- There is also a third table ("Carl") joined to the Abe list in one databasejoin element.
- In the generated SQL that Fabrik shows when displaying the error, the "where" clause is referencing a column of Ben without specifying which table the column is in (which I believe is causing the ambiguity error).
The Ben table is a "lookup" tool for the elements in the Abe table. In other words, some of the columns in the Abe table store a standardized shortcode, and the Ben table contains those shortcodes and their corresponding meanings in human-readable English. Databasejoin elements to Ben in the Abe list allow Fabrik to display the English to Fabrik users, rather than displaying the shortcodes.
We will call the three utilized columns from Ben:
- shortcode - contains the shortcodes from Abe
- translation - the readable English that Fabrik displays
- columnlookup - contains the names of the columns in Abe that are databasejoin elements
Here is the error + query Fabrik gives from an advanced search on the Abe list. This search is run on one the elements in Abe that is databasejoined to Ben (the search would be: WHERE abe.column2 EQUALS somerealvalue):
Code:
500 filter query error: column2 Column 'columnlookup' in where clause is ambiguous SQL=
SELECT DISTINCT (`ben_0`.`translation`), `abe`.`column2`
FROM `abe`
LEFT JOIN `carl` ON `carl`.`id` = `abe`.`carl_id`
LEFT JOIN `ben` AS `ben` ON `ben`.`shortcode` = `abe`.`column1`
LEFT JOIN `ben` AS `ben_0` ON `ben_0`.`shortcode` = `abe`.`column2`
WHERE `abe`.`column2`
IN ([the list of the values from ben.shortcodes where ben.columnlookup = abe.column2 is produced here by Fabrik])
AND columnlookup = 'column2'
Need some help here. Not sure if this is because Fabrik is not giving specificity where it is needed or because of a way I have joins set up.