Joining queries

#1

Ok I have this situation where I need to obtain some values from a query and then use them to finally render a visual. I’m talking specifically about getting some IDs from one table and then use them to get the data based in these IDs from another table. In other words, a classic sql JOIN.

I guess I should obtain the values from the first query (not so sure on how to do it) and then create filters with the obtained data to finally pass them to my visualization.

My question is: Is there a way to do this natively from CF? If not, how can I get the data obtained in the first query?

#2

Yes of course. ChartFactor allows multiple queries as explained here.

Basically you can concatenate several queries in one single visualization or Aktive instance. You can then format data and filters obtained from one query and pass it down to the next one.

#3

Hi, this is the same mechanism used by the Network and Graph charts right https://chartfactor.com/doc/1.12/visualizations/network_chart/#creating-a-graph-chart-with-provider-data. We can concatenate more than two queries on the same multi queries definition?. Thanks.

#4

hi @jhon, yes is the same mechanism and you can concatenate as many queries as you need, you have only to remember that you need to provide different names to the queries and set the keep flag to true if you want to persist the results. Let me know if something does not work for you. Best regards.

#5

Can I also use several providers as well with these multiple queries? I have a some test data in a local ES server but the actual data is in a remote server. I can always bring the data locally but that would save some work, at least for the tests.

#6

Of course, you can use different providers and different sources as long as the data is somehow related. Providers can even be from different types, ie: Elastic and BigQuery.

#7

Ok, I think this is the best thread to ask. I’ve dealt with multiple queries and they are really helpful on how they simplify things. However there’s one thing I’ve come across recently:

I had a multiquery visual:

const visual = cf… (multiquery AQL)

Then at some point after the execution, I have to set some filters to it:

visual.set(filters)

However, I realized by the result obtained after executing with the new set of filters, that just the second query was containing the filters. The first query was executed without any.

Is this intended? How can I also pass the filters to the first query?

#8

Hi @keith, when you mention that you had to set some filters, are you using the Interaction Manager component? Or are you setting those filters programmatically?

#9

Well, programmatically for now. But if I use the Interaction Manager will it be differently?

#10

In the current version of ChartFactor, if you have a multi-query visualization, you will have to manually update all queries except for the last one. The way to do this is by obtaining the AQLs by its id and then set the filters like this:

const multiq = cf.create('query-1')
                          .provider('prov')
                          ....
                          .create('query-2')
                          ...
                          .create('query-3-last')

const newFilters = [cf.Filter(), cf.Filter()]

multiq.getAQLConfig('query-1').filters(newFilters)
multiq.getAQLConfig('query-2').filters(newFilters)

If you are using the Interaction Manager, you should use the filter:before-add and filter:before-remove events, so you can get the latest filters and update the queries.

Next version will address this issue.