Yii Framework Forum: LIMIT working with MANY_MANY relations ? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

LIMIT working with MANY_MANY relations ? limit breaks the inner joins Rate Topic: -----

#1 User is offline   yiimann 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 102
  • Joined: 19-December 09

Posted 21 July 2010 - 02:32 AM

Does LIMIT working with MANY_MANY relations ?

I use AR with a many_many relation but when I add the limit => '3' It breaks the mysql query, the joins are removed and ON clause conditions too.

Without limit = 3
		$articles = $this->with(array(
					'categories' => array(
								'on'=>'categories.is_active = 1 AND categories.slug IN ("articlename1", "articlename2","articlename3", "articlename4")', 
								'joinType' => 'INNER JOIN',
					) 
				))->findAll(array(
				'order'=>'t.nombre_vendu DESC',
				'condition'=>'t.is_active=1',
				));


SELECT `t`.`id` AS `t0_c0`, `t`.`marque_id` AS `t0_c1`,
`t`.`nom` AS `t0_c2`, `t`.`description` AS `t0_c3`, `t`.`slug` AS `t0_c4`,
`t`.`reference` AS `t0_c5`, `t`.`prix_ht` AS `t0_c6`, `t`.`tva` AS `t0_c7`,
`t`.`poids` AS `t0_c8`, `t`.`quantite_stock` AS `t0_c9`, `t`.`meta_title`
AS `t0_c10`, `t`.`meta_description` AS `t0_c11`, `t`.`meta_keywords` AS
`t0_c12`, `t`.`nombre_vendu` AS `t0_c13`, `t`.`is_nouveau` AS `t0_c14`,
`t`.`is_accueil` AS `t0_c15`, `t`.`is_slider` AS `t0_c16`, `t`.`is_active`
AS `t0_c17`, `categories`.`id` AS `t1_c0`,
`categories`.`parent_categorie_id` AS `t1_c1`, `categories`.`nom` AS
`t1_c2`, `categories`.`slug` AS `t1_c3`, `categories`.`description` AS
`t1_c4`, `categories`.`image` AS `t1_c5`, `categories`.`meta_title` AS
`t1_c6`, `categories`.`meta_description` AS `t1_c7`,
`categories`.`meta_keywords` AS `t1_c8`, `categories`.`position` AS
`t1_c9`, `categories`.`is_active` AS `t1_c10` FROM `article` `t`  INNER
JOIN `article_categorie` `categories_categories` ON
(`t`.`id`=`categories_categories`.`article_id`) INNER JOIN
`categorie_article` `categories` ON
(`categories`.`id`=`categories_categories`.`categorie_id`) AND
(categories.is_active = 1 AND categories.slug IN ("articlename1", "articlename2",
"articlename3", "articlename4"))  WHERE (t.is_active=1) ORDER BY t.nombre_vendu
DESC









With limit = 3
		$articles = $this->with(array(
					'categories' => array(
								'on'=>'categories.is_active = 1 AND categories.slug IN ("articlename1", "articlename2","articlename3", "articlename4")', 
								'joinType' => 'INNER JOIN',
					) 
				))->findAll(array(
				'order'=>'t.nombre_vendu DESC',
				'limit' => '3',
				'condition'=>'t.is_active=1',
				));


SELECT `t`.`id` AS `t0_c0`, `t`.`marque_id` AS `t0_c1`,
`t`.`nom` AS `t0_c2`, `t`.`description` AS `t0_c3`, `t`.`slug` AS `t0_c4`,
`t`.`reference` AS `t0_c5`, `t`.`prix_ht` AS `t0_c6`, `t`.`tva` AS `t0_c7`,
`t`.`poids` AS `t0_c8`, `t`.`quantite_stock` AS `t0_c9`, `t`.`meta_title`
AS `t0_c10`, `t`.`meta_description` AS `t0_c11`, `t`.`meta_keywords` AS
`t0_c12`, `t`.`nombre_vendu` AS `t0_c13`, `t`.`is_nouveau` AS `t0_c14`,
`t`.`is_accueil` AS `t0_c15`, `t`.`is_slider` AS `t0_c16`, `t`.`is_active`
AS `t0_c17` FROM `article` `t`  ORDER BY t.nombre_vendu DESC LIMIT 3


0

#2 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 21 July 2010 - 02:41 AM

Think about it:

What should the database LIMIT in this case? The result will contain a row for every combination of main table + related table. So if you maybe have 3 main entries, each of them with 10 related items, the result will contain 30 rows. LIMIT always works on the total number of result rows, so it would give something completely unexpected in this case.

That's probably why Yii removes MANY_MANY (and i guess even HAS_MANY) joins from the query, if LIMIT is set.
0

#3 User is offline   yiimann 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 102
  • Joined: 19-December 09

Posted 21 July 2010 - 02:53 AM

Ok, I guess I have to make several requests then.

I think this may also be linked to my other many_many pagination problem.
0

#4 User is offline   Accelm 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 27-March 12

Posted 01 May 2013 - 03:06 PM

If you're not looking to get the models of the related data, I've found a method that's worked for me. I made a post about it here: http://www.yiiframew...any-with-limit/
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users