how to handle JOINs?

[afeld]

Hi @malcolm-kee (FYI that you don't seem to be Watching the repository),

I am looking to use this plugin for pulling related data from two tables: articles, and article_images. The latter has a foreign key of article_id, as you'd expect. I want to do something like this:

{
  allArticlesResults {
    edges {
      node {
        title
        body
        # JOIN
        article_images {
          caption
          url
        }
      }
    }
  }
}

Unfortunately, I'm not seeing a clear way to do so. My workaround is to query for all records of each:

{
  allArticlesResults {
    edges {
      node {
        title
        body
      }
    }
  }
  allArticleImagesResults {
    edges {
      node {
        article_id
        caption
        url
      }
    }
  }
}

and then do the JOIN in JavaScript after the fact. Wondering if you've come across this, or if there's a good way to deal with it.

I am new to both GraphQL and Gatsby, so forgive me if I'm missing something here. Thanks!

[malcolm-kee]

Hi @afeld thanks for reporting the issue.

You can do a join on the query options in your gatsby-config.js.

Example:

module.exports = {
  plugins: [
    {
      resolve: `gatsby-source-mysql`,
      options: {
        connectionDetails: {
          host: 'localhost',
          user: 'db-username',
          password: 'db-password',
          database: 'world'
        },
        query: `SELECT * FROM articles art JOIN article_images img ON img.article_id = art.id`,
        idFieldName: 'ID'
      }
    }
    // ... other plugins
  ]
};

Let me know if it works

[afeld]

Hmm, it doesn't, unfortunately. Getting an error on the GraphQL result:

Cannot query field "article_images" on type "ArticlesResults". Did you mean "article_id", "articlesId", "article_type_id", "feature_image", or "thumb_image"?

[malcolm-kee]

Hmm... now I understand what you want to achieve, which is a one-to-many relationship in the GraphQL query. I will need to do some exploration before able to answer you.

[malcolm-kee]

@afeld I've change this plugin to able to join and it's in v2 branch now. Could you install the v2 version and let me know if it works for you before I publish it to npm?

How to install

Use the following commands to install v2 from git:

npm install git://github.com/malcolm-kee/gatsby-source-mysql.git#v2

OR

yarn add git://github.com/malcolm-kee/gatsby-source-mysql.git#v2

Docs

Note that the configuration for the plugin has been changed to support this, so you would need to change your gatsby-config.js when upgrade.

You can refer the updated docs here.

[afeld]

You rock, thanks for adding support so fast! Unfortunately, running into an error:

https://github.com/brooklynrail/gatsby-test/pull/1

The code there is open source, but the data isn't (at the moment) - will think on if the latter is possible, and dig in more.

[malcolm-kee]

@afeld could you try to delete your .cache folder and public folder and retry again?

[malcolm-kee]

Also, the limit of the returned results is usually specified in the graphql query. You can update your grahql query to something like

{
  allArticlesResults(limit: 100) 
  {
            edges {
              node {
                id
                permalink
              }
            }
  }
}

[afeld]

Ah, sorry, it was a mistake on my end: I had some dangling rows on the right side of the JOIN, and it didn't like that. I'll open an issue about how to handle that. Thanks again!

the limit of the returned results is usually specified in the graphql query

Yeah, I was trying to reduce the build time by having a smaller MySQL query result and thus fewer result nodes to process, but not the end of the world.