Show specific columns with dataloader-sequelize in nested tables

Question

Currently I have some models. I'm using graphql with dataloader-sequelize and it works fine as long as I show associated tables without third level.

My models:

"articulo.js"

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Articulo = sequelize.define(
    'articulos', 
    {
      art_codigo: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        unique: true,
        autoIncrement: true
      },
      art_nombre: DataTypes.STRING(255),
      art_longitud: DataTypes.STRING(250),
      art_latitud: DataTypes.STRING(250),
      .....[more columns]
      art_contenido: DataTypes.TEXT,
    }, 
    {
      timestamps: false,    
      freezeTableName: true,
      name: {
        singular: 'Articulo',
        plural: 'Articulos',
      },
      indexes: [
        {
          unique: true,
          fields: ['art_codigo'],
        },
      ],
    }
  );

  Articulo.associate = (models) => {
    Articulo.belongsTo(models.canalizados, 
      {
          foreignKey: 'art_canalizado',
          as:"Canalizado",
      }
    );

    Articulo.belongsTo(
      models.articulos_tipos, 
      {
          foreignKey: 'art_tipo' 
      }
    );

  };
  return Articulo;
};

articulo_tipo.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const ArticuloTipo = sequelize.define('articulos_tipos', {
    ari_codigo: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      unique: true,
      autoIncrement: true
        },
    ari_nombre: DataTypes.STRING(255),
    }, {
      timestamps: false,
      freezeTableName: true,
      name: {
        singular: 'ArticuloTipo',
        plural: 'ArticulosTipos',
      },
      indexes: [
        {
          unique: true,
          fields: ['ari_codigo'],
        },
      ],
    });

  ArticuloTipo.associate = (models) => {
    ArticuloTipo.hasMany(models.articulos)
  };

  return ArticuloTipo;
};

canalizado.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Canalizado = sequelize.define('canalizados', {
    cnl_codigo: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      unique: true,
      autoIncrement: true
        },
    cnl_fecha_alta: DataTypes.DATE,
    ...... [more columns]
    cnl_revisado: DataTypes.BOOLEAN,
    }, {
      timestamps: false,
      freezeTableName: true,
      name: {
        singular: 'Canalizado',
        plural: 'Canalizados',
      },
      indexes: [
        {
          unique: true,
          fields: ['cnl_codigo'],
        },
      ],
    }
  );

  Canalizado.associate = (models) => {    
    Canalizado.hasMany(models.articulos);

    Canalizado.belongsTo(
      models.canalizados_tipos, 
      {
          foreignKey: 'cnl_tipo',
      }
    );

  };

  return Canalizado;
};

canalizado_tipo.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const CanalizadoTipo = sequelize.define('canalizados_tipos', {
    cai_codigo: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      unique: true,
      autoIncrement: true
        },
    cai_nombre: DataTypes.STRING(50)
    }, {
      timestamps: false,
      freezeTableName: true,
      tableName: "canalizados_tipos",
      name: {
        singular: 'CanalizadoTipo',
        plural: 'CanalizadosTipo',
      },
      indexes: [
        {
          unique: true,
          fields: ['cai_codigo'],
        },
      ],
    });

  CanalizadoTipo.associate = (models) => {
    CanalizadoTipo.hasMany(models.canalizados) 
  };

  return CanalizadoTipo;
};

My resolvers:

articulo.js

const Sequelize = require('sequelize');
const {detectarCampos} = require('../_extra/comunes'); //Optimize which columns you want to use in graphql
const Op = Sequelize.Op;

const resolvers = {
    Articulo:{
        art_tipo: (parent, args, { models, options }, info) => {
           return parent.getArticuloTipo(options); //It's an internal getter from sequelize, isn't it?
        },
        art_canalizado: (parent, args, { models, options }, info) => {
            return parent.getCanalizado(options); //It's an internal getter from sequelize, isn't it?
        },
    },
    Query: {
        async getArticulo(root, { codigo }, { models }, info) {
            return models.articulos.findByPk(
                codigo,
                {attributes: detectarCampos(info),}
            );
        },
        async getArticulos(root, { nombre, tipo}, { models, options }, info) {
            var whereStatement = {};
            if(nombre){
                whereStatement.art_nombre = {[Op.like]: '%' + nombre + '%'};
            }
            if (tipo){
                whereStatement.art_tipo = tipo;
            }

            return models.articulos.findAll({
                attributes: detectarCampos(info),
                where: whereStatement,
                //limit: 10, 
                options
            });
        },
        async getAllArticulos(root, args, { models }, info) {
            return models.articulos.findAll( {
                attributes: detectarCampos(info),
                limit: 10, 
            });
        },
    },
    Mutation: {
    },
}

