Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Edit:</strong> the working solution ;)</p> <p>The thing is, I actually rushed into the wrong direction. I came up with the following idea, which uses a recursive function therefore making it much more easy to read.</p> <p>Indeed, you want to have all the combinations, without the permutations. Here I put only the first letter of a color. So, from:</p> <pre><code>b1 b2 g1 y1 y2 r1 r2 r3 A A B C C D D D </code></pre> <p>You want to have (the order here is what the code will do):</p> <pre><code>A B C D b1 g1 y1 r1 b1 g1 y1 r2 b1 g1 y1 r3 b1 g1 y2 r1 b1 g1 y2 r2 b1 g1 y2 r3 b2 g1 y1 r1 b2 g1 y1 r2 b2 g1 y1 r3 b2 g1 y2 r1 b2 g1 y2 r2 b2 g1 y2 r3 </code></pre> <p>First, I wanted another format for the column/color array, as it would be easier to work with. I wanted to go from</p> <pre><code>[b1,b2,g1,y1,y2,r1,r2,r3] [A ,A ,B ,C ,C ,D ,D ,D] </code></pre> <p>to</p> <pre><code>[[b1,b2],[g1],[y1,y2],[r1,r2,r3]] [ A , B , C , D ] </code></pre> <p>This way we would have, for each column value, a matching subarray containg all the colors related to the column.</p> <p>The following function achieves this purpose (even though it is probably not the optimal way to do it):</p> <pre><code>//column and color are the paired arrays you have as data input function arrangeArrays(column, color) { var ret=new Array(); //the returned container for the data ret["color"]=new Array(); //the color part of the returned data ret["column"]=new Array(); //the column part of the returned data var tmp=new Array(); //an internal array we'll use to switch from associative keys to normal keys //we parse the paired arrays for(var i in column) { //if the column is not an associative key in tmp, we declare it as an array if(!tmp[column[i]]) tmp[column[i]]=new Array(); //we add the color to the subarray matching its column tmp[column[i]].push(color[i]); } //now we just translate these horrible associative keys into cute array-standard integer keys for(var i in tmp) { ret["color"].push(tmp[i]); ret["column"].push(i); } //the commented code is a quick does-it-work block /* for(var i in ret["column"]) { document.write("column="+ret["column"][i]+" --- color(s)="+ret["color"][i].join()+"&lt;br&gt;"); } */ return ret; } </code></pre> <p>Now to the core. Each call to the function will process one column, and actually only a part of it, using recursion to deal with other columns. Using a simplified example, the code does this:</p> <pre><code>[[b1,b2],[y1,y2]] [ A , B ] total combinations: 2*2=4 first call for 1st column, length is 4, 2 possible values, first insert loops 4/2=2 times: b1 b1 call for 2nd column, length is 2, 2 possible values, first insert loops 2/2=1 time: b1 y1 b1 call for 3rd column, no 3rd column, coming back call for 2nd column, length is 2, 2 possible values, second insert loops 2/2=1 time: b1 y1 b1 y2 call for 3rd column, no 3rd column, coming back call for 2nd column done, coming back first call for 1st column, length is 4, 2 possible values, second insert loops 4/2=2 times: b1 y1 b1 y2 b2 b2 call for 2nd column, length is 2, 2 possible values, first insert loops 2/2=1 time: b1 y1 b1 y2 b2 y1 b2 call for 3rd column, no 3rd column, coming back call for 2nd column, length is 2, 2 possible values, second insert loops 2/2=1 time: b1 y1 b1 y2 b2 y1 b2 y2 call for 3rd column, no 3rd column, coming back call for 2nd column done, coming back call for 1st column done, coming back (returning) </code></pre> <p>Here is the code, help yourself reading the comments ;)</p> <pre><code>//results is an empty array, it is used internally to pass the processed data through recursive calls //column and color would be the subarrays indexed by "column" and "color" from the data received by arrangeArrays() //resultIndex is zero, it is used for recursive calls, to know where we start inserting data in results //length is the total of results expected; in our example, we have 2 blues, 1 green, 2 yellows, and 3 reds: the total number of combinations will be 2*1*2*3, it's 12 //resourceIndex is zero, used for recursive calls, to know what column we are to insert in results function go(results, column, color, resultIndex, length, resourceIndex) { //this case stops the recursion, it means the current call tries to exceed the length of the resource arrays; so we just return the data without touching it if(resourceIndex&gt;=column.length) return results; //we loop on every color mentioned in a column for(var i=0;i&lt;color[resourceIndex].length;i++) { //so for every color, we now insert it as many times as needed, which is the length parameter divided by the possible values for this column for(var j=0;j&lt;length/color[resourceIndex].length;j++) { //ci will be the index of the final array //it has an offset due to recursion (resultIndex) //each step is represented by j //we have to jump "packs" of steps because of the loop containing this one, which is represented by i*(the maximum j can reach) var ci=resultIndex+i*(length/color[resourceIndex].length)+j; //the first time we use ci, we have to declare results[ci] as an array if(!results[ci]) results[ci]=new Array(); //this is it, we insert the color into its matching column, and this in all the indexes specified through the length parameter results[ci][column[resourceIndex]]=color[resourceIndex][i]; } //end of j-loop //we call recursion now for the columns after this one and on the indexes of results we started to build results=go(results, column, color, resultIndex+i*(length/color[resourceIndex].length), length/color[resourceIndex].length, resourceIndex+1); } //end of i-loop //we now pass the data back to the previous call (or the script if it was the first call) return results; } </code></pre> <p>I tested the function using this bit of code (might be useful if you want to experience what happens on each step):</p> <pre><code>function parseResults(res) { for(x in res) { //x is an index of the array (integer) for(var y in res[x]) { //y is a column name document.write(x+" : "+y+" = "+res[x][y]); //res[x][y] is a color document.write("&lt;br&gt;"); } document.write("&lt;br&gt;"); } } </code></pre> <p>You'll probably also need a function to tell <code>go()</code> what length to use for the first call. Like this one:</p> <pre><code>function getLength(color) { var r=1; for(var i in color) r*=color[i].length; return r; } </code></pre> <p>Now, assuming you have an array <code>column</code> and an array <code>color</code>:</p> <pre><code>var arrangedArrays=arrangeArrays(column, color); var res=go(new Array(), arrangedArrays["column"], arrangedArrays["color"], 0, getLength(arrangedArrays["color"]), 0); </code></pre> <p>Here it is, seems big but I wanted to explain well, and anyway if you remove the comments in the code and the examples, it's not that big... The whole thing is just about not going crazy with these indexes ;)</p> <p><strong>This below was the first answer</strong>, which didn't work well... which, well, didn't work.</p> <p>You can use "associative arrays" (or <code>eval</code> an object/property-like syntax, it's about the same). Something like that:</p> <pre><code>var arrayResult=new Array(); var resultLength=0; for(var globalCounter=0;globalCounter&lt;arrayColumn.length;globalCounter++) { //if this subarray hasn't been init'd yet, we do it first if(!arrayResult[resultLength]) arrayResult[resultLength]=new Array(); //case: we already inserted a color for the current column name if(arrayResult[resultLength][arrayColumn[globalCounter]]) { //1: we copy the current subarray of arrayResult to a new one resultLength++; arrayResult[resultLength]=new Array(); for(var i=0;i&lt;=globalCounter;i++) arrayResult[resultLength][arrayColumn[i]]=arrayResult[resultLength-1][arrayColumn[i]]; //2: we replace the value for the conflicting colmun arrayResult[resultLength][arrayColumn[globalCounter]]=arrayColor[globalCounter]; //case: default, the column wasn't already inserted } else { //we simply add the column to each subarray of arrayResult for(var i=0;i&lt;=resultLength;i++) arrayResult[i][arrayColumn[globalCounter]]=arrayColor[globalCounter]; } } </code></pre> <p>From there, I suppose it's easy to translate it to JSON format.</p> <p>When you parse the subarrays, keep in mind that <strong>associative keys are actually methods</strong>, i.e. you can't loop with <code>for(x=0;x&lt;array.length;x++)</code>, instead use <code>for(x in array)</code> ; and you have to test the key and be sure it starts with "column" before processing it (or you'll end processing the pair "length"/0 ^^). Same on arrayResult's keys if you want this container array to have that kind of keys.</p> <p>One last thing: I didn't test the code, it may miss a bit, or there may be some mistyping. The purpose is to help, not to do :)</p> <p>Good luck!</p>
    singulars
    1. This table or related slice is empty.
    1. POjquery array concatenation of specific combinations except where duplicate
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USStock Overflaw
    1. USStock Overflaw
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POjquery array concatenation of specific combinations except where duplicate
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COA more generic way of avoiding picking up the `length` pair would be to use [the `hasOwnProperty` method](http://bonsaiden.github.com/JavaScript-Garden/#object.hasownproperty) to avoid seeing properties on the prototype chain.
      singulars
      1. PO
      1. USGregL
    2. CO@GregL W3Schools [doesn't know this method](http://www.google.com/search?q=site:w3schools.com+hasownproperty), so I guess it's not really standard. Anyway, according to your link's description, I doubt you can use it to differentiate `.length` from literal keys, as `.length` is - as are keys - a method defined directly in our object (instantiated from the Array class), not in one of its superclasses... So `.hasOwnProperty` will return true for `.length` _and_ keys :s Though it may be useful for testing the presence of a key in result's subarrays.
      singulars
      1. PO
      1. USStock Overflaw
    3. CO@StockOverflaw Thanks for the starting point. Your code does work for the example I provided! I created a jsfiddle [link](http://jsfiddle.net/hAfbJ/). Unfortunately, it gets stuck in an infinite loop in two cases. Case#1, if ColumnA has two cases instead of ColumnD (ie order matters). Case#2, any number of combinations, so there might be two pairs for ColumnA and two pairs for ColumnD in the same array. I.e order and the number of pairs is flexible/unknown. With those 2 cases am I trying to do the impossible?
      singulars
      1. PO
      1. USAriesto
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload