Custom SQL Query from multiple tables

Introduction:-

Sometimes we encounter with a very complex DB and henceforth, a more complex query, where data has to ask many other tables for if they do have the same value with them or not. Basically, implementing a number of Constraints. For this we can then go for a CustomSQL mechanism of liferay. 
For more:- liferay wiki

Problem:
Sometimes we want to extract data from two tables, but are not able to.

Solution:-

Lets assume we have a query like this:-

SELECT forumPosts.id as id, forumPosts.title as title, forumPosts.date as date, 

forumPosts.author as author, 

forumPosts.countAnswers as count_answers, forumUsers.firstName as fname, 

forumUsers.lastName as lname FROM forumPosts INNER JOIN users ON forumPosts.author =forumUsers.userid WHERE forumPosts.parentPost IS NULL ORDER BY id DESC;

Steps:
1. Create a portlet (as in here).
2. Create service.xml (as in here).
 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.1.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_0.dtd">
<service-builder package-path="com.test">
    <author>priyanka</author>
    <namespace>forum</namespace>


    <entity name="ForumPosts" table="forumPosts" local-service="true"
        remote-service="true" >

      <column name="id" type="long" primary="true" />
      <column name="title" type="String" />
      <column name="date" type="String" />
      <column name="parentPost" type="long" />
      <column name="author" type="long" />
      <column name="countAnswers" type="long" />  
      <column name="lastPost" type="long" />
    </entity>

     <entity name="ForumUsers" table="forumUsers"
        local-service="true" remote-service="true">

        <column name="userid" type="long" primary="true" />
        <column name="firstName" type="String"  />
        <column name="lastName" type="String" />
    </entity>
</service-builder>

3. Do "ant build-service"
4. Create a folder custom-sql in {my-portlet}/docroot/WEB-INF/src/
5. Create default.xml in {my-portlet}/docroot/WEB-INF/src/custom-sql/
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
    <sql file="custom-sql/get_forum_data.xml" />
</custom-sql> 
6. Create get_forum_data.xml
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql id="com.test.service.persistence.ForumPostFinder.getForumData">
<![CDATA[

SELECT forumPosts.id as id, forumPosts.title as title, forumPosts.date as date, 

forumPosts.author as author, 

forumPosts.countAnswers as count_answers, forumUsers.firstName as fname, 

forumUsers.lastName as lname FROM forumPosts INNER JOIN users ON forumPosts.author =forumUsers.userid WHERE forumPosts.parentPost IS NULL ORDER BY id DESC;

 ]]>
</sql>
</custom-sql>

7. Create a class ForumPostFinderImpl under  package com.test.service.persistence

public class ForumPostFinderImpl extends BasePersistenceImpl implements
        ForumPostFinder {
public static String GET_FORUM_DATA = ForumPostFinder.class.getName()+".getForumData";

    public Object getForumData()
            throws SystemException {
        // open a new hibernate session in normal case when you are opening
        // session for same entity
        Session session = null;
        SQLQuery query = null;
        QueryPos qpos;
        try {
            session = openSession();
            String sql = CustomSQLUtil.get(GET_FORUM_DATA);
            // create a SQLQuery object
            query = session.createSQLQuery(sql);
            query.setCacheable(false);


            query.addScalar("id", Type.INTEGER);
            query.addScalar("title", Type.STRING);
            query.addScalar("date", Type.DATE);
            query.addScalar("author", Type.STRING);
            query.addScalar("count_answers", Type.INTEGER);
            query.addScalar("fname", Type.STRING);
            query.addScalar("lname", Type.STRING);
          
           //This is used when you want to pass any value to the query and place "?" in the query where you want the value to be set
           //qpos.add(Id); 
            
        } catch (Exception e) {
            System.out.println("Exception : Finder custom");
            e.printStackTrace();
        } finally {
            closeSession(session);
        }

        // execute the query and return a list from the db
        return (Object) query.list().get(0);
    }
}
 

8. Go to ForumPostLocalServiceImpl and add the following functionpublic Object getForumPostAndUserData() throws SystemException {
        return ForumPostFinderUtil.getForumData;
    }

9. Again do ant build-service.

10. ForumPostFinderUtil and interface ForumPostFinder is created.

11. In your portlet doView()
                    ForumPostLocalServiceUtil
                                .getForumPostAndUserData());

This is all, you can build-service and then deploy the portlet.

Enjoy Coding :)