problem with order by custom field

More
7 years 2 months ago #65699 by gauthier
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

Please Log in or Create an account to join the conversation.

More
7 years 2 months ago - 7 years 2 months ago #65702 by ggppdk
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
Code:
echo $query."<br/><br/> \n";

of file
component/com_flexicontent/models/category.php

and provide here the 2 SQL queries of the 2 cases


-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...
Last edit: 7 years 2 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

More
7 years 2 months ago #65706 by gauthier
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
Attachments:

Please Log in or Create an account to join the conversation.

More
7 years 2 months ago #65792 by gauthier
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

Please Log in or Create an account to join the conversation.

More
7 years 2 months ago - 7 years 2 months ago #65801 by ggppdk
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


-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...
Last edit: 7 years 2 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

More
7 years 2 months ago #65814 by gauthier
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

Please Log in or Create an account to join the conversation.

Moderators: vistamediajoomlacornerggppdk
Time to create page: 0.292 seconds
Save
Cookies user preferences
We use cookies to ensure you to get the best experience on our website. If you decline the use of cookies, this website may not function as expected.
Accept all
Decline all
Essential
These cookies are needed to make the website work correctly. You can not disable them.
Display
Accept
Analytics
Tools used to analyze the data to measure the effectiveness of a website and to understand how it works.
Google Analytics
Accept
Decline