Display A Single Record Based On Related Model Values

This seems a ridiculously basic question to ask but I am just not grasping the way to harness ActiveRecord in Yii.

The basic setup (a single example which will inform many other instances):

model Record with id, slug, name, etc.

model recordDetail with id, slug, name, recordId, recordType, sort, startDate, endDate, etc.

I know relations are set up correctly because a). I used Gii for CRUD scaffolding and B). I have the administrative end of the application working in fine fashion, including filtering the recordDetail so that it only operates within the context of a Record passed in the URL (borrowed from the Agile Yii book). I also have slugs set up correctly to load a model into a view based on the slug rather than the id, for SEO purposes.

What I am trying to do now is create a “Featured Record” view that will load a single Record based on criteria found in the recordDetail model: recordType=‘Featured’, startDate < $now and endDate > $now ($now being a variable passed from a function that derives a DateTime value from time() and adjusts for setTimeZone. I figured that these criteria would give me values of a certain record type, which has started and has not ended, which combined with order=‘sort’ and limit=1 would give me a single record at the top of the sort list.

I understand that all I need to do is derive the recordId number from this table and pass it to a Record model using findByPk($recordId)). I just can’t seem to DO it. I am attaching a few code samples of various methods I’ve tried by researching forum threads, the Definitive Guide, and various other resources:

for reference, the getCurrentTime function that will pop up here and there:


	public function getCurrentTime()

	{

	Yii::app()->setTimeZone("America/New_York");

	$now = date("Y-m-d\TH:i:s\Z", time());

	return $now;

	}



I have tried a named scope from recordDetail model:


public function scopes()

	{

		return array(

			'today'=>array(

				'condition'=>"recordType='Featured' AND startDate < '".$this->getCurrentTime()."' AND endDate> '".$this->getCurrentTime()."'",

				'order'=>'sort ASC',

				'limit'=>1,

			),

		);

	}

combined with this function in the RecordController:


	public function loadTodayRecord($recordId)

	{

		$model = Record::model()->with('recordDetails:today','other related records)->findAll();

		if($model===null)

			throw new CHttpException(404,'The requested Record could not be found.');

		return $model;

	}

and then render the view with this function in RecordController:


	public function actionFeatured($id)

	{

		$record=$this->loadTodayRecord(true);

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

			'model'=>$record,

		));

	}



I have also tried the more verbose form of eager loading:


$model = Record::model()->findAll(array(

	'with'=>array(

		'recordDetails'=>array(

			'scopes'=>array(

				'today',

			),

		),'other related tables'

	),

));

with the same (lack of) result.