module.exports = resolvers

canalizado.js

const {detectarCampos} = require('../_extra/comunes');

const resolvers = {
    Canalizado:{
        cnl_tipo: (parent, args, { models, options }, info) => {
            return parent.getCanalizadoTipo(options)
        },
    },
    Query: {
        async getCanalizado(root, { codigo }, { models, context }, info) {
            return await models.canalizados.findByPk(codigo,
                {attributes: detectarCampos(info),});
        },
        async getCanalizados(root, { tipo }, { models, options }, info) {
            var whereStatement = {};
            if (tipo)
                whereStatement.cnl_tipo = tipo;

            return models.canalizados.findAll({
                attributes: detectarCampos(info),
                where: whereStatement,
                limit: 2, 
                options
            });
        },
        async getAllCanalizados(root, args, { models, options }) {
            return models.canalizados.findAll({
                attributes: detectarCampos(info),
                limit: 100, 
                options
            });
        },
    },
    Mutation: {
    },
}

module.exports = resolvers

It works fine if I search in graphql with this sentence:

query{
  getArticulos(tipo:2){
    art_codigo
    art_nombre
    art_tipo{
      ari_nombre
    }
    art_latitud
    art_longitud
  }
}

Executing (default): SELECT [art_codigo], [art_nombre], [art_tipo], [art_latitud], [art_longitud] FROM [articulos] AS [articulos] WHERE [articulos].[art_tipo] = 2;
Executing (default): SELECT [ari_codigo], [ari_nombre] FROM [articulos_tipos] AS [articulos_tipos] WHERE [articulos_tipos].[ari_codigo] IN (2);

On the other hand, if I try to look for in a deeper level, I get automatic names from columns I don't need to use:

query{
  getArticulos(tipo:2){
    art_codigo
    art_nombre
    art_tipo{
      ari_nombre
    }
    art_canalizado{
      cnl_codigo
    }
    art_latitud
    art_longitud
  }
}

Executing (default): SELECT [art_codigo], [art_nombre], [art_tipo], [art_latitud], [art_longitud] FROM [articulos] AS [articulos] WHERE [articulos].[art_tipo] = 2;
Executing (default): SELECT [ari_codigo], [ari_nombre] FROM [articulos_tipos] AS [articulos_tipos] WHERE [articulos_tipos].[ari_codigo] IN (2);
Executing (default): SELECT [cnl_codigo], [cnl_fecha_alta], [........], [cnl_revisado], [cnl_tipo], [cnl_fuente], [cnl_autor], [CanalizadoTipoCaiCodigo] FROM [canalizados] AS [canalizados] WHERE [canalizados].[cnl_codigo] IN (51357, 51365, 51379, [........], 63910);

In this case, in Graphql returns this error: "message": "Invalid column name 'CanalizadoTipoCaiCodigo'.",

How can I ommite that field?? Could I use something like "attributes" to specify which attributes I'd like to show?? I tried to use it in resolvers, models... but always with no success

This error is the same if I look for a deep level:

query{
  getArticulos(relevancia:2){
    art_codigo
    art_nombre
    art_tipo{
      ari_nombre
    }
    art_canalizado{
      cnl_codigo
      cnl_tipo{
        cai_nombre
      }
    }
    art_latitud
    art_longitud
  }
}

Any idea about my problem? Everrything is wellcome!!

UPDATE

server.js

const express = require('express');
const { ApolloServer } = require('apollo-server-express');

const typeDefs = require('./configuracion/schema/typeDefs')
const resolvers = require('./configuracion/schema/resolvers')
const models = require('./configuracion/models')

const { createContext, EXPECTED_OPTIONS_KEY } = require('dataloader-sequelize');
const dataloaderContext = createContext(models.sequelize);

