Show specific columns in nested tables

[aaznar]

Hi everybody!! I need your help afer days and days without solving my problem.

Currently I have some models. It works fine as long as I show associated tables without third level.

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;

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

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

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!!

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.