500 error from list joins

[Metabern]

I am trying to show a list of Virtuemart products with other VM product data applied.

I have been successful in doing 4 Left List joins from VM tables

In the attached screen shot you can see my progress from jos_viruemart_userinfos

to jos_viruemart_vmusers
to jos_viruemart_vmuser_shoppergroup
to jos_viruemart_products_shoppergroup
to jos_viruemart_products

No problems so far, everything works as expected

If I try to join jos_viruemart_products to jos_viruemart_products_en_gb or jos_viruemart_products_prices I get the following error:


500
Unknown column '#__virtuemart_product_shoppergroups.virtuemart_product_id' in 'on clause

Not sure why this is happening,

We tested this configuration with a manual SQL query and it worked correctly

Attached is a screen shot of the jos_viruemart_product_shoppergroups in PHPMyAdmin.

 

[troester]

I don't know if this is the reason here.
But in Fabrik one "leg" of a join must be a primary key (in Fabrik usually the id element), i.e. a joins must be from PK to FK or FK to PK.
You can't join on two arbitrary columns.

 

[Metabern]

So I checked the FK/PK on all of the joins and all of them have one of each (FK/PK)

When I Left list join jos_virtuemart_products to jobs_virtuemart_product_en_gb both are using virtuemart_product_id and it is the PK for both

So my question is what is the issue with a join of PK to PK?

 

[cheesegrits]

Just so I understand ... the problem occurs when you add a join where both "ends" are PKs?

-- hugh

 

[Metabern]

yes and both have the same name

 

[cheesegrits]

Yeah ... as Troester said, our code expects one (and only one) "end" of the join to be a PK, so we know which way round to build the JOIN clause. The code has obviously chosen the wrong way to build it.

Short of adding an extra setting to the join params, to manually tell Fabrik which direction the join goes in, I don't see a way round this one.

It's something I've wanted to do for a while, so we can do FK to FK joins (not involving a PK), but the amount of work involved is huge, on core code which is very, very complex.

-- hugh