I have tried using CDbCriteria in RecordController as follows:


	public function getTodayRecord()

	{

		$q='Featured';

		$r=$this->getCurrentTime();

		$criteria = new CDbCriteria;

		$criteria->condition = 'recordType = :q AND startDate < :r AND endDate > :r';

		$criteria->order = 'priority';

		$criteria->limit = 1;

		$criteria->params = array(':q'=>$q,':r'=>$r);

		$model = RecordDetail::model()->findAll($criteria);

		

		if(!empty($model)) {

			return $model->recordId;

	}

and then using that value to load the model:


public function loadTodayRecord($recordId)

	{

		$recordId = $this->getTodayRecord();

		$model = Record::model()->with('recordDetails','other related records')->findByPK($recordId);

		if($model===null)

			throw new CHttpException(404,'The requested Record could not be found.');

		return $model;

	}

the actionFeatured function does not render that, either.

There have been other similar things I have tried but I don’t have all the various code snippets from all my other efforts, as they have been put up and taken down over the last 18 hours. They were very much like these, trying to filter my model using scopes, or find a way to pass the recordId into the model load.

Some of them returned a blank page, which left me unsure if the result was a null array, or an array with multiple values that could not be rendered singly. Trying to print_r various attributes, e.g. $model->name, gave no result either, and count($model->name) often returned 0. count($model) would give me a count of around 380, but print_r($model) just freezes the page on load. Oh, and sometimes I would get a Division By Zero error on variables that made calculations based on model attributes, further suggesting no proper returned values.

These more recent efforts have rendered the page with a 400 error, "Your request is invalid", which is a similar result to trying to do a Yii-standard actionView without passing an id in the URL. Adding an id in this instance returns the context error above: "The requested Record could not be found" but if I change the render to just a regular loadModel and put an id into the URL, the page renders correctly.

So I know the page is doing its job when it has an id to work with. I just can’t get it to go find the recordId I want and use that for the value.

I know I have gone on for a while but I wanted to provide whatever information I could. I have been at this problem for a while with no result and no direction on where to go next, and this is a critical project. If someone could point me in the right direction to get the result I need, I would appreciate it greatly.

Way too much code and text for me to look through, but here’s an issue that was immediately obvious:




                $model = RecordDetail::model()->findAll($criteria);

                

                if(!empty($model))

                        return $model->recordId;



You’re doing a findAll() request, which always returns an array of model objects. If you’re intending to get a recordId for the first model, you need to use:




return $model[0]->recordId;



Changed CDbCriteria function to return $model[0], got same result (Your Request is Invalid)

I used findAll because in the instances where I just tried to sort and filter and then do a find(), it always just returned the first Record by id regardless.

Also, I have been using the ActiveRecord->findAll() method because my research has led me to believe that using CActiveDataProvider is designed to return CGridView and CListView, which I’m pretty sure I don’t want: I just want access to the data so I can dynamically populate a web page.

And while I do need to find a solution for a single record, I am also going to need to return multiple results to build other pages, so what interests me most is just finding the correct method to get record::model based on recordDetail->recordId with filters to refine my results, as a general practice.

[s]Another problem:


$model = Record::model()->with('recordDetails:today','other related records)->findAll();

with() should be receiving an array of relations, not multiple separate parameters.[/s]

Sorry, just checked it and variable parameters are acceptable.

Have you read through the Yii guide in full?

It also might be worth trying to get more verbose error reporting. Can you see which line is causing the invalid request error message? Turn on the web logging in your config to get debugging info:




		'log'=>array(

			'class'=>'CLogRouter',

			'routes'=>array(

				array(

					'class'=>'CFileLogRoute',

					'levels'=>'error, warning',

				),

				// uncomment the following to show log messages on web pages

				array(

					'class'=>'CWebLogRoute',

				),

			),

		),



I’m not sure I understand this response.

The example in the guide outlines extended eager loading like so:

$posts=Post::model()->with(‘author’,‘categories’)->findAll();

My code above that mirrors this example exactly, with each related model in single quotes separated by commas. I truncated the list of loaded relations because just this one is relevant to the problem that I’m having, and ostensibly, knowing how to filter on one value will show me how to filter on others.

I have not read the entire guide in detail from front to back, though I have been through most of it at some point or another while learning the framework. I HAVE read the sections on Active Record and Relational Active Record in reference to this question, and if you will observe, most of the solutions I have tried are lifted more-or-less directly from the examples given in the guide:

  • setting up a scope to refine the relationship to the child model

  • using CDbCriteria to query a table and return a specific result

  • doing a FindAll with eager loading using multiple methods of filtering results, including passing the related scope in the FindAll(array).

I wrote this post because the resources at hand have not led me to a solution, and I don’t understand the process well enough to debug my problems. I won’t make the argument about whether the documentation is not clear/extensive enough or if I’m just not grasping the concept. I just want to understand how Yii combs through related data so I can devise the solutions I need.

Abstracting data through normalisation is (obviously) a good development tool for flexibility in an app, and Yii clearly is stellar at understanding and plumbing the depths of interrelated tables. Given a parent id, I’m thrilled with the number of levels I can drill down when rendering a page. But there also has to be a way to start from a child value with certain parameters and derive a parent id from that. It’s easy enough to do with standard MySql joined and even union queries. I’m just frustrated that I’m not seeing something that I’m certain is entry-level stuff.

I had forgotten about verbose page logging, thank you for pointing me to that.

It didn’t really give me any helpful information, sadly. Running through the stack, it eventually arrived at:


#9

C:\Users\Public\Documents\Sites\mySite\framework\web\CWebApplication.php(142):

CWebApplication->runController('record/featured')

#10

C:\Users\Public\Documents\Sites\mySite\framework\base\CApplication.php(162):

CWebApplication->processRequest()

#11 C:\Users\Public\Documents\Sites\mySite\root\index.php(13):

CApplication->run()

#12 {main}

REQUEST_URI=/record/featured

Which, as far as I can tell, means it tried to render record.featured and stumbled because it could not load the model. This is consistent with trying to do a standard record.view without passing an id in the URL, which suggests I’m not getting the parent id into the request correctly. Which is the root of this whole problem.

Can you post the full error output here?

If you think it will help. I wanted to spare you several pages of unrelated rbac garbage.


Timestamp 	Level 	Category 	Message

16:12:15.716325 	trace 	system.CModule 	


Loading "log" application component

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.734313 	trace 	system.CModule 	


Loading "bootstrap" application component

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.735618 	trace 	system.CModule 	


Loading "clientScript" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(103)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(84)

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.737386 	trace 	system.CModule 	


Loading "assetManager" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(328)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(103)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(84)


16:12:15.738631 	trace 	system.CModule 	


Loading "request" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(328)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(103)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\extensions\bootstrap\components\Bootstrap.php

(84)


16:12:15.899280 	trace 	system.CModule 	


Loading "urlManager" application component

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.918242 	trace 	system.web.filters.CFilterChain 	


Running filter DealController.filterrights()

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.919193 	trace 	system.CModule 	


Loading "user" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RightsFilter.php

(24)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RController.php

(36)

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.922498 	trace 	system.CModule 	


Loading "session" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RightsFilter.php

(24)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RController.php

(36)

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.924988 	trace 	system.CModule 	


Loading "db" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RightsFilter.php

(24)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RController.php

(36)

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.926968 	trace 	system.db.CDbConnection 	


Opening DB connection

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RightsFilter.php

(24)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RController.php

(36)

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.941617 	trace 	system.db.CDbCommand 	


Querying SQL: SELECT `data`

FROM `pp3_yiisession`

WHERE expire>:expire AND id=:id

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RightsFilter.php

(24)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RController.php

(36)

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.945602 	trace 	system.CModule 	


Loading "coreMessages" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RController.php

(36)

in C:\Users\Public\Documents\Sites\mySite\root\index.php (13)


16:12:15.948777 	error 	exception.CHttpException.400 	


***exception 'CHttpException' with message 'Your request is invalid.' in

C:\Users\Public\Documents\Sites\mySite\framework\web\CController.php:337***

Stack trace:

#0

C:\Users\Public\Documents\Sites\mySite\framework\web\CController.php(310):

CController->invalidActionParams(Object(CInlineAction))

#1

C:\Users\Public\Documents\Sites\mySite\framework\web\filters\CFilterChain.php(134):

CController->runAction(Object(CInlineAction))

#2

C:\Users\Public\Documents\Sites\mySite\framework\web\filters\CFilter.php(41):

CFilterChain->run()

#3

C:\Users\Public\Documents\Sites\mySite\root\protected\modules\rights\components\RController.php(36):

CFilter->filter(Object(CFilterChain))

#4

C:\Users\Public\Documents\Sites\mySite\framework\web\filters\CInlineFilter.php(59):

RController->filterRights(Object(CFilterChain))

#5

C:\Users\Public\Documents\Sites\mySite\framework\web\filters\CFilterChain.php(131):

CInlineFilter->filter(Object(CFilterChain))

#6

C:\Users\Public\Documents\Sites\mySite\framework\web\CController.php(292):

CFilterChain->run()

#7

C:\Users\Public\Documents\Sites\mySite\framework\web\CController.php(266):

CController->runActionWithFilters(Object(CInlineAction), Array)

#8

C:\Users\Public\Documents\Sites\mySite\framework\web\CWebApplication.php(283):

CController->run('featured')

#9

C:\Users\Public\Documents\Sites\mySite\framework\web\CWebApplication.php(142):

CWebApplication->runController('record/featured')

#10

C:\Users\Public\Documents\Sites\mySite\framework\base\CApplication.php(162):

CWebApplication->processRequest()

#11 C:\Users\Public\Documents\Sites\mySite\root\index.php(13):

CApplication->run()

#12 {main}

REQUEST_URI=/record/featured

---


16:12:15.949518 	trace 	system.CModule 	


Loading "errorHandler" application component


16:12:15.960043 	trace 	system.CModule 	


Loading "themeManager" application component

in

C:\Users\Public\Documents\Sites\mySite\root\protected\controllers\SiteController.php

(45)


16:12:15.963887 	trace 	system.CModule 	


Loading "widgetFactory" application component

in

C:\Users\Public\Documents\Sites\mySite\root\themes\myTheme\views\layouts\column1.php

(2)

in

C:\Users\Public\Documents\Sites\mySite\root\protected\controllers\SiteController.php

(45)

Not sure if the question is raised by the output but I have rights module installed, and deal.featured permissions are assigned to guest/authenticated/registered, in addition to which I am logged in as admin, so I know it’s not permissions. Also, I am able to load the page when I do a standard loadModel and pass an id in the URL, as if I were doing a regular view. It fails when I try to get a parent id from child criteria.

As you say, you’re getting an error because you’re not providing the id attribute. Explain to me again how you’re running this action. Logically, it should be one of:

  • The user clicks a link (or visits a URL).

  • The user is redirected from one page to another.

  • The request is performed over ajax.

Just visiting site.com/record/featured. So, I guess, the first one most often, and the second one occasionally (if this works I will probably make this the home page).

If I were doing a SQL query I would say (in pseudo-code):


SELECT record.*, recordDetail.* from record INNER JOIN recordDetail on record.id = recordDetail.recordId WHERE recordType='Featured' AND startDate < NOW() AND endDate > NOW() ORDER BY sort LIMIT 1

And then start echoing values based on the results of the query.

Obviously, the ActiveRecord feature of Yii is way more powerful and that’s crucial for me cause it spares me joining all the other related tables that I left out of this example. I just can’t seem to figure out the right function to run a query like that, or even one that queries recordDetail on the same WHERE/ORDER BY/LIMIT parameters and then passing the resulting recordId to the parent model.

[color="#006400"]/* Removed ‘Urgently needed help’ from the topic title. All help request are urgent, right? */[/color]

Not to belittle the needs of anyone else but I have asked for help in other forums for one issue or another over the years and never presumed to label it urgent in the title, much less sit on the topic and refresh it every five minutes. So this is kind of a once-in-a-lifetime redline issue for me.

Having said that, I have no complaint about editing the title at this point, as Keith has been extremely responsive throughout the morning, allaying my fears that it might sit with 0 replies for several days, or even forever, as has happened on other sites.

Do you need to be able to visit the page both with and without the id specified? If so, what should be the behaviour when the id is omitted?

I actually do not desire the id to be called out in the URL at all, but to have it examine the list of available details and return the relevant record(s).

To borrow from the Guide example, it would be as if going to post/featured would return a post with recent comments. The comment table would have the relevant data for filtering and sorting, but the post model is what you want to load. You should be able to derive the postId from the comment data.

Similarly, if you wanted a page of "hot topic" posts, then you would want an array of posts drawn from the same comment criteria, but returning more than one post to loop through and display on the page.

Another approach as I understand the way Yii works is to think of it as loading the post model ‘with’ the comment model, filtered and sorted on fields joined from the comment model. But it wasn’t clear to me if there’s a way to do that directly from the parent ‘post’ model.

I have been able to enact a (partial) solution through IRC interaction.


public function actionFeatured()

{

	$record=$this->loadFeaturedRecord();

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

		'model'=>$record,

	));

}

