We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Answered getResource and mysql function in where condition

    • 38062
    • 6 Posts
    milance2009 Reply #1, 14 years, 9 months ago
    I'm looking the best way how to make the list of recent resources in sidebar that are created on the same month as viewed resource. I realised that this condition works 
     [[!getResources? &parents=`287` &where=`{"createdon:>": 10000}`
&sortby=`{"createdon":"DESC"}` ]]

    but this doesnt work
     [[!getResources? &parents=`287` &where=`{"month(createdon)": 1}`
&sortby=`{"createdon":"DESC"}` ]]

    I could even make snipet that makes timestamp for current month and year (is there easier way than that), but how could I pull just resources created just in january of any year for example?!

    This question has been answered by milance2009. See the first response.

      • 22303 MODX Staff
      • 10,725 Posts
      opengeek Reply #2, 14 years, 9 months ago
      Try this:
      [[!getResources? &parents=`287` &where=`["month(createdon) = 1"]`
&sortby=`{"createdon":"DESC"}` ]]


      In order to use SQL functions, you need a full expression; they will not work using the associative array approach where the key is the field alias.
        Jason Coward
        Chief Architect @ MODX
        http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
      • discuss.answer
        • 38062
        • 6 Posts
        milance2009 Reply #3, 14 years, 9 months ago
        Thanks, I'll try that.

        Update: For some (unknown) reason 
        &where=`["month(createdon) = 1"]`
        didnt work so I turned on loging in gerResource and realised that createdon is not datetime or timestamp column type in MySQL. It's integer type!!! So correct way to write where condition would be 
        &where=`["MONTH(FROM_UNIXTIME(createdon)) = 1"]`

        I would also like to share two tips.

        Tip #1 - Resize error log window

        Because I wanted larger error log window height I found out that you need to change these file(s)
        manager/assets/modext/build/widgets/system/modx.panel.error.log-min.js and add growMin:500 and set growMax to be bigger that growMin if you set compress manager js files or do same thing in manager/assets/modext/widgets/system/modx.panel.error.log.js.

        Tip #2 - Firebug extension for ExtJS

        Because I dont have any experience in Ext JS and I had to somehow find that textarea creating code so I used Firebug extension for ExtJS called Illuminations. [ed. note: milance2009 last edited this post 14 years, 9 months ago.]