Orderby not working

  • Resolved netschmiede24

    (@netschmiede24)


    2 years, 1 month ago

    Hi,

    I have again a problem with finding the correct syntax for ordering the list of pods.

    I have a field in my pod called “monat-jahr”, which is of type date. I want the list of pods sorted by this field.

    I am using the Gutenberg Block for the output on the page. So in the field “Order by”, I tried the following options:

    monat-jahr DESC

t.monat-jahr DESC

DATE(monat-jahr.meta_value) DESC

    No matter what I try, the output is:

    Pods Embed Error: ORDER BY contains SQL that is not allowed.

    I then figured that I get the same error, even if I enter “t.post_title ASC” or “rand()” into that field. The output will only be displayed, when I leave the field blank (and then it is sorted by Post-Title, which I clearly do not want.

    What am I doing wrong?

    Thanks,
    Astrid

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Support pdclark

    (@pdclark)

    2 years, 1 month ago

    SQL Not Allowed

    See Pods Admin > Settings > Security

    Dynamic Features > New installs will now default to not allowing all SQL arguments to be used by dynamic features. Existing installs will default to only allowing simple SQL arguments. All SQL fragments are checked for disallowed usage like subqueries. This can be set in a new setting in Pods Admin > Settings > Security. 

    https://pods.io/2024/02/21/pods-3-1-feature-release-access-rights-revamp/

    Sorting by Date

    Date fields are represented as text. To sort as time, MySQL uses CAST(), not DATE(). See https://docs.pods.io/searching-filtering-sorting/sorting/sorting-date-fields-using-cast/

    CAST( monat-jahr.meta_value AS DATE ) DESC

    Accessing a Post Meta Value

    It will likely be monat-jahr.meta_value or monat_jahr.meta_value.

    Usual notation is an underscore, but it will be whatever is set as the field name (not the label).

    t. refers to the primary table being referenced by the query. e.g., wp_posts or similar primary tables for users or terms. So it would be for t.post_title or t.post_date, but t. would not be used for referencing a meta field, as these require JOINs which are generated by Pods dynamically. See https://codex.ww.wp.xz.cn/Database_Description

    Thread Starter netschmiede24

    (@netschmiede24)

    2 years, 1 month ago

    Thanks – I didn’t see the extra SQL security setting. That obviously fixed it.

    Astrid

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Orderby not working’ is closed to new replies.