problem with order by custom field

Hi,

I would like some help to troubleshoot an ordering problem:
- I have a list of items I want to order by a custom field, alphabetically.
- I set it in the category settings, and it is almost ok, but some of my items are still in the wrong place.
- If I go to the menu item and enable overrides and select the simple blog template, I can see they are all in the correct order.
(- I cleared joomla cache but it didn't solve the problem)

What and where should I look to find the cause of the issue? I guess it must be somewhere in my custom template but I don't know how to find the "orderby" function.

Thanks for your help!

Gauthier

Hello

you mean ordering the exact same items e.g. 17 items,
by a specific custom field

does not give the same order if you enable it in category parameters
with the order produced by menu item ?

The custom order lets you select the type of data in the custom field
- alphanumeric
- integer
- float
- date

in a copy of the website, or you can even do it for a few seconds on the live site, just make sure you backup the changed file

uncomment about line 677
of file
component/com_flexicontent/models/category.php

and provide here the 2 SQL queries of the 2 cases

Thank you for your answer ggppdk,

Here are the queries :

SELECT SQL_CALC_FOUND_ROWS DISTINCT i.id FROM #__flexicontent_items_tmp AS i JOIN #__flexicontent_types AS ty ON i.type_id = ty.id JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN #__categories AS c ON c.id = i.catid LEFT JOIN #__flexicontent_fields_item_relations AS f ON f.item_id = i.id AND f.field_id=16 LEFT JOIN #__flexicontent_fields_item_relations AS f2 ON f2.item_id = i.id AND f2.field_id=16 WHERE 1 AND rel.catid IN ('114', '115', '116', '117', '118', '119', '271') AND ty.access IN (0,1,1,2,3,6,9,10) AND c.access IN (0,1,1,2,3,6,9,10) AND i.access IN (0,1,1,2,3,6,9,10) ORDER BY ISNULL(f.value), f.value ASC, ISNULL(f2.value), f2.value ASC, i.title

SELECT i.*, ie.*, u.name as author, ty.name AS typename, ty.alias AS typealias, rel.catid as rel_catid, c.title AS maincat_title, c.alias AS maincat_alias, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.alias) ELSE i.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as categoryslug, c.access as category_access, ty.access as type_access, 1 AS has_access FROM #__content AS i JOIN #__flexicontent_items_ext AS ie ON ie.item_id = i.id JOIN #__flexicontent_types AS ty ON ie.type_id = ty.id JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN #__categories AS c ON c.id = i.catid LEFT JOIN #__users AS u ON u.id = i.created_by WHERE i.id IN (8232,9254,5649,8863,8233,7924,8557,7455,8231,104,8867,8520,7457,7312,8252,5629,9255,7306,8167) GROUP BY i.id

Hello,

I don't really understand what is written in these queries. Is the "order by isnull" causing the problem?

Let me know if you think you can help me or not. Thank you!

Gauthier

Hello

The ordering : ISNULL(f.value)
will place fields without a value before others

This is what is happening
- when you order
1. by custom field and
2. you use ASCENDING (incrementing) order

then the items without a value for the field (NULL = no value) items are placed first

- In universal content module you have option to exclude these items

Ok, but then I really don't see what can cause the ordering to be broken. Can it be a from a setting in the item itself?
If I keep the same template, but use another ordering setting (like simply order by title) the alphanumerical order is also wrong.

Gauthier