Using a reactive javascript sql result in a mosaic plot

[oscaro00]

Within the starter Framework app with the launches dataset, I am trying to create a reactive plot based on user's filter selection. The following logic works to filter and display the table.

const test_query = sql([`
SELECT * FROM launches
  WHERE state IN ('--'${", ?".repeat(selectCountryTest.length)})
`], ...selectCountryTest);

Inputs.table(test_query)

However, the following code throws an error:

vg.plot(
  vg.barX(
    vg.from(test_query),
    {x: vg.count(), y: "family", fill: "#ccc", fillOpacity: 0.2}
  ),
  vg.barX(
    vg.from(test_query, {filterBy: $click}),
    {x: vg.count(), y: "family", fill: "#ccc"}
  ),
  vg.toggleY({as: $click}),
  vg.highlight({by: $click}),
  vg.xDomain(vg.Fixed),
  vg.yDomain($domain),
  vg.yLabel(null),
  vg.colorDomain($domain),
  vg.colorRange($colors),
  vg.marginLeft(45),
  vg.width(660)
)

If I replace, test_query with "launches" in the above code block, then the plot renders, but doesn't react to a user's selection.

The browser debugger reports the following error: "Uncaught (in promise) RuntimeError: index out of bounds"

Is test_query not the right type for the Mosaic plot function?

[Fil]

The arguments of vg.from are strings indicating names of tables in the backing DuckDB instance. Not the data itself.
https://idl.uw.edu/mosaic/api/sql/queries.html#from

[oscaro00]

---

theme: dashboard
title: Dropdown Test
toc: false
sql:
launches: ./data/launches.csv
style: style.css

Dropdown Test

