Using Double SplitinBatches Node with MySQL Node

Questions
#1

Here is my usecase:

  1. Cron Job run every x days. It select all the email address from the database. (I’ll replace start node with cronjob)
  2. Then SplitinBatches Node split all the data into batches. (Currently Batch size is 1)
  3. Then goes into Another MySQL Node(Number 3) (Checking for that email got from MySQL Node (Number 1) inside different table)
  4. Then It’ll send an Email

The Problem:
As you can see in the below screenshot, Node Number 3 has 2 Data where Node 4 & Node 5 have only one. Which means the data isn’t fully coming to the end.

What I want to achieve:
I’m getting the email from one MySQL table then check whether that email is present in another table. If it doesn’t present. I want to send an email to those email address which isn’t present.

image
image1078×479 29.1 KB

Workflow Code

{
      "nodes": [
        {
          "parameters": {},
          "name": "Start",
          "type": "n8n-nodes-base.start",
          "typeVersion": 1,
          "position": [
            380,
            300
          ]
        },
        {
          "parameters": {
            "operation": "executeQuery",
            "query": "SELECT email FROM demo;"
          },
          "name": "MySQL",
          "type": "n8n-nodes-base.mySql",
          "typeVersion": 1,
          "position": [
            550,
            300
          ],
          "credentials": {
            "mySql": "Singapore DB"
          }
        },
        {
          "parameters": {
            "batchSize": 1,
            "options": {}
          },
          "name": "SplitInBatches",
          "type": "n8n-nodes-base.splitInBatches",
          "typeVersion": 1,
          "position": [
            700,
            300
          ]
        },
        {
          "parameters": {
            "operation": "executeQuery",
            "query": "=SELECT email from api_key WHERE email='{{$node[\"SplitInBatches\"].json[\"email\"]}}';"
          },
          "name": "MySQL1",
          "type": "n8n-nodes-base.mySql",
          "typeVersion": 1,
          "position": [
            850,
            300
          ],
          "credentials": {
            "mySql": "Singapore DB"
          }
        },
        {
          "parameters": {
            "fromEmail": "[email protected]",
            "toEmail": "={{$node[\"SplitInBatches1\"].json[\"email\"]}}",
            "subject": "Hi",
            "text": "How are you?",
            "options": {}
          },
          "name": "Send Email",
          "type": "n8n-nodes-base.emailSend",
          "typeVersion": 1,
          "position": [
            1170,
            380
          ],
          "credentials": {
            "smtp": "SendGrid 1"
          }
        },
        {
          "parameters": {
            "batchSize": 1,
            "options": {
              "reset": false
            }
          },
          "name": "SplitInBatches1",
          "type": "n8n-nodes-base.splitInBatches",
          "typeVersion": 1,
          "position": [
            1000,
            310
          ]
        }
      ],
      "connections": {
        "Start": {
          "main": [
            [
              {
                "node": "MySQL",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "MySQL": {
          "main": [
            [
              {
                "node": "SplitInBatches",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "SplitInBatches": {
          "main": [
            [
              {
                "node": "MySQL1",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "MySQL1": {
          "main": [
            [
              {
                "node": "SplitInBatches1",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "Send Email": {
          "main": [
            [
              {
                "node": "SplitInBatches",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "SplitInBatches1": {
          "main": [
            [
              {
                "node": "Send Email",
                "type": "main",
                "index": 0
              }
            ]
          ]
        }
      }
    }
#2

Hey @mcnaveen!

Is there a particular reason that you’re using the SplitInBacthes node? If the MySQL node returns different items then the subsequent node will execute for these different items.

For example, if the MySQL node returns similar to the following

[
  {
    "id":1,
    "email": "[email protected]"
  },
  {
    "id":2,
    "email": "[email protected]"
  }
]

The MySQL1 node will execute the query for both the outputs. Now based on the output of this node you can decide what should be the next step in the workflow. Let me know if this helps :slightly_smiling_face:

1 Like
#3

Hey @harshil1712 Happy New Year Bro :partying_face: Thanks for your input.

I followed the different approach and Solved using WHERE NOT EXIST in MySQL Select query

Here it is:

SELECT email 
FROM   demo d 
WHERE  NOT EXISTS (
   SELECT email
   FROM   api_key
   WHERE  email = d.email
   );

Explanation:

It only shows the email address from table demo which is not present in the table api_key.

Then using the Split in Batches to sending an Email to all the batches

image
image808×448 22.6 KB