I checking the query string from a query and I can see a weird subselect
I have 2 tables: users and userType1 ( users $_has_one userType1)
when I do a search like this
Model_User::find()->related( 'userType1')->where_open()->where( 'userType1.name', 'like', '%a%' )->where_close()
the select looks like this
SELECT * FROM (SELECT * FROM users LIMIT 10 OFFSET 0) AS `t0` LEFT JOIN `userType1` AS `t1` ON (`t0`.`id` = `t1`.`user_id`) WHERE ( `t1`.`name` LIKE '%w%')
why is there a subselect here ?
why not this?
SELECT * users LIMIT 10 OFFSET 0 AS `t0` LEFT JOIN `userType1` AS `t1` ON (`t0`.`id` = `t1`.`user_id`) WHERE ( `t1`.`name` LIKE '%w%')
Also it breaks the pagination as the limit offset should be placed in the main select
Thx guys
Chris
Searching helps as I've explained this about 20 times already. But in short: if you place the limit on the join result you can't fetch to-many relations as they would limit the number of rows instead of the number of returned objects. For those who want to limit the number of rows there is a function rows_limit() you can use instead of limit().
Search one of the older topics if you want to see more about alternatives and more reasoning why this choice was made.
Donate
