Display data in Liferay Search Container from different database tables : Liferay

Question

I have been using search container in liferay to display data from a table. Works well!! here is a snippet of code:

<% 
List<testapp> pendingApprovals = ActionClass.getPendingLeaveApplications();
%>
<liferay-ui:search-container delta="20" emptyResultsMessage="No Results Found">
    <liferay-ui:search-container-results total="<%= pendingApprovals.size() %>"
       results="<%= ListUtil.subList(pendingApprovals , searchContainer.getStart(), searchContainer.getEnd()) %>" />

    <liferay-ui:search-container-row keyProperty = "empId" modelVar="search"
        className="com.test.mis.portal.model.testapp">
        <liferay-ui:search-container-column-text name='Leave Duration' value = '<%=String.valueOf(search.getLeaveDuration())%>'   href="" />
    </liferay-ui:search-container-row>

    <liferay-ui:search-iterator/>
</liferay-ui:search-container>

Using the above code I display data from the testapp table based on some condition. In the same code I would like to add a row and display data. The data for this row should come from another table. In short I want to display data using search container from two different database tables. Is it possible to do? My requirement is such that data comes from two different tables

EDITED SECTION WITH REQUIREMENT I have Employee table with some fields I have another table Leave with some Fields. empId is in Leave table which maps to Employee table.

I have a search container whicg displays the data from the Leave table only if the leave is pending I want to display only those fields from Employee table which match with Leave table and satisfy the above condition.

Is there some connection between the entities of testApp and the 'other Table', e.g. like a foreign key ? will there be exactly one matching instance of the 'other table' per row, or more ? Do you use Service Builder for the database ? Your question is really vague and hard to answer, please try to make things more clear — yannicuLar, Apr 23 '13 at 19:55
@yannicuLar: Yes I have foreign key. I apologise as I havent mentioned much in my question. I have FK. testApp has testAppId and the "otherTable" has testAppId which refers to testApp table. I have used Service Builder. I want to display only those data entries where FK matches.. I think it may not be possible since in the search container we mention the entity in the className. But do enlighten me if there is a way. One way is using custom query which I am not aware of. — Seeya K, Apr 24 '13 at 03:33
Just a clarification : Do you need to display in each row, data that are retrieved from 2 Entities ? — yannicuLar, Apr 25 '13 at 15:34
@yannicuLar: Yes! More specifically, I need the person's name from Employee table whose LeaveStatus is pending. LeaveStatus is in Leave table. Check the EDIT.. — Seeya K, Apr 25 '13 at 15:47
I tried implementing the solution given here by Prakash but that too isn't working. Gives error while creating function — Seeya K, Apr 25 '13 at 15:51
No, I think you got me wrong. Please give as a detailed description of 1) what columns will each row have, 2) what attribute and from what entity will be displayed in each column — yannicuLar, Apr 25 '13 at 18:00
@yannicuLar: 1. Each row will have: Person Name (From Employee Entity) , Leave Duration ( From Leave Entity) Leave Reason ( from Leave Entity) 2.empName from Employee entity, leaveDuration,LeaveReason both LLeave Entity — Seeya K, Apr 26 '13 at 03:49
@yannicuLar: empid is the mapping key in the Leave table. So if the empid matches from Employee and Leave and if the leaveStatus in Leave entity has "Pending" as the entry, then the search container should display the records. I hope this is what you were asking. — Seeya K, Apr 26 '13 at 03:58
@yannicuLar By the way, I have upvoted your answers in my ques wherever I can. Hope it helps you increase your rep... :) — Seeya K, Apr 26 '13 at 05:21

score 2 · Answer 1 · answered Apr 24 '13 at 07:24

There might be many ways, here I list a few which come to my mind readily:

one way is to modify the TestAppImpl model generated through ServiceBuilder to include your dependency something like this:

public class TestAppImpl extends TestAppBaseImpl {

    private List<OtherTableData> otherTableDataList;

    private OtherTableData otherTableData;

    // if want to retrieve a list of rows
    public List<OtherTableData> getOtherTableDataList() {

        // call a custom method created in the OtherTableDataLocalService
        // testAppId variable is available to TestAppImpl
        List<OtherTableData> otherDataList = OtherTableDataLocalServiceUtil.getOtherDataListByTestAppId(testAppId);

        return otherDataList;
    }

    // if want to retrieve just one row
    public OtherTableData getOtherTableData() {

        // call a custom method created in the OtherTableDataLocalService
        // testAppId variable is available to TestAppImpl
        OtherTableData otherData = OtherTableDataLocalServiceUtil.getOtherDataByTestAppId(testAppId);

        return otherData;
    }
}

You would have to rebuild your service after the above change.

Now in the JSP you just can use:

<liferay-ui:search-container-column-text name='Other table data name' value='<%=search.getOtherTableData().getName() %>' href="" />

Or else if you don't want to change the TestAppImpl then in the JSP you can use:

<liferay-ui:search-container-column-text>

<%
List<OtherTableData> otherDataList = OtherTableDataLocalServiceUtil.getOtherDataListByTestAppId(search.getTestAppId());

for (OtherTableData tempData : otherDataList) {
%>

    <%=tempData.getName() %>
    <%=tempData.getDescription() %>
    <%=tempData.getData() %>

<%
}
%>

