- Posts: 19482
[SOLVED] Search breaks when sorted by custom field (date)
9 years 8 months ago - 9 years 8 months ago #62066
by ggppdk
-- 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 review. Thanks!
Replied by ggppdk on topic Search breaks when sorted by custom field (date)
Here is an example ordering by file field total downloads and then by a date field
notice the table alias
also note that the ordering clause is created by other file:
component/com_flexicontent/classes/flexicontent.helper.php
notice the table alias
Code:
AS f2
also note that the ordering clause is created by other file:
component/com_flexicontent/classes/flexicontent.helper.php
Code:
SELECT SQL_CALC_FOUND_ROWS i.id
FROM #__content AS i
JOIN #__categories AS c
ON c.id = i.catid
JOIN #__flexicontent_items_ext AS ie
ON ie.item_id = i.id
JOIN #__flexicontent_types AS ty
ON ie.type_id = ty.id
LEFT JOIN (SELECT f.item_id,
Sum(fdat.hits) AS file_hits
FROM #__flexicontent_fields_item_relations AS f
LEFT JOIN #__flexicontent_files AS fdat
ON fdat.id = f.value
WHERE f.field_id = 35
GROUP BY f.item_id) AS dl
ON dl.item_id = i.id
LEFT JOIN #__flexicontent_fields_item_relations AS f2
ON f2.item_id = i.id
AND f2.field_id = 52
WHERE 1
AND i.state IN ( 1, -5, 2 )
AND c.published = 1
AND ( i.publish_up = '0000-00-00 00:00:00'
OR i.publish_up <= UTC_TIMESTAMP() )
AND ( i.publish_down = '0000-00-00 00:00:00'
OR i.publish_down >= UTC_TIMESTAMP() )
AND ty.access IN ( 0, 1, 1 )
AND c.access IN ( 0, 1, 1 )
AND i.access IN ( 0, 1, 1 )
AND ( ie.language LIKE 'el%'
OR ie.language = "*" )
AND ie.type_id IN ( 1 )
AND i.id IN (SELECT id
FROM fc_filter_iids_32)
GROUP BY i.id
ORDER BY file_hits DESC,
Cast(f2.value AS date) ASC,
i.title
-- 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 review. Thanks!
Last edit: 9 years 8 months ago by ggppdk.
Please Log in or Create an account to join the conversation.
- Ninjakitten
- Topic Author
- Offline
- Senior Member
-
9 years 8 months ago #62069
by Ninjakitten
Replied by Ninjakitten on topic Search breaks when sorted by custom field (date)
I appreciate your help, but I don't feel as though you understand what I'm saying, and that's really becoming frustrating. 
I'm not sure how I can rephrase it, but I'll try.
Please look at line 444 and line 461 in flexiadvsearch.php, and notice the table alias:
and likewise, line 639 in the same file:
That is not something I did. That is the untouched distributed file, straight from the 15-dev zip.
Changing the alias for the #__flexicontent_fields table from f to something unused (fi) resolved the custom ordering 404 error.
#__flexicontent_fields_item_relations is aliased to f 4 times (lines 438, 444, 455, 461), and to f2 4 times (lines 473, 479, 490, 496).
#__flexicontent_fields is aliased only once, and it is aliased to f (line 639).
Resolving this resolves the error I originally posted about.
I'm not trying to do the second level ordering via PHP -- I just want it to work through the normal search configuration options.
Your image in the example below SHOWS the level 2 drop-down.
This matters because when the drop-down is SHOWN, level 2 ordering DOES WORK -- even without using the drop-down.
When the drop-down is NOT SHOWN, level 2 ordering DOES NOT work.
...and on changing to 15-dev, now only the first result on a page seems to have a categories array (and yes, I do have categories set as a field that search should render).
All the other results on the page have an empty array.
This result is the same whether I use my custom default_results.php or the unchanged one from the zip.
I'm not sure how I can rephrase it, but I'll try.Please look at line 444 and line 461 in flexiadvsearch.php, and notice the table alias:
Code:
LEFT JOIN #__flexicontent_fields_item_relations AS f
Code:
JOIN #__flexicontent_fields as f
Changing the alias for the #__flexicontent_fields table from f to something unused (fi) resolved the custom ordering 404 error.
#__flexicontent_fields_item_relations is aliased to f 4 times (lines 438, 444, 455, 461), and to f2 4 times (lines 473, 479, 490, 496).
#__flexicontent_fields is aliased only once, and it is aliased to f (line 639).
Resolving this resolves the error I originally posted about.
I'm not trying to do the second level ordering via PHP -- I just want it to work through the normal search configuration options.
Your image in the example below SHOWS the level 2 drop-down.
This matters because when the drop-down is SHOWN, level 2 ordering DOES WORK -- even without using the drop-down.
When the drop-down is NOT SHOWN, level 2 ordering DOES NOT work.
...and on changing to 15-dev, now only the first result on a page seems to have a categories array (and yes, I do have categories set as a field that search should render).
All the other results on the page have an empty array.
This result is the same whether I use my custom default_results.php or the unchanged one from the zip.
Please Log in or Create an account to join the conversation.
- Ninjakitten
- Topic Author
- Offline
- Senior Member
-
- Ninjakitten
- Topic Author
- Offline
- Senior Member
-
9 years 8 months ago #62079
by Ninjakitten
Replied by Ninjakitten on topic Search breaks when sorted by custom field (date)
This new version definitely seems to have fixed the custom date field sorting issue and the categories. Thank you!
Unfortunately, it seems like the 2nd level ordering for search results is still only working when the drop-down is displayed.
Unfortunately, it seems like the 2nd level ordering for search results is still only working when the drop-down is displayed.
Please Log in or Create an account to join the conversation.
