Any Options To Combine 2 Cactivedataprovider?

Yii 1.1 General Discussion
1

I have 3 table as below…

tbl_user

[sql]

user_id | name | division

 1    | Anthony   | admin


 2    | Jane      | admin


 3    | Rachel    | admin


 4    | Abby      | account


 5    | Anna      | account


 6    | Henry     | IT


 7    | Jackson   | IT[/sql]

tbl_course

[sql]

course_id | course_name | start_date | end_date | days | hours

 1      | ABC          | 2012-01-02 | 2012-01-04 | 3    | 0


 2      | DEF          | 2012-01-12 | 2012-01-12 | 0    | 6


 3      | Yii Training | 2012-03-12 | 2012-03-16 | 5    | 0[/sql]

tbl_attend_course

[sql]

course_id | user_id

1        |    1


1        |    2


1        |    5


1        |    6





2        |    2


2        |    3


2        |    4


2        |    5


2        |    6[/sql]

a user may attend no course or more, a course might attended by one or more users.

My question is I want to generate report of number of days and hours for all user from a particular division which attended courses from

a range of start and end date.

sampel report as below…

[sql]

division : admin

start_date : 2012-01-01

end_date : 2012-01-31

user_id | name | course_id | days | hours

1 | Anthony | 1 | 3 | 0

2 | Jane | 1 | 3 | 0

2 | Jane | 2 | 0 | 6

3 | Rachel | 2 | 0 | 6

division : account

start_date : 2012-01-01

end_date : 2012-01-31

user_id | name | course_id | days | hours

5 | Anna | 1 | 3 | 0

4 | Abby | 2 | 0 | 6

5 | Anna | 2 | 0 | 6

division : IT

start_date : 2012-01-01

end_date : 2012-01-31

user_id | name | course_id | days | hours

6 | Henry | 1 | 3 | 0

6 | Henry | 2 | 0 | 6

7 | Jackson | NULL | NULL | NULL[/sql]

… Please help …

2

you might need another table just to hold your reports something like when a person started the course and what course and all that

3

I found the query but if using CActiveDataProvider, I can’t display all the data at one page due to the pagination issue. I try ‘pagination’ = > false in CActiveDataProvider but it gave me error. I am still look around on how to solve this issue. By the way, the query as below




SELECT u.user_id, u.name, ac.course_id, c.days, c.hours FROM tbl_user AS u

LEFT JOIN tbl_attend_course AS ac ON u.user_id = ac.user_id

LEFT JOIN tbl_course AS c ON ac.course_id = c.course_id AND c.start_date >= :start AND c.start_date <= :end_date

WHERE u.division = :division
4

Let’s see how I can help. Before that let’s get somethings straight.

  • There is no method you can use to combine the results (data) gotten of CActiveDataProvider. (You can write a function for that but I don’t see the need)

  • CActiveDataProvider holds Active Record objects. Unless the Active Record object is capable of holding data from joined tables, CActiveDataProvider cannot help you.

If you have display data from multiple tables, use CArrayDataProvider or CSqlDataProvider

5

[color="#006400"]/* Moved to General Discussion */[/color]