How to loop a tRestClient to fetch all data

[jhammagisa]

I aim to retrieve all the data from our Freshdesk ticketing system. However, I've encountered a limitation with the API, which only permits a maximum of 100 data entries per page.

As we have over 1,000 contacts to gather and store in our database, I'm currently facing a challenge in looping back to the tRESTClient to fetch the next set of data and appending it to my CSV file.

Here's my current setup: tRESTClient -> tXMLMap -> tLogRow -> tFileOutputDelimited. My end goal is to be able to fetch all the contact details and store it to our MySQL DB. 

I am very new at using Talend so any guidance on how to proceed would be greatly appreciated.

[Shicong_Hong]

Hello

There has been some similar topics on community, we usually use tLoop make a loop, set the limit & offset parameters to control the number of records retrieved each time until all records are retrieved and the loop ends. Please get more information about your API and check if these parameters are available. 

Regards

Shicong

[jhammagisa]

Hi Shicong, 

Thank you for your reply.

The API we're working with doesn't provide information about the total number of data or the number of pages available. Our current approach involves using a variable called "pageCount" and implementing an if statement. If the condition "tLogRow nb_line <= 100 and tLogRow nb_line != 0" is met, we'll increment the pageCount by 1 and loop back to the tRestClient to retrieve the data from the next page.

The URL structure we're using is {baseUrl}api/v2/contacts?page=1&per_page=100, where we need to update the page number with each loop iteration.

I'm currently facing challenges in setting up the pageCount parameter for the tRestClient to read, and also connecting the loop back to the tRestClient. I've reviewed similar posts and attempted the suggested solutions, but unfortunately, they haven't resolved our issue so far.

[Shicong_Hong · Accepted solution]

You can define a context variable for page number called pageNumber, int type, default value as 1, and set a dynamic URL:

"{baseUrl}api/v2/contacts?page="+context.pageNumber+"&per_page=100"

Use a tLoop with For type to make a loop, define a context variable (boolean type, default value as "true") called "condition" and use this context variable in the Condition field.

A simple job looks like:

tLoop--iterate--tRestClient--> tXMLMap -> tFileOutputDelimited_1--oncomponentok--tJava

on tJava:

int number_of_records=((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE"));

if(number_of_records<=100&&number_of_records!=0){

context.pageNumber=context.pageNumber+1;

}else{
context.condition=false;
}

Please have a try and let me know if you have any issues/questions.

[jhammagisa]

Hi Shicong

Thank you so much for your help. I was able to make the loop work and store all the data in my database, thanks to your guidance.

I now have another issue where I need to fetch each conversation related to each ticket. I can do this by calling each ticket using this template: 'https://domain.freshdesk.com/api/v2/tickets/[ticket number]/conversations', where [ticket number] is updated each time the API has finished fetching all the conversations related to the ticket.

Currently, I have this job:

I am not quite sure how to achieve my desired goal. Any advice would be greatly appreciated.

Thank you!

Just for reference:

My DBInput has this query: "select ticketID from dw_ticket limit 1"

tJava_2 has this condition:

int number_of_records=((Integer)globalMap.get("tLogRow_1_NB_LINE"));

if(number_of_records == 100){

context.pageNum = context.pageNum + 1;

} else if (number_of_records <= 100 && number_of_records != 0) {

context.condition = false;

} else{

context.condition = false;

}

[Shicong_Hong]

where's the tDBInput component in your job?  

My DBInput has this query: "select ticketID from dw_ticket limit 1"

=>Only query 1 ticketID? but you want to iterate all ticket ids one by one, and change the ticket id in the URL each time. 

[jhammagisa]

Hi Shicong, 

I got my loop working already. Thank you for all your help. 

This is what my job looks like:

[Shicong_Hong]

Great! Glad to be of help!