9 Replies Latest reply on Apr 28, 2009 2:56 AM by Richard_Abbott

    Equivalent of select distinct

    Richard_Abbott Level 3

      Hi all,

      I have a dataset brought into my Flex app by a server-side database call as XML. I then need to use this same dataset in a couple of different places, eg in a grid, as chart data, in a combo etc. So far no problem.

      Now, for the combo entries what I would like to do is the equivalent of SQL "select distinct field_name from table_name", but applied directly to the XML I have brought back already. Is there such a beast in the XML-handling methods? I have found a work-around which sets up an Array, then cycles round the XML nodes and either adds the relevant field value as the next entry or skips it accordingly. But this seems unduly manual and (I suspect) will not be very snappy as the data set grows. Just now I am working with a fairly small test set but that will change.

      Any suggestions?

      Thanks in advance,

      Richard

        • 1. Re: Equivalent of select distinct
          Michael Borbor Level 4

          You could use E4X to do that without looping over it.

          • 2. Re: Equivalent of select distinct
            Barna Biro Level 3

            Hi Richard,

             

            You can't do the same kind of search in an XML object as you'd do with a database with a query. But, if your data is structured well enough then you can use the new features AS 3.0 brought in the game with the introduction of e4x ( or, you could try searching trough an Array as you already tried but I think that the e4x way is much cleaner ). Take a look at these links ( especially the second one ):

             

            http://livedocs.adobe.com/flex/3/langref/XMLList.html

            http://www.trilemetry.com/clients/f3gse/partIII/partIIIa/workingWithData_e4x.cfm#

             

            Best regards,

            Barna Biro

            • 3. Re: Equivalent of select distinct
              Richard_Abbott Level 3

              Thanks Michael and Barna, but this doesn't really address the problem. The data is well structured and I am using E4X methods. But the examples don't (I think) meet the need. Take the trilemetry example. In the XML file we have two individuals named "John". The requirement is to come up with a reduced list of unique elements so including Bob, John, Jane, Art, Brad... ie John only the once as we already have that name in. Clearly in this case not much of a reduction but where I am generating a selection box where the categorisation is much more common it is essential. Take Fisher's Iris data - you have 150 rows consisting of 50 each of three kinds of iris. Obviously in the drop-down selector you just want 3 names not 150. I think it would be much neater if I could just pull them out with an E4X-style query, but so far I haven't found a method for this, hence the work-around of manually looping and testing.

              Richard

              • 4. Re: Equivalent of select distinct
                Barna Biro Level 3

                Let me see if I got it right. So, lets say you have 3 Fishers, 1 Bob and 2 Johns in your XML structure. You want to popoulate the drop down list so it will only show Fisher, Bob, John and once you click on one of the names then it would somewhere display all the Fishers or all the Bobs or all the Johns? Am I correct or am I on the wrong road?

                 

                In case I'm correct then I can only think of one way to do this.. You need to loop trough your entire XML structure, node by node and store each family name ( or whatever you want to store ) in an ArrayCollection, BUT, when you are looping, you should also check if the current name does not already exist in the "namesArray"; if it exists then you don't add it, if it doesn't, then simply add it to the array. Then, you can populate your drop down list with this "namesArray" that holds only unique names. Once you click on a name, you would loop trough the XML structure again ( with e4x or whatever you prefer ) and this time, only retrieve the names that math the name you selected from the drop down list.

                 

                I know of no easier way of doing this. In case this wasn't what you meant then excuse me but English is not my first language and I might have understood something else than what you were trying to say.

                 

                Best regards,

                Barna Biro

                1 person found this helpful
                • 5. Re: Equivalent of select distinct
                  Michael Borbor Level 4

                  You're welcome I think that so far there isn't a method like that 

                  probably an enhance request. In that case you'll need to traverse the 

                  XML and add the items to another XMLList or an AC.

                   

                  Sincerely,

                   

                  Michael

                   

                  El 27/04/2009, a las 8:51, Richard_Abbott <forums@adobe.com> escribió:

                   

                  >

                  Thanks Michael and Barna, but this doesn't really address the 

                  problem. The data is well structured and I am using E4X methods. But 

                  the examples don't (I think) meet the need. Take the trilemetry 

                  example. In the XML file we have two individuals named "John". The 

                  requirement is to come up with a reduced list of unique elements so 

                  including Bob, John, Jane, Art, Brad... ie John only the once as we 

                  already have that name in. Clearly in this case not much of a 

                  reduction but where I am generating a selection box where the 

                  categorisation is much more common it is essential. Take Fisher's 

                  Iris data - you have 150 rows consisting of 50 each of three kinds 

                  of iris. Obviously in the drop-down selector you just want 3 names 

                  not 150. I think it would be much neater if I could just pull them 

                  out with an E4X-style query, but so far I haven't found a method for 

                  this, hence the work-around of manually looping and testing.

                  Richard

                  >

                  1 person found this helpful
                  • 6. Re: Equivalent of select distinct
                    Richard_Abbott Level 3

                    Michael, Barna,

                    I think we have all arrived at the same conclusion! Yes Barna, your understanding is correct of the problem, so I think the work-around is the only way to solve the problem. Pity but with all three of us ending at the same place it seems pretty conclusive.

                    Thanks again for your help,

                    Richard

                    • 7. Re: Equivalent of select distinct
                      ntsiii Level 3

                      One tweak, instead of using Array or Array collection use and object to hold the unique entries.  "Object" is sometimes called an "associateive array" or a hashtable.  It is optimized for rapid access and will be faster than examining an array.

                       

                      var xlcUnique:XMLListCollection = new XMLListCollection();

                      var oUnique:Object = new Object();

                      var sValue:String;

                      var myXMLList:XMLList = myXML.(myE4xExpression);     //get the non-unique list

                      for (xml in myXMLList) {                                                  //loop over it

                           sValue = xml.myValue.text()                                      //get the value 

                           if (oUnique[sValue] == null)  {                                     //is the value in the unique list?

                                oUnique[sValue] = true;                                        //if not, add it to the unique list

                                xlcUnique.addItem(xml);                                        //and add the xml node to the unique collection

                           }

                      }

                       

                      Tracy Spratt

                      • 8. Re: Equivalent of select distinct
                        ntsiii Level 3

                        Hmm. XMLList has the "contains" property.

                         

                        I can't speak to the relative efficiency of a hashtable vs "contains" , but I bet they are comparable.  And using list.contains is a lot simpler to code.

                         

                        I developed the hashtable approach before e4x, maybe I need to change.

                         

                        Just don't loop over the unique control collection, that would be the worst.

                         

                        Tracy

                        • 9. Re: Equivalent of select distinct
                          Richard_Abbott Level 3

                          Hi Tracy,

                          thanks for these two suggestions, I'll have a look to see which works best in my situation,

                          Richard