</liferay-ui:search-container-column-text>

A variation of point-2 above:

<liferay-ui:search-container-column-jsp
    name="otherDataFetch"
    path="/html/portlet/testApp/other_data.jsp"
/>

And in the other_data.jsp we can have the following code:

<%
ResultRow row = (ResultRow)request.getAttribute(WebKeys.SEARCH_CONTAINER_RESULT_ROW);

TestApp search = (TestApp) row.getObject();

List<OtherTableData> otherDataList = OtherTableDataLocalServiceUtil.getOtherDataListByTestAppId(search.getTestAppId());

for (OtherTableData tempData : otherDataList) {
%>

    <%=tempData.getName() %>
    <%=tempData.getDescription() %>
    <%=tempData.getData() %>

<%
}
%>

Hope this is what you were looking for or else atleast it may give you a hint to go forward.

Thanks for the explanation. I will try to implement and let you know within an hour :) Please follow this post — Seeya K, Apr 24 '13 at 07:28
It is not working. May be I should write down my requirent in more detail. Check the edited part in my question. EDITED SECTION WITH REQUIREMENT — Seeya K, Apr 24 '13 at 09:00
Can you tell me specifically what is not working? The requirement just re-iterates what you had already written, you have just given the name of the two tables `testapp = leaves` and `otherDataTable == Employee`. Also in the `EDITED SECTION WITH REQUIREMENT` by `fields` do you mean `rows` of data or columns in those table? Also I assume `Employee` and `Leave` are separate model objects created. — Prakash K, Apr 24 '13 at 09:24
Yes Employee and Leave are separate model objects. By fields I meant columns in those tables. In the first option that you mentioned, the line: List otherDataList = OtherTableDataLocalServiceUtil.getOtherDataListByTestAppId(testAppId); gives error for the method saying that the method doesn't exist — Seeya K, Apr 24 '13 at 09:27
I built the services and followed exactly like you mentioned. Bu tstill there is an error. I guess you are on liferay community. I had posted the same in that community. I got some different answers. You could have a look at it: http://www.liferay.com/community/forums/-/message_boards/message/23989713 — Seeya K, Apr 24 '13 at 09:30
Did you create the method `getOtherDataListByTestAppId` in `OtherTableDataLocalServiceImpl` or your `LeavesLocalServiceImpl` or whatever model you are using and then re-built the service? If you are interested only with a few properties/columns and not the whole object/table then you would have to use custom-finder. If fetching the whole object is not a concern than my approach should work, but this would be a little expensive since it would call `getOtherDataListByTestAppId` `n` number of time. Where `n` denotes the number of items/page — Prakash K, Apr 24 '13 at 10:31
I only need to fetch those employee names from the Employee table whose leaves are pending. The data won't be lot. Leaves can hardly be 6-8 entries only on certain ocassions. — Seeya K, Apr 24 '13 at 10:37

score 2 · Accepted Answer · answered Apr 29 '13 at 07:18

2

Your problem here has 2 faces:

Being able to retrieve Data from tables Leave and Employee, by using the employeeId Foreign key. You don't need a custom query for that, and it's a pretty trivial task, I'm just pointing it out.
Displaying in the search container, data that cannot be retrieved from one Data/ Table. As you can see, the 'liferay-ui:search-container-row' attribute named 'className' which ca take the valu of one Class name. About this, I can see 2 approaches:

a) Retrieve results per Leave table, and filter out by employee ID and pending status. Then in each row, use the employeeId to get back again the Employee instance (by EmployeeLocalServiceUtil. getEmployee(empId)), then get Employye attributes, like employee Name etc. This will need to get your hands dirty on the jsp file

b) Create a custom Class (say EmployeePendingLeaves), and use it as the searchContainer's model class. Don't include it in your database model. Just create a function that scans the Employee and Leave tables, and creates a EmployeePendingLeaves instance for each result row. it should have a variable/attribute for each of the row's columns

answered Apr 29 '13 at 07:18

yannicuLar

3,083
3
32
50

I applied the first approach you suggested. Thanks yannicuLar it worked!! But out of curiosity I woulod like to know about the second approach too.In the second approach you have suggested me to use custom class as searchContainder's model class. So from what i understood should I write a function EmployeePendingLeaves in my action class and include it in the className.. Is it correct? Btw I have upvoted your answer and accepted too :) – Seeya K May 07 '13 at 12:00
No, In my example, EmployeePendingLeaves is a class, not a function. Just a class with 3 variables, one for each row's column. In your action Class, you'll just create these objects and return them to jsp. This can be done in a function that scans the db for Employee and Leave entries, and creates one EmployeePendingLeaves instance for each result/row. Then Returns an array with these instances – yannicuLar May 08 '13 at 02:34
@ yannicuLar: Thanks I will try that as an exercise. in the meantime could you help me with http://stackoverflow.com/questions/16229369/get-images-from-liferay-document-library-whose-id-is-stored-in-separate-databse?noredirect=1#comment23567377_16229369 I want your help with the EDITED section of the ques.. – Seeya K May 08 '13 at 06:17

Display data in Liferay Search Container from different database tables : Liferay

2 Answers2

Linked