//const server = new ApolloServer({ typeDefs, resolvers, context: { models } });
const server = new ApolloServer({ 
    typeDefs, 
    resolvers, 
    context: async () => ({
      models,
      options: { [ EXPECTED_OPTIONS_KEY ]: dataloaderContext },
    }),
  });

const app = express();
server.applyMiddleware({ app });

models.sequelize.authenticate().then((err) => {
    console.log('*** MSG [server.js]: Successful Connection');
})
.catch((err) => {
    console.log('*** ERROR [server.js]: No ha sido posible conectarse a la base de datos', err);
})
//models.sequelize.sync();

app.listen({ port: 3000 }, () =>
    console.log(`** API ready at http://localhost:3000${server.graphqlPath} `)
);

configuracion/models/index.js

'use strict';

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(__filename);
//const env = process.env.NODE_ENV || 'development';
const config = require('../config_sqlserver')
const db = {};

const sequelize = new Sequelize(config.db_database, config.db_user, config.db_password, 
    {
        host: config.db_host,
        port: config.DB_PORT,  // <----------------The port number you copied
        dialect: "mssql",
        pool: {
            max: 5,
            min: 0,
            acquire: 30000,
            idle: 10000
        }
    }
);

fs
  .readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
    //const model = sequelize['import'](path.join(__dirname, file));
    const model = sequelize.import(path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

resolver > articulo_tipo.js

const Sequelize = require('sequelize');
const {detectarCampos} = require('../_extra/comunes');
const Op = Sequelize.Op;

const resolvers = {
    Query: {
        async getArticuloTipo(root, { codigo }, { models, context }, info) {
            return await models.articulos_tipos.findByPk(codigo, { attributes: detectarCampos(info)},);
        },
        async getArticulosTipos(_, { nombre, tipo }, { models }, info) {r
            var whereStatement = {};
            if(nombre)
                whereStatement.ari_nombre = {[Op.like]: '%' + nombre + '%'};

            if(tipo)
                whereStatement.ari_codigo = tipo;

            return models.articulos_tipos.findAll({ 
                attributes: detectarCampos(info),
                where: whereStatement, 
            });
        },
        async getAllArticulosTipos(root, args, { models }) {
            return models.articulos_tipos.findAll()
        },
    },
    Mutation: {
    },
}

module.exports = resolvers

score 0 · Answer 1 · answered Feb 10 '20 at 12:02

0

I don't use sequelize ... but I probably can point you in the right direction:

attributes are used already...
maybe not exactly the way you need ...
check what is returned from detectarCampos(info) in resolvers

Probably you'll find that info is undefined ... sometimes info is missing... why!?

    art_canalizado: (parent, args, { models, options }, info) => {
        return parent.getCanalizado(options); //It's an internal getter from sequelize, isn't it?
    },

getCanalizado is called with options while normally it should be called with more arguments:

    async getCanalizado(root, { codigo }, { models, context }, info) {

Fix:

Pass missging arguments - it should work (if detectarCampos works, of course).

answered Feb 10 '20 at 12:02

xadm

8,219
3
14
25

Yes, `detectarCampos`works :D. But certainly that `async getCanalizado(root, { codigo }, { models, context }, info) {` isn't called by `return parent.getCanalizado(options)`, because it's an internal getter which is created by sequelize automatically (I traced logs about it withoud success, so it´s true that it isn't called). – Andynedine Feb 10 '20 at 13:29
I'd log all result of `detectarCampos(info)` with call places ... check relations: `Articulo.belongsTo(models.canalizados, ` vs `Articulo.belongsTo(models.articulos_tipos,`, why not `articulos_canalizados` ? is `art_canalizado` in `canalizados` ? – xadm Feb 10 '20 at 13:57
`models.canalizados` is related with `articulos`, `news`, `events`... Each kind of those registers have their own "canalizado", and `canalizado` has his own `canalizado_tipo`. That is my problem, return that last level `articulos>canalizados>canalizados_tipo`. I can show `articulos>articulos_tipos`, however I can´t jump until `articulos>canalizados_tipos` without `articulos>canalizados>canalizados_tipos` because the relationship is between `archivos` and `canalizados`. It`s really dificult to explain, isn't it?? xD – Andynedine Feb 10 '20 at 14:22
1

IMHO problem is earlier ... on `articulos>canalizados` ... from SQL even for `art_tipo` it returns all fields, not only required `ari_nombre` ... use `model.canalizados....` instead `parent.getCanalizado()`? ... still, log resolver called (method, params) – xadm Feb 10 '20 at 15:03
Yes, It'd be my second option, using `model.canalizados.findByPk(...)`, but it'd generate too many SQL sentences. I would like to use the cache system like dataloader to optimize my SQL sentences...if it was possible, of course. – Andynedine Feb 10 '20 at 20:48
if you return `cnl_codigo` it shouldn't affect SQL – xadm Feb 10 '20 at 20:59
I thought so, but when it uses `parent.getCanalizado(options)` from sequelize, it returns all my fields, and some more: `Executing (default): SELECT [cnl_codigo], [cnl_fecha_alta], [........], [cnl_revisado], [cnl_tipo], [cnl_fuente], [cnl_autor], [CanalizadoTipoCaiCodigo] FROM [canalizados] AS [canalizados] WHERE [canalizados].[cnl_codigo] IN (51357, 51365, 51379, [........], 63910);` – Andynedine Feb 11 '20 at 06:06
1

I saw that earlier, but this is overfetching, one of the problems graphql tries to resolve. Have you tried findbypk? – xadm Feb 11 '20 at 09:04
Yes, is works with `findByPk`. Pros: It works. Cons:if I find 100 elements, it'll execute 100 new SQL sentences. :) It's a pity, because most of them are the same SQL sentence, so it'd be perfect to optimize it. – Andynedine Feb 11 '20 at 09:59
dataloader is for that ? – xadm Feb 11 '20 at 10:17
With dataloader you can optimize how many times a nested table is called. `SELECT * FROM articulos_tipos WHERE ari_codigo IN (, ....)` vs n-querys 'SELECT * FROM articulos_tipos WHERE ari_codigo=`. Sum up, Dataloader solves n+1 problem from Graphql – Andynedine Feb 11 '20 at 11:38
and what is in these 100 SQLs, how they are different? – xadm Feb 11 '20 at 11:41
In the worst case, as querys as diferents id's. The "big problem" is that most of querys are `SELECT * FROM canalizados_tipos WHERE cnl_tipo=1`, for example, and it's a pity send the same query again and again. So, finally, you have to send 100 extra querys to get a "type of register" when you'd only need... ¿1?¿2? – Andynedine Feb 11 '20 at 11:57
... as above ... dataloader? – xadm Feb 11 '20 at 12:01
With dataloader only one query. Previously, It detect how many different "types of register" is going to show, and execute only query like `SELECT * FROM canalizados_tipos WHERE cai_tipo IN (...)` to get that info, instead of n-querys one by one, id by id. – Andynedine Feb 11 '20 at 12:06
More information in webs such as https://itnext.io/what-is-the-n-1-problem-in-graphql-dd4921cb3c1a or https://engineering.shopify.com/blogs/engineering/solving-the-n-1-problem-for-graphql-through-batching. It`s really dificult to explain with my poor english :) Sorry! By the way, thanks a lot for your time ;) – Andynedine Feb 11 '20 at 12:07
I know.... why you're not using this for this scenario - dataloader-sequelize - as in title ? – xadm Feb 11 '20 at 12:14
Well, I'm really using that powerful library. My problem is that It works fine to certain level. It fails if you go deeper. `articulos>articulos_tipos` is OK but `articulos>canalizados>canalizados_tipos` fails. – Andynedine Feb 11 '20 at 12:23
where exactly it's used... I don't see – xadm Feb 11 '20 at 12:28
Post updated with more information at the end. Better now? Certainly, think I didn't post enough information – Andynedine Feb 11 '20 at 13:00
dataloader context defined in graphql context - not used in sequelize `findByPK` ... not working at all beside main models? ... [new context should be created per [parent] request] ... not using `graphql-sqeuelize`? explore `dataloader-sequelize` issues for similiar examples – xadm Feb 11 '20 at 13:30

Show specific columns with dataloader-sequelize in nested tables

1 Answers1

Fix: