Using Limit In Relational Query Criteria With Cactivedataprovider

Yii 1.1 General Discussion
1

Hi guys,

I have a bit of trouble here getting ‘limit’ to work with CDBCriteria in CActiveDataProvider. I have 2 tables, Author and Books.

The relations is quite straight forward;


Author HAS_MANY Books

and


Books BELONGS_TO Author

.

Here is the ActionIndex method of my AuthorController.php. Basically, I just want to select one Book that belongs to the Author.


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

	'criteria' => array(

		'with' => array(

			'books' => array(

				'select' => 'title',

				'limit' => 1,

			),

		),

		'select' => array('id', 'name'),

		'order' => 't.id DESC',

	),

));

However, this does not seem to work and it return a list of books from that author. Is there I can limit this to only 1?

I have tried adding


pagination => false,

but that does not seem to work as well.

Thanks for your help.

2

What SQL query do you expect to be executed? Try to construct it manually and you will understand why CActiveRecord is not able to generate it. Unfortunately there’s no such SQL construct as “LIMIT 1 FOR EACH author_id”.

Possible solutions:

  • Specify an ON condition that matches exactly one book per author.

  • Create a view in your database that contains only one book per author and define HAS_ONE / BELONGS_TO relationship between Author table and the view.

3

The existing code will query all Authors and on top of that, I would like to display just one book title from the Author, hence limit 1.

However, it does not seem to work.

Any help on this?