function dropdownInput(config = {}) {

  //set up configuration
  let {
    inputId, 
    inputLabel,
    placeholderText,
    options, 
    selected,
  } = config;


  const width = '250px'

  const maxHeight = '250px'

  //dropdown  button
  const dropdownButton = Inputs.button(html`<div class='button-inner' style='display:flex;justify-content:space-between;height:100%;' id='dropdown-${inputId}'><div class='filter-selected' style='text-align:left;'>${placeholderText}</div><span style='font-size:10px;padding-left:5px;display:flex;align-items:center;'><i class="fas fa-chevron-down"></i></span></div>`);
  dropdownButton.classList.add("dropdown-button");

  const  optionValues = options.map(option => option.value)

  //selectInput
  const selectInput = Inputs.select(optionValues, {multiple:true, value:selected});
  selectInput.classList.add("dropdown-select");
  selectInput.style.display="none";

  //select and deselect buttons
  const selectDeselectButtons = Inputs.button([["Select All", value=> "Select All"],["Deselect All", value => "Deselect All"]]);
  selectDeselectButtons.classList.add("dropdown-action-buttons")
  
  let selectInputValue = selected.slice(); // Create a copy of selected for selectInputValue

  let optionListItems = options.map(option => {
    const isSelected = selectInputValue.includes(option.value);
    const listItem = html`<li id='${option.value}'><a style='cursor:pointer;'><span class='text'>${option.label}</span><span class='checkmark'><i class="fas fa-check"></i></span></a></li>`;

    
    // Event listener for the list item
    listItem.addEventListener('click', (event) => {
      event.preventDefault();
      
      const clickedListItem = event.currentTarget;
      const clickedItemId = clickedListItem.id;
      const index = selectInputValue.indexOf(clickedItemId);

      if (clickedListItem.classList.contains('selected')) {
        clickedListItem.classList.remove('selected');
        
        if (index !== -1) {
          selectInputValue.splice(index, 1);
        }
      } else {

        clickedListItem.classList.add('selected');
        selectInputValue.push(option.value);
      }

      // Update selectInput value
      selectInput.value = selectInputValue;
      
      // Update form value with selectInputValue
      form.value = selectInputValue;
      form.dispatchEvent(new CustomEvent("input", { bubbles: true }));
    });

    return listItem;
  });

  const optionList = html`<ul class='dropdown-list'>${optionListItems}</ul>`

  const innerDropdown = html`<div class='dropdown-action-buttons-container'>${selectDeselectButtons}</div>${selectInput} <div class='dropdown-list-container' style='max-height:${maxHeight};overflow-y:scroll;'>${optionList}</div>`
  
  innerDropdown.classList.add("dropdown-inner")
  innerDropdown.style.display = "none";
  innerDropdown.style.width = width;

   // Function to toggle dropdown visibility
  const toggleDropdown = () => {
    innerDropdown.style.display = innerDropdown.style.display === "none" ? "block" : "none";
  };

  // Toggle the visibility of the select input on button click
  dropdownButton.onclick = (event) => {
    toggleDropdown();
  };

    optionListItems.forEach((t, index) => {
  const option = options[index].value;
  if (selectInputValue.includes(option)) {
    t.classList.add('selected');
  }
});
  
selectDeselectButtons.onclick = () => {
  const buttonText = selectDeselectButtons.value;
  if (buttonText === "Select All") {
    selectInput.value = options.map(option => option.value);
    selectInputValue = options.map(option => option.value);
    form.value = selectInputValue;

    optionListItems.forEach(t => {
        t.classList.add('selected');
    });
  } else if (buttonText === "Deselect All") {
    selectInput.value = [];
    selectInputValue = [];
    form.value = [];

    optionListItems.forEach(t => {
        t.classList.remove('selected');
    });
  }

  form.dispatchEvent(new CustomEvent("input", { bubbles: true }));
};

//modified from inputs formula by https://observablehq.com/@jashkenas/inputs
selectInput.onchange = e => {
  e && e.preventDefault();
  const value = selectInput.value; 
  if (form.output) {
    const out = value;
    if (out instanceof window.Element) {
      while (form.output.hasChildNodes()) {
        form.output.removeChild(form.output.lastChild);
      }
      form.output.append(out);
    } else {
      form.output.value = out;
    }
  }
  // Update form.value with the selectInput value
  form.value = value;
  form.dispatchEvent(new CustomEvent("input", { bubbles: true }));
};
  
  const form = html`<label style='font-weight:bold;font-size:14px;'>${inputLabel}</label><form style='width:250px' id=${inputId}>${dropdownButton} ${innerDropdown}</form>`

  form.classList.add("dropdown-form");

  form.value = selected;



const updatePlaceholderText = () => {
  const selectedValues = form.value;
  if (selectedValues.length > 0) {
    const filteredLabels = options
      .filter(option => selectedValues.includes(option.value))
      .map(filteredOption => {
        // Check if the label is an HTML element
        if (typeof filteredOption.label === 'object' && filteredOption.label instanceof HTMLElement) {
          return filteredOption.label.outerHTML;
        } else {
          return filteredOption.label; // Assuming label is a string if not HTML
        }
      });
      
    const concatenatedText = filteredLabels.join(', ');
    dropdownButton.querySelector('.filter-selected').innerHTML = `${concatenatedText}`;
  } else {
    dropdownButton.querySelector('.filter-selected').innerHTML = placeholderText;
  }
};

  // ... (existing code)

  // Listen for changes in form.value
  form.addEventListener('input', updatePlaceholderText);


  // Initial update of placeholder text
  updatePlaceholderText();
  
  return form;
}

const selectCountryTest = view(dropdownInput({
  inputLabel: "Country",
  inputId: "countryTest",
  placeholderText: 'Select Countries...',
  options: [{value:'United States', label:'United States'},
            {value:'Soviet Union', label: 'Soviet Union'}], // need to fix so these are not hard coded
  selected: ['United States']
}));

display(selectCountryTest)

const test_query = sql([`
SELECT * FROM launches
  WHERE state IN ('--'${", ?".repeat(selectCountryTest.length)})
`], ...selectCountryTest);

Inputs.table(test_query)

const $click = vg.Selection.single();
// want to avoid hard coding these too
const $domain = vg.Param.array(["R-7", "Other", "Thor", "Atlas", "Titan", "R-14", "Proton", "R-36", "DF5", "Zenit", "Falcon9"]);
const $colors = vg.Param.array(["ffe74c","ff5964","ffffff","38618c","35a7ff", "454545","6d5959","9dcbba","abebd2","04f06a", "8cada7"]);
// const $range = vg.Selection.intersect();

// plot with the original data, so not reactive to dropdown input
vg.plot(
  vg.barX(
    vg.from("launches"),
    {x: vg.count(), y: "family", fill: "#ccc", fillOpacity: 0.2}
  ),
  vg.barX(
    vg.from("launches", {filterBy: $click}),
    {x: vg.count(), y: "family", fill: "#ccc"}
  ),
  vg.toggleY({as: $click}),
  vg.highlight({by: $click}),
  vg.xDomain(vg.Fixed),
  vg.yDomain($domain),
  vg.yLabel(null),
  vg.colorDomain($domain),
  vg.colorRange($colors),
  vg.marginLeft(45),
  vg.width(660)
)

