7 years ago
5
Topic

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

Get a Book for SEBLOD
4229 Posts
Kadministrator
7 years ago
0
Level 1

I would say that problem is that you are mixing objects in one search, you can add tables by join but I think different objects won't work

548 Posts
joomleb
6 years ago
0
Level 1

Hi guys, 

@pulsarinformatique: Please, Have you found a solution about ?

6 years ago
0
Level 1

Hi

No, and I can't even understand Klas's explnation. What is a free query for if we can't use it for non standard joomla objects ?

thanks

cyril

4229 Posts
Kadministrator
6 years ago
1
Level 1

This does not work for the simple reason that there is no order_id column in the select part of the query. Joining a table is not enough, you need to add something like t1.order_id as order_id to the SELECT using search query field in append mode.

41 Posts
jukov_b1
6 years ago
0
Level 2

I will add some pepper, to a campaign such fields as field_x, group_x even received черес the attached inquiry don't make sense because it isn't provided in model work with the ready field, all the same will be the additional inquiry and only to article is based :) my consciousness has blown up from such restriction. But applying form&list a plug-in it is possible to build the same and not strongly to think :) (excuse for an off topic)

Get a VIP membership