I have specified a primary key for table 1, and it’s corresponding foreign key in table 2 within my postgres DB. Metabase recognizes this when I am in the ‘Data Model’ admin view. However, I cannot see any data from table 1 when looking at table 2, etc - the join doesn’t appear to be working within metabase, even though it works fine using raw sql. Any ideas? The column type is BIGINT, so should be easy to match.
Hi @kbitz
Not sure if I understand fully, but are you talking about Object Details view, where it displays a panel to the right called "This TABLE is connected to:" ?
Thanks for the response @flamber. I'm talking about the Data Model page, and then subsequently when you are creating a question and looking at the options for grouping. The columns are identical in the DB - both bigint, all the values match since the 2nd table is just an output from a python script that's doing some data transforms. I've worked around by having the script add in all the columns I had hoped to see via the join.
You can see down by the display options that it is not joining like it should.
@kbitz
Have you defined “Entity Name” on the “Feed Orders” table?
Without knowing your full structure, then I think you might be hitting this issue:
@flamber
I do not have ‘entity name’ specified in either table. There are no null values in the entity key column nor the foreign key column, so don’t think that’s it. I also cannot see the relationship reflected in the field dropdowns when I create a question and use either table.
@kbitz
It’s difficult to know, when everyone has different structures, so without some way of reproducing the problem, it can be very tricky to find the cause.
I cannot reproduce on 0.31.2 with multiple different datasources (H2, SQL2008, MySQL 5.7, MariaDB 10.3, Postgres 11.2)
EDIT: Do you see anything in the Metabase log, which could point at something? Or even the browser console?
@flamber I did some troubleshooting. When I had first created Table 2, I did not specify the foreign key column in postgres as ‘bigint’ and it defaulted to float, and so metabase errored when I attempted to link them together with a foreign key. I fixed this, and the error went away but had the issue outlined above.
I just created duplicate tables with different names but otherwise identical (e.g. create table copy as table original). Did the same exact foreign key join and it works fine.
There must be some cache somewhere that is not being cleared appropriately? I had clicked all the discard buttons, resync, etc before to no avail. Any ideas? Is there somewhere in the app database I can just clear out all history of those two tables so that metabase treats them as new? I could rename them but would be inconvenient to do that elsewhere.
@kbitz
You’re using 0.31.2? You can have a look in the internal database, in the table metabase_field to see what the *_type gets set to.
I think you might be hitting this issue:
https://github.com/metabase/metabase/issues/7923
1 Like
@flamber - Thanks - I fixed it. The column type had been resynced appropriately, but the column ‘visibility_type’ in the ‘metabase_field’ table was set to ‘sensitive’ - this was the only row in that table where the value was ‘sensitive’. I changed it to ‘normal’ and everything works now.