First off, I made the mistake of loading a variable in the function itself, which if I understand correctly, was causing it to look for one passed in the URL. So it basically would never look at the model I was trying to load without a reference from that.


public function loadFeaturedRecord()

{

	$sql = "select recordId from recordDetail where `recordType` = 'Featured' AND `startDate` < '".$this->getCurrentTime()."' AND `endDate` > '".$this->getCurrentTime()."' ORDER BY `sort` LIMIT 4";

	$children = Yii::app()->db->createCommand($sql)->queryColumn();

	$criteria = new CDbCriteria;

	$criteria->addInCondition('t.id',$children);

	$model = Record::model()->isEnabled()->with('recordDetails','other related tables')->findAll($criteria);

	if($model===null)

		throw new CHttpException(404,'The requested Record could not be found.');

	return $model;

}

This seems to be filtering the data properly (isEnabled is a scope of Record model that filters for enabled boolean column is 1) but it still seems to order the results by the original Record id and not the sort column of Detail as listed in the SQL statement. The LIMIT function does work correctly, and I have it set to 4 so I can show the next three items of the list in a "related" box in the second column.

This seems like an inadequate solution for several reasons:

1). it relies on basically a direct SQL query of the data where it seems that this is the kind of thing ActiveRecord should handle without breaking a sweat

