CActiveRecord based on Grouped Table

Archives Yii 1.0.x Discussions
1

Hi,

I am trying to understand if a CActiveRecord can be used on a table when it is grouped. Let me explain a bit.

I am adding auditing to my Yii-based application. So I have an active record called AuditDetail that records each create/update/delete by each user. I want to show a summary for each user showing the number of actions taken in the past week. So my query for the summary will involve a group-by. My question: can I use an active record for this query. I’d rather not use raw SQL because then I have to write custom views and lose all the sorting and other bells and whistles in my Table view class.

The only solution I can think of involves using AuditDetail and somehow having it know whether the underlying query involved grouping. Then it can add in the relevant attributes. Is there an active record can know this (short of setting a flag in the controller)?

Any help is appreciated,

Greg

2

Actually this is not a case where you need a group by. This is a simple log table that include the action taken, the date and a reference to the user that executed it. MAybe some additional information. Everytime a delete, edit, update or insert occurs a new entry is added to that table.

You would simply make a select on that table with the date > now() - 5 days and the id of the user.

If it somehow needs some grouping, i would suggest to create a view in your database and make an active record for that view.

3

I will need to group, because I want to present a one-line summary for each user: how many actions did they take this week. Actually, it’s how many actions did they take this week or 2 weeks, month, quarter, etc for about 10 possible time periods. Is the best solution to create 10 views into the database? I guess I can do that.

But what if I want to allow them to summarize by an arbitrary number of days? I can’t make views for all of them – or is there a way to parametrize a view in MySQL?

thanks,

Greg

4

No you only need on single view for it. Just group by both action and user_id.

If this would be the audit_detail table




CREATE TABLE t1 (

    id  INT auto_increment not null,

    create_date DATETIME,

    action VARCHAR(255),

    user_id INT,

    Primary_key(id)

)

the view could look like




CREATE VIEW grouped_audit AS SELECT * FROM audit_detail GROUP BY action, user_id;

Now you create and ActiveRecord for that table




class GroupedAudit extends CActiveRecord {

  public function tableName()

  {

    return 'grouped_audit';

  }

}

Though you can not actually edit this view with the record but you can simply select from it.




$condition = 'user_id = :userId AND create_date > :date';

$params = array();

$params[':userId'] = <userId>;

$params[':date'] = date("Y-m-d H:i:s", strtotime("-1 WEEK");


//last week

$lastWeek = GroupedAudit::finder()->count($condition, $params);




$params[':date'] = date("Y-m-d H:i:s", strtotime("-1 MONTH");

$lastMonth = GroupedAudit::finder()->count($condition, $params);


//etc.
5

Thanks for the fast coding, Dave. I like your thinking.

I’m only scanning the code here, but once the view groups the rows, I believe the individual create_dates are lost. So the count() cannot be used to figure out how many rows in the group occurred within a week or a month. I could certainly be misreading…

Greg

6

Well it was late yesterday :) But you are right.

The view should look like




CREATE VIEW grouped_audit AS SELECT ad.*, count(ad.action) as amount FROM audit_detail ad GROUP BY action, user_id;

The then you just select the amount from the view and not count the rows.

7

I have no problem iterating past false starts toward a workable solution. I appreciate your putting your head into this, late or not.

I still can’t see how to get a single view to do all the work. In this current case, there is no date involved, so I could not summarize the count for a week or month or whatever.

Greg

8

If there is no date involved your are not able to make a date based summary. No matter what. Without a date you could only limit the result for example to show the last 5 actions taken.

9

Sidenote: I once tried to create an Active Record from a database view, but failed because CActiveRecord requires its parent table to have a primary key defined. If you know of a workaround for this, Dave, I’d love to hear it :)

10

you can override function primaryKey returning the "primary key"

you must create a field of view which is unique

example




CREATE VIEW grouped_audit AS 

SELECT concat(field1,field2) as id, ad.*, count(ad.action) as amount FROM audit_detail ad GROUP BY action, user_id;





public function primaryKey()

    {

            return 'id';

    }

work for me