Relation does not exist for custom Queries on Charts

Help me!
1

Hi,

We are having issues with charts that are using custom Queries on our forest.

Actual behavior

All charts using Simple query work, but charts using the custom Query don’t.

We get an error that the relation doesn’t exist. But the exact same relation can be queried with Simple query and it works.

Expected behavior

Custom queries should work as expected

Failure Logs

Screen Shot 2021-01-15 at 9.57.10 AM
Screen Shot 2021-01-15 at 9.57.10 AM1671×328 39.9 KB

Context

We are in a multi-database setup. Not sure if this can affect the way custom Query should be written

2

Hello @Esteban,

Thanks for your message.

Could you tell us what chart type is it?
And could you give us your full query?

It’d help us what’s going on here :wink:

Thanks!

3

Here is the query

Please note it also happens on other tables. Pretty much all tables with custom queries don’t work, all with Simple work.

SELECT payments.billing_cycle AS key, SUM(payments.payment_amount + payments.enterprise_subsidy) as value
FROM payments
WHERE payments.billing_cycle is not null and payments.account_id is not null
GROUP BY key
ORDER BY key;

And full screenshot

Screen Shot 2021-01-15 at 11.24.08 AM
Screen Shot 2021-01-15 at 11.24.08 AM659×819 56.7 KB

And our model

'use strict'

module.exports = (sequelize, DataTypes) => {
  var Model = sequelize.define(
    'payments',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      user_id: {
        type: DataTypes.INTEGER
      },
      enterprise_id: {
        type: DataTypes.INTEGER
      },
      payment_amount: {
        type: DataTypes.FLOAT
      },
      status: {
        type: DataTypes.ENUM(
          'awaitingData',
          'due',
          'completed',
          'failed',
          'refunded'
        )
      },
      stripe_data: {
        type: DataTypes.JSON
      },
      subscription_id: {
        type: DataTypes.INTEGER
      },
      total_cost: {
        type: DataTypes.FLOAT
      },
      enterprise_subsidy: {
        type: DataTypes.FLOAT
      },
      enterprise_max_subsidy: {
        type: DataTypes.FLOAT
      },
      date_processed: {
        type: DataTypes.DATE
      },
      received: {
        type: DataTypes.BOOLEAN
      },
      amount_received: {
        type: DataTypes.FLOAT
      },
      date_received: {
        type: DataTypes.DATE
      },
      billing_cycle: {
        type: DataTypes.STRING
      },
      created_at: {
        type: DataTypes.DATE
      },
      updated_at: {
        type: DataTypes.DATE
      },
      additional_fees: {
        type: DataTypes.FLOAT
      }
    },
    {
      tableName: 'payments',
      underscored: true
    }
  )

  Model.associate = function(models) {
    Model.belongsTo(models.subscriptions, {
      foreignKey: 'subscription_id',
      as: 'subscriptions'
    })
  }

  return Model
}
4

Hello @Esteban,

There is currently a limitation with the multi-db setup. Smart Queries are only possible on collections from the first / initial database connection.

Can you please check if you get this issue only with collections from your second database? Or on all collections?

Thank you

5

Hello!

Is there any news on this? I’m using two databases and can confirm that only the first declared one can be used in the SQL queries for the query charts.