2). the Relational AR section of the Guide suggests that I can apply named scopes from related models in a way similar to what I did here with the named scope of the parent model, but the various methods of calling out those filters like the Guide examples does not filter the result. I may open another thread asking about what I’m doing wrong re: scopes, since it at least gave me the proper result in the parent model, suggesting I’m on the right track

3). improper ordering of returned array is obviously still a significant concern.

My thanks to Keith for helping me walk through portions of my problem here, and for the IRC folks who brought me the last mile from there to my quasi-solution.

UPDATE:

I have gotten one step closer. "together" seems to have been a sticking point for me previously. I can now get better results with this function using named scopes in related models:


public function loadFeaturedRecord()

{

	$sql = "select recordId from RecordDetail where `startDate` < '".$this->getCurrentTime()."' AND `endDate` > '".$this->getCurrentTime()."'";

	$children = Yii::app()->db->createCommand($sql)->queryColumn();

	$criteria = new CDbCriteria;

	$criteria->addInCondition('t.id',$children);

	$model = Record::model()->isEnabled()->with(array(

		'recordDetails'=>array(

			**'scopes'=>array('isFeatured', 'isSorted'),**

			***'together'=>true,***

		),'other related tables'))->findAll($criteria);

	if($model===null)

		throw new CHttpException(404,'The requested Record could not be found.');

	return $model;

}