const db = DuckDBClient.of({interactive_tbl: test_query});

// plot not working because it can't find the table
vg.plot(
  vg.barX(
    vg.from("interactive_tbl"), // also tried "db.interactive_tbl"
    {x: vg.count(), y: "family", fill: "#ccc", fillOpacity: 0.2}
  ),
  vg.barX(
    vg.from("interactive_tbl", {filterBy: $click}), // also tried "db.interactive_tbl"
    {x: vg.count(), y: "family", fill: "#ccc"}
  ),
  vg.toggleY({as: $click}),
  vg.highlight({by: $click}),
  vg.xDomain(vg.Fixed),
  vg.yDomain($domain),
  vg.yLabel(null),
  vg.colorDomain($domain),
  vg.colorRange($colors),
  vg.marginLeft(45),
  vg.width(660)
)

[Fil]

Ooof! You should try and reduce this bit by bit until you find what's breaking. https://stackoverflow.com/help/minimal-reproducible-example

[oscaro00]

My fault. I should have provided a lot more context. My issue is passing my dynamically filtered dataset result into a Mosaic plot.

Here is a simplified version:

// this assumes the launches dataset is defined in the page front matter
// this is normally dynamically filtered by user input, but it is simplified because that component is working
const test_query = sql([`
SELECT * FROM launches
  WHERE state IN ('United States')
`]);

// testing the above query works even when passing dynamic input to the query above
Inputs.table(test_query)

// this code doesn't raise any errors
const db = DuckDBClient.of({interactive_tbl: test_query});

// defining some helpers for the below Mosaic plot
const $click = vg.Selection.single();
// want to avoid hard coding these too
const $domain = vg.Param.array(["R-7", "Other", "Thor", "Atlas", "Titan", "R-14", "Proton", "R-36", "DF5", "Zenit", "Falcon9"]);
const $colors = vg.Param.array(["ffe74c","ff5964","ffffff","38618c","35a7ff", "454545","6d5959","9dcbba","abebd2","04f06a", "8cada7"]);

// plot not working because it can't find the table
vg.plot(
  vg.barX(
    vg.from("interactive_tbl"), // also tried "db.interactive_tbl" unsuccessfully, but works with "launches"
    {x: vg.count(), y: "family", fill: "#ccc", fillOpacity: 0.2}
  ),
  vg.barX(
    vg.from("interactive_tbl", {filterBy: $click}), // also tried "db.interactive_tbl" unsuccessfully, but works with "launches"
    {x: vg.count(), y: "family", fill: "#ccc"}
  ),
  vg.toggleY({as: $click}),
  vg.highlight({by: $click}),
  vg.xDomain(vg.Fixed),
  vg.yDomain($domain),
  vg.yLabel(null),
  vg.colorDomain($domain),
  vg.colorRange($colors),
  vg.marginLeft(45),
  vg.width(660)
)

[Fil]

OK I see it now. That problem is discussed here and @mbostock shows a solution
#1598 (comment)

Adapting Mike's solution to the launches example:

```js echo
import * as vgplot from "npm:@uwdata/vgplot";

const db = await DuckDBClient.of({launches: FileAttachment("data/launches.csv")});
const sql = db.sql.bind(db);

const coordinator = new vgplot.Coordinator();
const vg = vgplot.createAPIContext({coordinator});
coordinator.databaseConnector(vgplot.wasmConnector({duckdb: db._db}));

const test_query = sql([`
CREATE TABLE interactive_tbl AS (SELECT * FROM launches
  WHERE state IN ('United States'))
`]);
```

However it's not going to be fully reactive. If 'United States' is selected with an input widget, you probably have to manage it from within Mosaic.

[oscaro00]

I appreciate the answer. When you say manage it from within Mosaic, do you mean using Mosaic sql?

I guess I incorrectly assumed DuckDB in Observable Framework would work nicely with DuckDB in Mosaic. Ideally, I was imagining a situation where interactive input widgets could filter data from the front matter in a sql block or a javascript sql() function. Then, that resulting filtered table would be passed to Mosaic to keep the sql query logic separate from the plotting logic.

[Fil]

duplicate of uwdata/mosaic#559