connections appear to be using incorrect db on f3.1

[skyrun]

environment:

• j!3.1.1 f3.1
• have 3 connections setup to 3 different databases (all on same db server).
• added a databasejoin element (location element on campaigns form in app if you want to take a look) which joins to a table using a different connection. then when i display the table i get this error:

1146 Table '[B]sitebname[/B].2ndtablename' doesn't exist SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `1sttablename`.`id` AS `1sttablename___id`,
...more columns...
` LEFT JOIN `2ndtablename` AS `2ndtablename` ON `2ndtablename`.`id` = `1sttablename`.`location` LIMIT 0, 10

note how the tablename on the join doesn't have a the db name of the join specified on it. also note the error message incorrectly puts the 'site database' name (vs the db name of the joined db from the connection), so that's causing an error since that table isn't on the 'main' database, but instead on the other db i'm trying to join to.

UPDATE: tested and if i expressly specify the name of the db on the join as in ...LEFT JOIN dbfromconnection.'2ndtablename'... etc... on the SQL generated vs. not specifying which means it defaults to the site db, then the SQL works. so i think you're perhaps not specifying the db name on joins that are created by adding a databasejoin element that refers to a different database (which i agree is an uncommon use case to test).

 

[skyrun]

i know i've put alot of stuff in the f3.1 forum that i'm finding as i create my new app (hopefully it's helpful in addition to being a pain...) and you're fixing them fast (thanks!!!)

but fyi, THIS one is the most critical for me right now. i have unpublished these databasejoin elements in my app for now to avoid this issue. i suspect it's perhaps an issue in all versions of fabrik that use a non 'site database' connection on a databasejoin elements.

 

[jfquestiaux]

Unfortunately, as yet, you can't make a join in Fabrik between tables in different databases, even though the settings let you beleive you can (since you can actually select these tables). Maybe in Fabrik 4?

 

[troester]

A workaround: create a MySQL view in your main database, link a fabrik list to this view (make sure it picks the correct pk), use this list in the dbjoin element (not very comfortable, but should work).

 

[cheesegrits]

Troester is correct, you can't (yet) do cross database joins. Long story.

But as per Troester's response, the easiest workaround is just to create a view on the database your join element is on, for the table you want to join to, like:

CREATE VIEW yourview AS SELECT * FROM other_database.other_table

Not exactly optimal, but this kind of view is very low impact in MySQL, doesn't generate a lot of overhead. Only thing to watch for is that the * field list is "frozen" when you create the view, so if you add more fields on the other table, you have to delete and recreate the view to pick up the new fields.

I don't think you have to create a Fabrik List for it, as I think for join elements we show all tables / views from the connection in the join settings, we don't restrict it to just Lists Fabrik knows about.

-- hugh

 

[skyrun]

confirmed, the 'view' workaround worked nicely and has little if any overhead in my testing.