isFeatured is a scope that replaces the recordType condition previously set forth in the query, and the isSort scope orders the results by the sort order in the related table.

The sql query is still required because the isCurrent scope is not correctly returning results within the start/end date parameters. I’m not sure if that’s because I set up the function wrong or simply cannot pass that value into a scope like this. I also can’t seem to get a result by placing a limit in the isFeatured scope. I can still set a limit in the SQL query that grabs records in the date range but then I have to order those results or it will just give me the first three records by recordId, and I would have to put the ORDER BY back into that query.

It’s almost more maddening to be this close to getting my head around it than a couple days ago when I felt completely lost, but at least I feel a little more like I was on the right track using scopes to filter the models when calling CActiveRecord. I just need to figure out why my dates aren’t working and I can dispense with the SQL query altogether and just use ActiveRecord.

Final Update: I was able to achieve precise results for my pages using ActiveRecord with no SQL statements or "hand-written" criteria:


public function loadFeaturedRecord()

{

	$model = Record::model()->isEnabled()->isCurrent()->inCategory()->with(array(

		'recordDetails'=>array(

			'scopes'=>array('isFeatured'),

			'order'=>'sort ASC',

		),'other related tables'))->findAll();

	if($model===null)

		throw new CHttpException(404,'The requested Record could not be found.');

	return $model;

}

The final piece I was missing came to me as I was trying to get the page to filter even further by category. I started by trying to set up a regular named scope in the category model. When that did not work, I tried using a parameterized scope. But I could not figure out a way to pass a parameterized scope in the array I used above to get my recordDetails to filter correctly.

What I did instead was to set up a parameterized scope for the related model from inside the parent model:


public function inCategory($category=false)

{

	if($_GET['category'])

		$this->getDbCriteria()->mergeWith(array(

			'condition'=>"recordCategories.`slug` = '".$_GET['category']."'",

		));

	return $this;

}

where recordCategories is the name of the relation as specified in the model, not the explicit name of the related table.

With that done, I can use the parameterized scopes in the beginning of the model load, e.g. ->inCategory()-> as long as I remember to include the referenced related model in the ->with() array.

The moral of the story: it appears that named scopes can be included in the scopes() function so long as you are querying on a static value, whereas if you are getting a variable from another function, you need to use a parameterized scope, and if you spell out the model the way I did above, that scope needs to originate in the parent model - not the child model - and use the relation name for disambiguation.

Also, you may note that "together" is now back out of the code. Having taken the LIMIT out of these models, it was no longer necessary to include "together" to get the right results.

hi friends you can also used

foreach($user->records(array(‘limit’=>4,‘order’=>‘id DESC’)) as $model){

                    &#036;this-&gt;renderPartial('/user/_record',array('model'=&gt;&#036;model));


                }

on rendering the form it will show only 4 entries.

Hi

You can actually nest ‘with’ clauses and add them as critéria or ‘with’ conditions or ‘find’ condition parameter.

Here is an example with ‘scope’, but I have complex ones in my main project.


public function hasExpiredNoMovementAlerts($timelimit=null) {

        if($timelimit===null) $timelimit=time();

        $criteria=$this->getDbCriteria();

        $criteria->mergeWith(

                array(

                        'with'=>array(

                                'activeEntityAlerts'=>array(

                                        'with'=>array(

                                                'alert'=>array(

                                                        'scopes'=>'no_movement',

                                                        'joinType'=>'INNER JOIN',

                                                ),

                                        ),

                                        'condition'=>"`activeEntityAlerts`.`state` IS NOT NULL AND (`activeEntityAlerts`.`state` + `alert`.`alert_memo` < :tlim)",

                                        'params'=>array(':tlim'=>$timelimit,),

                                        'joinType'=>'INNER JOIN',

                                ),

                        ),

                )

        );

        return $this;

[size=2]    }[/size]

It is this technique which allowed me to create my RelatedSearchBehavior extension efficiently.