Using Relational Active Record on three tables

Yii 1.1 General Discussion
1

Hi guys!

I have three tables in my database:

  • tbl_user

  • tbl_customer_page

  • tbl_page_click

The relations in my models are defined like this:

User:


'customer_page'=>array(self::HAS_ONE, 'CustomerPage', 'user_id')

CustomerPage:


'user'=>array(self::BELONGS_TO, 'User', 'user_id'),

'page_clicks'=>array(self::HAS_MANY, 'PageClick', 'page_id')

PageClick:


'customer_page'=>array(self::BELONGS_TO, 'CustomerPage', 'page_id')

My challenge is to build an Active Record query to get PageClicks where user_id=x. I really stumble on this one as I manage to get data from tables that are directly connected but since I have set no direct relations between the User model and the PageClick model I can’t seem to get data from the PageClick model based on a condition from the User model.

I just figured out that I’m so frustrated I don’t even know if my question makes sense :blink:

Summary: One User has one CustomerPage, and one CustomerPage has many PageClick. I want to get all PageClick from the CustomerPage where User is the logged in user (user id is [font="Courier New"]Yii::app()->user->id[/font]).

Thanks for any help!

2

You don’t tell if you use a CDataProvider but assuming Gii-generated code, you should have a criteria like this in the search() method of the PageClick model (for use with view pageClick/admin):

PageClick.php




$crit = new CDbCriteria(array(

  'with' => array(

    'customer_page' => array(

      'with' => array(

        'user =>' = array(

          'condition' => array('user.id = "'.Yii::app()->user->id.'"'),

        ),

      ),

    ),

  ),

  'together' => true,

));


//usage:

$dp = new CActiveDataProvider(get_class($this), $crit);

(IIRC, not tested now)

/Tommy

3

Hello centauri,

First of: I just woke up, so I might not have full access to my cognitive functions.

As I understand, your problem lies in accessing a page_click array from a users model (I’m guessing for logging purposes).

Assuming that your relations are set up correctly and $user has an instance of a User row.




$user->customer_page->page_clicks;

Will give you an array of all the click rows the customer page has. You can do this because both BELONGS_TO and HAS_ONE return a single instance so you can directly call relations on them.

Take note though that the above php line will take some resources out of your app. To avoind unnecessary lagging better set with parameters like tri shows or, if you just need a pageview count, use a column in customer_page to increment an INT.

4

Thanks, so far so good! I have managed to extract the data needed based on the user id inside my controller class like this:


$criteria=new CDbCriteria(array(

    'condition'=>'user_id='.Yii::app()->user->id,

    'with'=>'page_clicks',

));

		

$dataProvider=new CActiveDataProvider('CustomerPage', array(

    'criteria'=>$criteria,

));

		

$data=$dataProvider->getData();

But still I can’t wrap my head around Active Record: How do I present the [font=“Courier New”]$dataProvider->getData()[/font] object in a view file? E.g. if I would like to have all page_clicks in an array. Is there an elegant way of doing this without writing tons of code?

Thanks for the help! Really appreciate it :)

5

What kind of view file? Gii-generated? Which one of them? Did you try what I suggested?

/Tommy

6

the render() function accepts an array of variables that will be acceptable in the view file.

so if you have a view called list (protected/views/controller/view.php) you can:





<?php 

   // in controller action

   // $dataprovider is the CDataprovider object you already created


$this->render( 'list', array(

   'dataprovider' => $dataprovider,

   'somethingelse' => $otherVar

));




   // in view file: list.php

   

   echo '<h1>'.$somethingelse.'</h1>';   


   foreach ( $dataprovider->data as $object ){

      // do something

      $this->renderPartial( 'someotherView', array( 'model' => $object ) );

   }

?>
7

I did not use the Gii CRUD generator and I will narrow my query more later on :) However, for the time being my query retrieves the data I want (customer_page based on user id and customer_page’s page_clicks based on page id) from the database and returns it like this ([font=“Courier New”]var_dump()[/font] in a view file):


array(1) {

  [0]=>

  object(CustomerPage)#59 (12) {

    ["_md":"CActiveRecord":private]=>

    object(CActiveRecordMetaData)#29 (5) {

      ["tableSchema"]=>

      object(CMysqlTableSchema)#33 (9) {

        ["schemaName"]=>

        NULL

        ["name"]=>

        string(17) "tbl_customer_page"

        ["rawName"]=>

        string(19) "`tbl_customer_page`"

        ["primaryKey"]=>

        string(2) "id"

        ["sequenceName"]=>

        string(0) ""

        ["foreignKeys"]=>

        array(0) {

        }

        ["columns"]=>

        array(3) {

          ["id"]=>

          object(CMysqlColumnSchema)#34 (14) {


        // etc.

How can the above be presented in a view file at all?

E.g. like this:

Customer ID: 1

Customer Page: /superstore

Page Clicks: 1234 times

8

If you just want to display the click count you can add a STAT relation to CustomerPage.

Note, it’s not mandatory to create a dataprovider.

Not tested now, but since you don’t want to write too much code, you should be able to start from User and lazy load the rest. In this case you shouldn’t have to use eager loading in order to specify the condition.




$model = User::model()->find('id = '.Yii::app()->user->id)


//usage:

$model->customer_page

$model->customer_page->page_clicks

/Tommy

9

Unfortunately that doesn’t work because customer_page has the [font=“Courier New”]HAS_MANY[/font] relation to page_clicks. If I change to [font=“Courier New”]HAS_ONE[/font] it does work, but page_clicks should return an array.

If customer_page has the relation [font="Courier New"]HAS_MANY[/font] to page_clicks I get the following PHP error when trying to access [font="Courier New"]$user->customer_page->page_clicks[/font]: [font="Courier New"]Trying to get property of non-object[/font]

10

I see. Perhaps I misunderstood your view example.

You will have to iterate over page_clicks.




foreach ($model->customer_page->page_clicks as $page_clicks)

  echo $page_clicks->...

/Tommy

11

Thank you so much! :D