I'm writing a list to manage the J2STORE orders attached to users.
Since the J2store orders are stored in the #__j2store_orders and #__j2store_orderitems tables I used a JOIN field as seen on the attached screenshots.
When I don't put order fields in the list items there is no issue (but of course the list is pretty useless!).
If we display the SQL request we get :
SELECT t0.id AS pid,t0.pk AS pk,t0.pkb AS pkb,t0.parent_id AS parent,t0.author_id AS author,t0.cck AS cck,t0.storage_location AS loc,tt.id AS type_id,tt.alias AS type_alias
FROM `#__cck_core` AS t0
LEFT JOIN `#__users` AS t3 ON t3.id = t0.pk
LEFT JOIN `#__j2store_orders` AS t1 ON t1.user_id = t3.id
LEFT JOIN `#__j2store_orderitems` AS t2 ON t2.j2store_orderitem_id = t1.j2store_order_id
LEFT JOIN `#__cck_core_types` AS tt ON tt.name = t0.cck
WHERE t0.cck = 'user'
ORDER BY t3.name ASC
LIMIT 25
BUT when we put at least one field (j2store field with a storage in the joined table) in the items tab the SQL request is completely modified (!!) with an error message :
1054 Unknown column 'id' in 'where clause' SQL=SELECT * FROM #__j2store_orders WHERE id IN (357,358)
(for the record 357 and 358 are the IDs of the existing users)
The newly added field in the items tab has the following storage:
STANDARD / FREE / order_id / #__j2store_orders
--> So why just adding a field in the items tab corrupt the SQL query please ??
Thanks
Cyril