With + limit + not lazy load

Archives Bug Discussions
1

Hello everything from a bigges country in the world! Pretime sorry for bad english.




public function relations()

{

    return array(

        'comments' => array(self::MANY_MANY, 'Comment', 'comments2travels(travels_id, comments_id)')

    );

}

and trying execute this as…:




$travel = Travel::model()->with(

    'comments' => array(

        'with' => array(

            'poster'

        ),

        'limit' => 3

)))->findByPk((int)$travelId);

and it give me all comments row instead of 3 first. How to get first 3 rows without lazy load?

2

Try adding together => true (meaning just one query will be generated).

/Tommy

3

You can’t, because there is no SQL that would limit the number of JOINed rows. This is not a Yii limitation but simply not possible with SQL. So you will have to use lazy loading.

4

I see. Now realized the question was about limiting to three comments for each travel, not for the complete dataset.

/Tommy

5

This deserves another look. I’ve been trying to add some simple pagination to a page now for about two hours and it is really frustrating me. I realized that what I was trying at first was similar to the above and was not possible to do in SQL. So I altered my logic and what should work now, does not.

Here is my code:




$Articles = NewsArticles::model()->with(array(

  'categories' => array(

    'select'    => false,

    'condition' => 'newsCategoryID = ' . $id,

  )

))->findAll(array('order' => 'publishDate DESC'));

When I run this, all the Articles are returned for the Category "$id". The SQL generated is:




SELECT `t`.`id` AS `t0_c0`, `t`.`headline` AS `t0_c1`,

`t`.`extract` AS `t0_c2`, `t`.`encoding` AS `t0_c3`, `t`.`text` AS `t0_c4`,

`t`.`publishDate` AS `t0_c5`, `t`.`byLine` AS `t0_c6`, `t`.`tweetText` AS

`t0_c7`, `t`.`source` AS `t0_c8`, `t`.`state` AS `t0_c9`, `t`.`clientQuote`

AS `t0_c10`, `t`.`createdDate` AS `t0_c11`, `t`.`lastModifiedDate` AS

`t0_c12`, `t`.`htmlTitle` AS `t0_c13`, `t`.`htmlMetaDescription` AS

`t0_c14`, `t`.`htmlMetaKeywords` AS `t0_c15`, `t`.`htmlMetaLangauge` AS

`t0_c16`, `t`.`tags` AS `t0_c17`, `t`.`priority` AS `t0_c18`, `t`.`format`

AS `t0_c19` FROM `NewsArticles` `t`  LEFT OUTER JOIN

`NewsArticleCategories` `categories_categories` ON

(`t`.`id`=`categories_categories`.`newsArticleID`) LEFT OUTER JOIN

`NewsCategories` `categories` ON

(`categories`.`id`=`categories_categories`.`newsCategoryID`)  WHERE

(newsCategoryID = 800094410) ORDER BY publishDate DESC

Now when I change my code to (notice the addition of limit):




$Articles = NewsArticles::model()->with(array(

  'categories' => array(

    'select'    => false,

    'condition' => 'newsCategoryID = ' . $id,

  )

))->findAll(array('order' => 'publishDate DESC', 'limit' => 4));

The LIMIT is added, but the OUTER JOIN that keeps only one category showing is removed. This is not the desired result. It is possible for me to take the above SQL statement and simply add "LIMIT 4" to the end and get the desired result directly from MySQL.

Just because a LIMIT is added to the params does NOT mean that all OUTER JOINs must be removed in all cases.

Peace,

Tom