Cannot load list after creating database join

[witchypaws]

Hi everyone

So I did this

Installed Fabrik downloaded off the website latest version

Installed Joomla 3.9.15

Created a second database to store the data

Created several lists in the second database

I am trying to join two lists

One list is Users (not joomla users) - this is a list filled in by users to add extra contacts

One list is Workspaces

Both forms and lists were working fine on the front and back end with the two base fields and then a couple of other fields installed

Tried to create a database join element (workspace_owner) in the workspaces list that joins to the user_id in the users list

Both lists and the new element use the external database

BOOM. Workspaces won't load. The message says

500 Fabrik has generated an incorrect query for the list Workspaces:

But nothing else

Can someone please help

From my reading, I should be able to join one list to another as long as they are both in the same database but it's not working for some reason

Thanks

Jacqui

 

[witchypaws]

Further to this, by publishing the new field but not showing it in the list, I got the list to load but when I go to details, I get the following error

1146 Table 'bocosyli_jsaybll.#__fabrik_lists' doesn't exist

Which is true as all the fabrik tables are in the Joomla database

Have I missed a parameter when setting up the element that uses the database join functionality from the Joomla Fabrik installation but pulls all the lists and elements from the external database

Thanks

 

[troester]

You can't do cross joining in Fabrik (i.e. join two tables from different databases or create a dbjoin element pointing to a list in an other database).

 

[witchypaws]

Just confirming, the dbjoin element won't work even if both lists are in the same external database but fabrik is installed in then joomla database?

Meaning, if you want to join elements across lists, you have to store your data inside Joomla?

 

[troester]

It should do if all joined table are in the same database (with Fabrik meta tables in Joomla's database).

Did you change parameters of the Fabrik connection with ID=1 to the other database? I think this won't do.

Fabrik does support external databases but not in all circumstances and corner cases.

If I had the choice I would prefer to keep all in the Joomla database.

 

[witchypaws]

HI Troester

No, there are two connections (please see the screenshot), both lists, groups, forms, elements, etc are in the second one and the fabrik meta tables are in the first joomla database

Both connections work fine and lists, elements, etc are working from the second default database, just the joined element is not (so far) as it is returning the second message above

146 Table 'bocosyli_jsaybll.#__fabrik_lists' doesn't exist

It is looking for the Fabrik meta tables in the second database not the first

I have to keep the data in a separate database as that is a client requirement for a different API

I can provide access to the site if you need it

Warm regards

Jacqui

 

[troester]

I just tested, a dbjoin element belonging to a list in the 2nd database and pointing to a table in the 2nd database is working fine.
I even added a list join to a table in 2nd database, it's all working on my site.

BTW: if you are joining two tables in Fabrik (in list settings) one of the columns must be a primary key