Shoppers error VM 3.0.7.2 J3.4.1

[thewitt]

If I select Shoppers from the Virtuemart menu, I get the following error:

Code Select Expand

1052 Column 'virtuemart_user_id' in order clause is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS ju.id AS id , ju.name AS name , ju.username AS
username , ju.email AS email , IFNULL(vmu.user_is_vendor,"0") AS is_vendor , IFNULL(sg.shopper_group_name, "") AS shopper_group_name FROM tsaga_users
AS ju LEFT JOIN tsaga_virtuemart_vmusers AS vmu ON ju.id = vmu.virtuemart_user_id LEFT JOIN tsaga_virtuemart_vmuser_shoppergroups AS vx ON ju.id =
vx.virtuemart_user_id LEFT JOIN tsaga_virtuemart_shoppergroups AS sg ON vx.virtuemart_shoppergroup_id = sg.virtuemart_shoppergroup_id GROUP BY ju.id
ORDER BY virtuemart_user_id ASC LIMIT 0, 30


VM 3.0.7.2
JM 3.4.1

[thewitt]

It can be corrected by simply changing the ORDER BY clause to be

ORDER BY vmu.virtuemart_user_id

but i can't figure out where to change this... Still digging.

[Milbo]

So when you are in the BE? The list does not allow to sort by ids, actually. At least not in j2.5. I wonder how you get this query. Anyway is almost fixed.

If you wonder how. Check the constructor of the user model.

[thewitt]

The more I look at this, I think it should just be:

ORDER BY id

and I can't see where it broke....  but it does look like it's broken and should not be using virtuemart_user_id in the order clause at all.

[thewitt]

This is the DEFAULT order by clause in the query, and I think it's incorrect and should simply be ID

[Milbo]

Hmmm, I just wanted to post my solution

Code Select Expand


$this->setToggleName('user_is_vendor');
$this->addvalidOrderingFieldName(array('ju.username','ju.name','sg.virtuemart_shoppergroup_id','shopper_group_name','shopper_group_desc','vmu.virtuemart_user_id') );
$this->setMainTable('vmusers');
$this->removevalidOrderingFieldName('virtuemart_user_id');
array_unshift($this->_validOrderingFieldName,'ju.id');


But you are right. If we want to sort also users who are only joomla users and not already vmusers, then we should use id.

[Milbo]

The code above is the same. Just use in the BE user view, default.php line 65

Code Select Expand


<th><?php echo $this->sort('ju.id', 'COM_VIRTUEMART_ID') ?></th>


Thank you for reporting

[thewitt]

Thank you !

[Milbo]

Just for explanation, the

Code Select Expand


array_unshift($this->_validOrderingFieldName,'ju.id');


Sets the ordering ju.id as the default.

[thewitt]

Sorry. It did not make any difference. I'm trying now to understand why.

[thewitt]

Just a typo... thank you

[thewitt]

Can you see any reason NOT to add Sort by Email to this screen in default.php?

Code Select Expand

<th width="25%"><?php echo $this->sort('ju.email', 'COM_VIRTUEMART_EMAIL'); ?></th>

Seems to make it more complete to me.

I needed to add ju.email to the constructor too, but it seems to work fine.

[Milbo]

yeh, why not. Added.