Hi,

I've been battling with this for a while now so thought would ask. I was asked to change the default sort order on a collection. The new sort field is a foreign key on a foreign key. The actual collection sort works fine and all works. However, when I go into the edit item view for any item in this newly sorted collection, I get an error message.

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in D:\xampp\htdocs\www.site.com\libraries\joomla\database\database\mysqli.php on line 306

Other than the error message, it's all fine, I can view and edit the item with no problem.

The fly view does not show the error message.

It's my understanding that this error is due to a fault in the current model query, but, as I say, the actual colection produces no error message and is ordered as I would expect it to be.

The query is:
SELECT a.* , _genus_plant_key_.genus AS `_genus_plant_key_genus` , _genus_plant_key_.genus_family_key AS `_genus_plant_key_genus_family_key` , _genus_plant_key_genus_family_key_.botanical_family AS `_genus_plant_key_genus_family_key_botanical_family` , _genus_plant_key_genus_family_key_.german_family AS `_genus_plant_key_genus_family_key_german_family` FROM `vs4go_garden_plants` AS a LEFT JOIN `vs4go_garden_genus` AS _genus_plant_key_ ON _genus_plant_key_.id = a.genus_plant_key LEFT JOIN `vs4go_garden_family` AS _genus_plant_key_genus_family_key_ ON _genus_plant_key_genus_family_key_.id = _genus_plant_key_.genus_family_key ORDER BY _genus_plant_key_genus_family_key_botanical_family

and in the model this is what I have changed.

$pre_order = '_genus_plant_key_genus_family_key_botanical_family';

If I use the old sort order which was:

$pre_order = 'a.genus_plant_key';

and then sort the collection by manually clicking on the botanical family column, then the identical query is created, but I do not get any error message when going into the item edit screen.

Having spent a couple of days on this and going round in circles, it would be great if someone could help.

Thanks very much in advance

Because you cannot use aliases in the ordering command.

The joined aliased table : _genus_plant_key_genus_family_key_
The field behind it : botanical_family

So, try using the '.' :

Thank you, this answer helped me