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

@SELECT binding to power a dropdown: how to allow for empty option?

    • 9207 ☆ A M B ☆
    • 2,475 Posts
    Everett Reply #1, 13 years, 8 months ago
    I've got a simple SELECT binding powering a list of TV input options so we can select a user:

    @SELECT `username`,`id` FROM `[[+PREFIX]]users` WHERE `active` = 1 ORDER BY username ASC


    The field is not required, and "Allow Blank" is set to "yes". The problem is that since a SQL query will always return values, there seems to be no way to really have a dropdown list that has its first option be blank (i.e. "-- none --"). Does anyone know a workaround? Is this a common enough use case to make a refactor request?

    The only thing I can think of would be to re-do this as an @EVAL binding and force the first option in the array to be empty, but I like the simplicity of the SQL query.

    MODX 2.2.6
      Hosting stuff: http://fireproofsocks.com/
      Coding stuff: http://craftsmancoding.com/
      Blog stuff: http://tipsfor.us/
      • 36416
      • 589 Posts
      Eoler Reply #2, 13 years, 8 months ago
      Quote from: Everettg_99 at Jan 30, 2013, 10:21 AM
      I've got a simple SELECT binding powering a list of TV input options so we can select a user:
      @SELECT `username`,`id` FROM `[[+PREFIX]]users` WHERE `active` = 1 ORDER BY username ASC


      The field is not required, and "Allow Blank" is set to "yes". The problem is that since a SQL query will always return values, there seems to be no way to really have a dropdown list that has its first option be blank (i.e. "-- none --"). Does anyone know a workaround? Is this a common enough use case to make a refactor request?

      A bit of (My)SQL saves the day:
      @SELECT '[none]' AS username, 0 AS id UNION ALL SELECT `username`,`id` FROM ...

        • 9207 ☆ A M B ☆
        • 2,475 Posts
        Everett Reply #3, 13 years, 8 months ago
        Genius. THANK YOU. With several years of writing MySQL queries, I've never used UNION ALL.
          Hosting stuff: http://fireproofsocks.com/
          Coding stuff: http://craftsmancoding.com/
          Blog stuff: http://tipsfor.us/
          • 36416
          • 589 Posts
          Eoler Reply #4, 13 years, 8 months ago
          Quote from: Everettg_99 at Jan 30, 2013, 01:23 PM
          Genius. THANK YOU. With several years of writing MySQL queries, I've never used UNION ALL.

          As a thread starter you have proper buttons... wink
            • 19604 ☆ A M B ☆
            • 179 Posts
            mindeffects Reply #5, 12 years, 7 months ago
            How would the statement be, if I wanted to show the "fullname" (modx_user_attributes) instead of the "username" (modx_users)? Tried some JOINS but somehow without success.

            Is there a way to show what is returnd? (The text-type TVs do not evaluate the "Input Option Values" field.)

            How can I influence, which value is getting stored?

            Thanks!
              MINDEFFECTS – DESIGN for PRINT, WEB and MEDIA
              http://twitter.com/mindeffects · http://www.facebook.com/mindeffects · http://www.youtube.com/mindeffects/ · skype://mindeffects_oliver
              • 4172
              • 5,888 Posts
              Bruno17 Reply #6, 12 years, 7 months ago
              If you have MIGX installed you can do it like that:

              @CHUNK getUserOptions


              your chunk getUserOptions:

              --none--==||[[migxLoopCollection? &where=`{"active":"1"}` &sortConfig=`[{"sortby":"Profile.fullname"}]` &classname=`modUser` &joins=`[{"alias":"Profile"}]` &tpl=`@CODE:[[+Profile_fullname]]==[[+id]]` &outputSeparator=`||`]]


              you could also use @EVAL - binding and doing the same with $modx->runSnippet.
                -------------------------------

                you can buy me a beer, if you like MIGX

                http://webcmsolutions.de/migx.html

                Thanks!