We carried out the process of working with Select actions within the scope of the Builder 1 course for the web application named I Have an Idea! In this learning content, we will examine the SQL action creation process on a sample scenario on the Dashboard screen of I Have an Idea! We will examine a sample scenario on the Dashboard screen of the web application.
We carried out the process of working with Select actions within the scope of the Builder 1 course for the web application named I Have an Idea! In this learning content, we will examine the SQL action creation process on a sample scenario on the Dashboard screen of I Have an Idea! We will examine a sample scenario on the Dashboard screen of the web application.
As we will remember, in I Have an Idea!, users can share their ideas, and other users can comment and like these ideas. On the Dashboard screen, we present the details of each idea, the details of the person who shared the idea, the category of the idea and the idea interactions. Within the scope of Builder 1, we prepared the action that presents the details of the idea, the details of the idea owner and the category details without writing any code in line with the capabilities of the Select action. Now let's create a new SQL action and prepare the action that presents both the details we just mentioned and the number of interactions for each idea step by step.
Step 1: Creating the Basic Query
First, let's create a SQL Action using the Custom Action panel.
Now let's get the basic information of the ideas that will be in our action. Our table named “Idea” contains the title and image of the ideas. To pull this information, we write the relevant SQL commands in the editor.
Query to display:
select i.Id as IdeaID, i.IdeaImage, i. IdeaTitle from Idea i
This query retrieves the unique ID, title and image of each idea.
After the first step, in the second step, let's pull the information of the person who created the idea.
Step 2: Adding the Information of the Person Who Shared the Idea
To find out who shared the idea, we need some information from the table we created earlier called “Personnel”. We will match this table with the identity of the person who shared the idea. First, we join the Personnel table to the Idea table with a Join operation. While doing the join operation, we use the IdeaHolder column in the Idea table and create the relationship with the ID of Personnel. Then, under Select, we enter the column names that we will pull from the Personnel table, which will allow us to pull the unique Personnel Id, Personnel name, Personnel image and Title. We display the final version of the query on the screen.
Screen Query to be displayed:
select i.Id as IdeaID, i.IdeaImage, i. IdeaTitle, p.Id as PersonnelID, p.FullName, p.Title, p.PersonnelImage from Idea i
left join Personnel p on p.Id=i.IdeaHolder
Let's continue expanding our action to pull the idea category information in the next step.
Step 3: Adding the Idea's Category
We will use the “Category” table to add the categories of the ideas. Now let's join the Category table with the Idea table by creating a new Join operation on the action. While doing the join operation, we use the CategoryID column in the Idea table and create the relationship with the Id of Category. Then, under Select, we enter the column names that we will pull from the Category table, which will allow us to pull the unique Id and category name.
select i.Id as IdeaID, i.IdeaImage, i. IdeaTitle, p.Id as PersonnelID, p.FullName, p.Title, p.PersonnelImage, c.Id as CategoryID, c.CategoryName from Idea i
left join Personnel p on p.Id=i.IdeaHolder
left join IdeaCategory c on c.Id=i.IdeaCategoryID
Step 4: Calculating Likes
In our example, we want to calculate the total number of likes for each idea. As we will remember, likes are kept in a table named “Likes”. Now we first need to create a subquery that calculates the number of likes for each idea. You can think of this subquery as an independent query in itself. Now let's create this query.
select IdeaID, Count(Id) as TotalLike From IdeaLike
group by IdeaID
This subquery allows us to get the total number of likes for each IdeaID. Now let's connect this independent query to our main query using Left Join.
left join (select IdeaID, Count(Id) as TotalLike From IdeaLike
group by IdeaID) as LikeCount on i.Id=LikeCount.IdeaID
To get the total number of likes, we create a temporary table named Like_counts in the action.
We get the total number of likes by matching i.Id in the main query with Like_counts.IdeaID. Let's add to the action to pull the total number of likes we get. We use the Coalesce function to pull the total number of likes. If there are no likes for an idea, the number of likes will be NULL (empty). We use the COALESCE function to show it as “0”. Thus, we have the action query structure presented on the screen.
select i.Id as IdeaID, i.IdeaImage, i. IdeaTitle, p.Id as PersonnelID, p.FullName, p.Title, p.PersonnelImage, c.Id as CategoryID, c.CategoryName, COALESCE(LikeCount.TotalLike, 0) as TotalLike from Idea i
left join Personnel p on p.Id=i.IdeaHolder
left join IdeaCategory c on c.Id=i.IdeaCategoryID
left join (select IdeaID, Count(Id) as TotalLike From IdeaLike
group by IdeaID) as LikeCount on i.Id=LikeCount.IdeaID
Now let's add the query that will allow us to pull the total number of comments in a similar structure to the action. With these operations, we reach the final version of the action as seen on the screen. We complete the process by naming and saving the action.
select i.Id as IdeaID, i.IdeaImage, i. IdeaTitle, p.Id as PersonnelID, p.FullName, p.Title, p.PersonnelImage, c.Id as CategoryID, c.CategoryName, COALESCE(LikeCount.TotalLike, 0) as TotalLike, COALESCE(CommentCount.TotalComment, 0) as TotalComment from Idea i
left join Personnel p on p.Id=i.IdeaHolder
left join IdeaCategory c on c.Id=i.IdeaCategoryID
left join (select IdeaID, Count(Id) as TotalLike From IdeaLike
group by IdeaID) as LikeCount on i.Id=LikeCount.IdeaID
left join (select IdeaID, Count(Id) as TotalComment From IdeaComment
group by IdeaID) as CommentCount on i.Id=CommentCount.IdeaID
Thanks to this structure, we have created a special SQL action that can get detailed information about the ideas, includes the category information of the idea and the person who shared the idea in this process, and calculates the total number of comments and likes for each idea. You can quickly prepare complex SQL actions in a similar setup in Kuika's SQL editor according to your needs.
