[SOLVED] Search breaks when sorted by custom field (date)

Ninjakitten
Topic Author
Offline
Senior Member

8 years 11 months ago #62061 by Ninjakitten

Replied by Ninjakitten on topic Search breaks when sorted by custom field (date)

I believe I've fixed it -- it appears to be working now, though I don't know yet if I might've broken something else!
In flexiadvsearch.php I've changed:

Code:

$join_textfields = ' JOIN #__flexicontent_fields as f ON f.id=ts.field_id';

Code:

$join_textfields = ' JOIN #__flexicontent_fields as fi ON fi.id=ts.field_id';

on (what is at least for me) line 591 and

Code:

', GROUP_CONCAT(ts.search_index ORDER BY f.ordering ASC SEPARATOR \' \') AS text'

Code:

', GROUP_CONCAT(ts.search_index ORDER BY fi.ordering ASC SEPARATOR \' \') AS text'

on line 710.

This appears to have fixed the custom ordering both when set from the backend in the search menu item, and when done from a frontend drop-down.

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

Ninjakitten
Topic Author
Offline
Senior Member

8 years 11 months ago #62062 by Ninjakitten

Replied by Ninjakitten on topic Search breaks when sorted by custom field (date)

Hmm. Odd remaining issue: level 1 sorting is working fine now, but level 2 (which is supposed to be 'ordering in current category') seems only to work if I tell the level 2 ordering drop-down to be displayed on the frontend. If that drop-down isn't shown, the current category sorting for items with the same date doesn't happen. If it is, it does. This seems to be entirely independent of whether the level 1 dropdown is shown -- it's only the level 2 one that matters.

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

ggppdk
Offline
Administrator

8 years 11 months ago - 8 years 11 months ago #62063 by ggppdk

Replied by ggppdk on topic Search breaks when sorted by custom field (date)

Hello

both of these are already fixed

You can get a 3.015-dev build from here
github.com/FLEXIcontent/flexicontent-cck/releases

or download the v3.0.15-stable branch from here:
github.com/FLEXIcontent/flexicontent-cck/tree/3.0.x-stable
(click download ZIP)

[EDIT]
you may want to do "fix permissions" with admin tools before you upgrade
after you upgrade, please check this file has been updated (compare it with file in the package):
flexiadvsearch.php

-- 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: 8 years 11 months ago by ggppdk.

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

Ninjakitten
Topic Author
Offline
Senior Member

8 years 11 months ago #62064 by Ninjakitten

Replied by Ninjakitten on topic Search breaks when sorted by custom field (date)

No, I'm sorry -- I used the 15-dev build, confirmed it did update flexiadvsearch.php, and am back to the same 1066 error. My edit as listed above (now lines 639 and 758) seems to have gotten it working again, so no, I'm afraid this was not already fixed.

It also still won't do the level 2 part of the sorting (the order in category) unless the level 2 drop-down is enabled (showing). No change has to be made in that drop-down; order in category is what I've set as the default level 2 sorting criterion, and IF the level 2 drop-down is shown, it works. If it's hidden, it does not.

Neither issue seems to be addressed in 15, but at least the 1066 error still resolved with the edits I made above.

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

ggppdk
Offline
Administrator

8 years 11 months ago - 8 years 11 months ago #62065 by ggppdk

Replied by ggppdk on topic Search breaks when sorted by custom field (date)

Hello

in the new file there is no code like:

Code:

table AS fi

and no:

Code:

fi.

some of the files were not updated

Last edit: 8 years 11 months ago by ggppdk.

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

ggppdk
Offline
Administrator

8 years 11 months ago - 8 years 11 months ago #62066 by ggppdk

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

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