How do I fix "500 error Fabrik has generated an incorrect query for the list:

[Metabern]

Fabrik 3.9
Joomla 3.9.10

I am doing the same series of Fabrik joins to two lists

Joins are built on two tables that manage and edit Purchase orders From Virtumart

List one, the joins work: custom_purchase_order_numbers to virtuemart_orders to virtuemart_orderitems to virtuemart_products.

List two, the joins work: custom_purchase_order_revisions to custom_purchase_order_numbers to virtuemart_orders to virtuemart_orderitems to virtuemart_products.

Same setup with a revisions join fails with a 500 error

As far as I know all PK's in my databas are working.

All trashed elements, groups, lists and forms are deleted

Any Suggestions?
Cheers

 

[cheesegrits]

Try enabling J! debug and set error reporting to max, see if it gives you the actual MySQL error.

-- hugh

 

[Metabern]

This is what I get:

500 Fabrik has generated an incorrect query for the list REVS: <br /><br /><pre>Unknown column '#__virtuemart_order_items.virtuemart_product_id' in 'on clause'</pre><br /><pre></pre><br />
/home/mysite/public_html/components/com_fabrik/models/list.php:948

 

[cheesegrits]

I dunno ... does that look like one of the fields you are joining on?

-- hugh

 

[Metabern]

It is one of the fields I'm joining on, moreover the join is being used on another list and its functioning perfectly

 

[cheesegrits]

MySQL doesn't seem to think #__virtuemart_order_items.virtuemart_product_id exists.

-- hugh

 

[Metabern]

It's strange, we are using the same table and making all of the necessary joins successfully in another list.

 

[troester]

Do you have dbjoin elements pointing to #__virtuemart_order_items and using the table name directly in e.g. concat label or data-where?
You may have to use {thistable} (literally) to get the correct table aliases.

 

[Metabern]

No data-where or concats, just conventional fabrik joins. All with PK's. Myself and my developer looked into it yesterday and found that the database joins aren't executed in the order that they are listed.

 

[Metabern]

here is an example of our logged join list.


'join' =>
array (
0 =>
JDatabaseQueryElement::__set_state(array(
'name' => 'LEFT JOIN',
'elements' =>
array (
0 => ' `hm9dh_vmcs_vendor_purchase_order_nums` AS `hm9dh_vmcs_vendor_purchase_order_nums` ON `hm9dh_vmcs_vendor_purchase_order_nums`.`purchase_order_id` = `hm9dh_vmcs_vendor_purchase_order_revs`.`purchase_order_id`',
),
'glue' => ',',
)),
1 =>
JDatabaseQueryElement::__set_state(array(
'name' => 'LEFT JOIN',
'elements' =>
array (
0 => ' `hm9dh_vmdropshipping_warehouse` AS `hm9dh_vmdropshipping_warehouse` ON `hm9dh_vmdropshipping_warehouse`.`warehouse_id` = `hm9dh_vmcs_vendor_purchase_order_nums`.`warehouse_id`',
),
'glue' => ',',
)),
2 =>
JDatabaseQueryElement::__set_state(array(
'name' => 'LEFT JOIN',
'elements' =>
array (
0 => ' `hm9dh_virtuemart_orders` AS `hm9dh_virtuemart_orders` ON `hm9dh_virtuemart_orders`.`virtuemart_order_id` = `hm9dh_vmcs_vendor_purchase_order_nums`.`order_id`',
),
'glue' => ',',
)),
3 =>
JDatabaseQueryElement::__set_state(array(
'name' => 'LEFT JOIN',
'elements' =>
array (
0 => ' `hm9dh_virtuemart_products` AS `hm9dh_virtuemart_products` ON `hm9dh_virtuemart_products`.`virtuemart_product_id` = `hm9dh_virtuemart_order_items`.`virtuemart_product_id`',
),
'glue' => ',',
)),
4 =>
JDatabaseQueryElement::__set_state(array(
'name' => 'LEFT JOIN',
'elements' =>
array (
0 => ' `hm9dh_virtuemart_order_userinfos` AS `hm9dh_virtuemart_order_userinfos` ON `hm9dh_virtuemart_order_userinfos`.`virtuemart_order_id` = `hm9dh_virtuemart_orders`.`virtuemart_order_id`',
),
'glue' => ',',
)),
5 =>
JDatabaseQueryElement::__set_state(array(
'name' => 'LEFT JOIN',
'elements' =>
array (
0 => ' `hm9dh_virtuemart_order_items` AS `hm9dh_virtuemart_order_items` ON `hm9dh_virtuemart_order_items`.`virtuemart_order_id` = `hm9dh_virtuemart_orders`.`virtuemart_order_id`',
),
'glue' => ',',
)),
)

 

[troester]

The error message says #__table, in the debug output all is replaced by your J! prefix. Are you sure you don't have some custom code somewhere using #__table?

Gesendet von meinem SM-G930F mit Tapatalk

 

[cheesegrits]

This is one of those issues I'd have to debug, which means installing an Akeeba copy locally and setting up a PHP Storm project for it ... which is usually at least an hour of billable time.

-- hugh

 

[Metabern]

So I've done a manual update of Fabrik and reset / wiped all of my old test lists, forms elements, groups etc. This join is still not working. How do we proceed with getting this debug process going ASAP?

Cheers

 

[cheesegrits]

Robbie is going to PM you.

-- hugh