Joining with another table workaround...

[jamesalanfinnigan]

For those requiring a join with another table, I came up with a workaround (disclaimer: clearly not the best solution but will keep you moving).

Let's say you have some parts in a hardware store and are associating nuts and bolts. However, you might want to further qualify the search by the size in mm.

Your declaration might look similar to this...sizes table not shown for brevity.

Bolts: {

      sqlTable: 'bolts',
      uniqueKey: 'id',
      fields: {
        partName: {
          sqlColumn: 'boltname'
        },
        bolts: {
          junction: {
            sqlTable: 'bolts_nuts',
              sqlJoins: [
                (boltTable, junctionTable) => `${boltTable}.id = ${junctionTable}boltid`,
                (junctionTable, nutTable) => `${junctionTable}.nutid = ${nutTable}.id`
              ]
          }
        }
      }
    }

and your query is something like this...

query (size: "10mm") {
  bolts {
     boltName
     boltSize
     nuts {
        nutName
        nutSize
      }
   }
}

`

This should return all the nuts/bolts that are 10mm. However, there's not a 'built-in' way to do this.

I've created a function that essentially creates a join between the joinMonster query and another custom query. (disclaimer: yes, it's now two queries and a hack!) The reason for this is that joinMonster creates dynamic queries that may or may not include a table to join on in a where clause. :(

Disclaimer: This isn't compiled code but should be close enough

async function dataRequest(parent, args, ctx, resolveInfo, qualifierSQL) {
  
    return await joinMonster(resolveInfo, ctx, sql => {

      const totalSQL = `
        SELECT o2.* FROM
        ( ${qualifierSQL} ) o1
        INNER JOIN
        (
          ( ${sql}  )
        ) o2 on o1.id = o2.id
      `
      var promise = new Promise(function(resolve, reject) {
         yourDBDataFunction(totalSQL).then(results => {
          resolve(results.records);
        });
      });
  
      return promise;
    }, { dialect: 'pg' })
  }`


Then in your query section put a query that can join with another table.  This is could be essentially the same query joinMonster created but with a qualifier.  It depends on what your query is asking for.

  const filterSQL = `
        SELECT n.id
        FROM sizes s
        LEFT JOIN sizes_bolts s_b on s.id = s_b.sizeid
        LEFT JOIN nuts n ON s_b.nutid = n.id
        **WHERE s.size = ${args.size}**
        `
        return dataRequest(parent, args, ctx, resolveInfo, filterSQL);

Lastly, in order to use the latest graphQL (as of this writing), you can use this code to get around the 'typeConfig' errors. This is for those using:

https://github.com/acarl005/join-monster-graphql-tools-adapter

joinAdapter.ts

const assert = require('assert');

export default function (schema, jmConfigs) {
  for (let typeName in jmConfigs) {
    const type = schema._typeMap[typeName]

    if (!type._typeConfig) {
      type['_typeConfig'] = {}
    }
    assert(type, `Type with name ${typeName} not found in schema.`)
    decorateType(type, jmConfigs[typeName])
  }
}

function decorateType(type, jmConfig) {
  const typeConfig = type._typeConfig
  typeConfig.sqlTable = jmConfig.sqlTable
  typeConfig.uniqueKey = jmConfig.uniqueKey
  if (jmConfig.alwaysFetch) {
    typeConfig.alwaysFetch = jmConfig.alwaysFetch
  }
  if (jmConfig.typeHint) {
    typeConfig.typeHint = jmConfig.typeHint
  }
  if (jmConfig.resolveType) {
    typeConfig.resolveType = jmConfig.resolveType
  }
  for (let fieldName in jmConfig.fields) {
    const field = type._fields[fieldName]
    assert(field, `Field "${fieldName}" not found in type "${type.name}".`)
    Object.assign(field, jmConfig.fields[fieldName])
  }
}

I hope that helps somebody as joinMonster is an amazing tool that could really use some TLC to fix/enhance!

Cheers!

[nicoabie]

Hi @jamesalanfinnigan I'm a new maintainer and I would like to see if this is still valid with latest join-monster. Can you file a https://github.com/join-monster/join-monster-sscce ?

Possibly we could transform this into either a feature request and/or documentation.

Thanks!