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

Help with @SELECT statement in TV

    • 18608
    • 112 Posts
    Mathias Dannevang Reply #1, 13 years, 8 months ago
    Hi,

    I have a Listbox TV with the following @SELECT in the input options:
    @SELECT value,contentid FROM `modx_site_tmplvar_contentvalues` WHERE tmplvarid = 7

    It works perfectly in selecting specific resources from a tv value (sku number) and storing their ID in a list. My problem is that the site has now grown and has multiple context, and the same tv with the same content might occur in several context.

    Can any one help me to modify the above @select statement to make it select only from resources in the "web" context?

    Help will be highly appreciated smiley
      Mathias Dannevang | Webdesigner at dannevang.org | Tweets @dannevang
      • 14677
      • 54 Posts
      Creatable Design Reply #2, 13 years, 8 months ago
      You could try something like this:

      @SELECT value,contentid FROM `modx_site_tmplvar_contentvalues` JOIN `modx_site_content` ON contentid = id WHERE tmplvarid = 7 AND context_key = 'web'


      Not tested, but this would only join if that resource has the TV id of 7 and context key of web.
        • 18608
        • 112 Posts
        Mathias Dannevang Reply #3, 13 years, 8 months ago
        Tried testing it and the result is that the list is empty. If I run the query directly in phpMyAdmin it gives me the following error: "Column 'id' in on clause is ambiguous"
          Mathias Dannevang | Webdesigner at dannevang.org | Tweets @dannevang
          • 14677
          • 54 Posts
          Creatable Design Reply #4, 13 years, 8 months ago
          Try this:
          @SELECT TC.value,TC.contentid FROM `modx_site_tmplvar_contentvalues` TC JOIN `modx_site_content` SC ON TC.contentid = SC.id WHERE TC.tmplvarid = 7 AND SC.context_key = 'web'


          This error is because both tables have the field name of id... The above should infact fix it but again I am unable to test it at the moment.
            • 18608
            • 112 Posts
            Mathias Dannevang Reply #5, 13 years, 8 months ago
            It works! Thank you so much, that was brilliant!
              Mathias Dannevang | Webdesigner at dannevang.org | Tweets @dannevang
              • 14677
              • 54 Posts
              Creatable Design Reply #6, 13 years, 8 months ago
              Your welcome, no problem at all and glad I could help.