Hi,
The following ORM creates this SQL query:
SELECT `t0`.`username` AS `t0_c0`, `t0`.`email` AS `t0_c1`, `t0`.`authentication_token` AS `t0_c2`, `t0`.`remember_token` AS `t0_c3`, `t0`.`created_at` AS `t0_c4`, `t0`.`updated_at` AS `t0_c5`, `t0`.`sign_in_count` AS `t0_c6`, `t0`.`current_sign_in_at` AS `t0_c7`, `t0`.`last_sign_in_at` AS `t0_c8`, `t0`.`current_sign_in_ip` AS `t0_c9`, `t0`.`last_sign_in_ip` AS `t0_c10`, `t0`.`reset_password_token` AS `t0_c11`, `t0`.`reset_password_sent_at` AS `t0_c12`, `t0`.`is_confirmed` AS `t0_c13`, `t0`.`confirmation_token` AS `t0_c14`, `t0`.`confirmation_sent_at` AS `t0_c15`, `t0`.`failed_attempts` AS `t0_c16`, `t0`.`unlock_token` AS `t0_c17`, `t0`.`locked_at` AS `t0_c18`, `t0`.`id` AS `t0_c19`, `t0`.`encrypted_password` AS `t0_c20`, `t1`.`id` AS `t1_c0`, `t1`.`user_id` AS `t1_c1`, `t1`.`old_id` AS `t1_c2`, `t1`.`name` AS `t1_c3`, `t1`.`phone` AS `t1_c4` FROM (SELECT `t0`.`username`, `t0`.`email`, `t0`.`authentication_token`, `t0`.`remember_token`, `t0`.`created_at`, `t0`.`updated_at`, `t0`.`sign_in_count`, `t0`.`current_sign_in_at`, `t0`.`last_sign_in_at`, `t0`.`current_sign_in_ip`, `t0`.`last_sign_in_ip`, `t0`.`reset_password_token`, `t0`.`reset_password_sent_at`, `t0`.`is_confirmed`, `t0`.`confirmation_token`, `t0`.`confirmation_sent_at`, `t0`.`failed_attempts`, `t0`.`unlock_token`, `t0`.`locked_at`, `t0`.`id`, `t0`.`encrypted_password` FROM `users` AS `t0` ORDER BY `t0`.`id` ASC LIMIT 10 OFFSET 0) AS `t0` LEFT JOIN `profiles` AS `t1` ON (`t0`.`id` = `t1`.`user_id`) WHERE (`t0`.`id` LIKE '%sadasd%' OR `t1`.`name` LIKE '%sadasd%' OR `t0`.`email` LIKE '%sadasd%') ORDER BY `t0`.`id` ASC
INSTEAD OF
SELECT `t0`.`username` AS `t0_c0`, `t0`.`email` AS `t0_c1`, `t0`.`authentication_token` AS `t0_c2`, `t0`.`remember_token` AS `t0_c3`, `t0`.`created_at` AS `t0_c4`, `t0`.`updated_at` AS `t0_c5`, `t0`.`sign_in_count` AS `t0_c6`, `t0`.`current_sign_in_at` AS `t0_c7`, `t0`.`last_sign_in_at` AS `t0_c8`, `t0`.`current_sign_in_ip` AS `t0_c9`, `t0`.`last_sign_in_ip` AS `t0_c10`, `t0`.`reset_password_token` AS `t0_c11`, `t0`.`reset_password_sent_at` AS `t0_c12`, `t0`.`is_confirmed` AS `t0_c13`, `t0`.`confirmation_token` AS `t0_c14`, `t0`.`confirmation_sent_at` AS `t0_c15`, `t0`.`failed_attempts` AS `t0_c16`, `t0`.`unlock_token` AS `t0_c17`, `t0`.`locked_at` AS `t0_c18`, `t0`.`id` AS `t0_c19`, `t0`.`encrypted_password` AS `t0_c20`, `t1`.`id` AS `t1_c0`, `t1`.`user_id` AS `t1_c1`, `t1`.`old_id` AS `t1_c2`, `t1`.`name` AS `t1_c3`, `t1`.`phone` AS `t1_c4` FROM `users` AS `t0` LEFT JOIN `profiles` AS `t1` ON (`t0`.`id` = `t1`.`user_id`) WHERE (`t0`.`id` LIKE '%sadasd%' OR `t1`.`name` LIKE '%sadasd%' OR `t0`.`email` LIKE '%sadasd%') ORDER BY `t0`.`id` ASC LIMIT 10 OFFSET 0
$users = Model_User::find()
->related('profile')
->or_where_open()
->where('id', 'LIKE', $like)
->or_where('profile.name', 'LIKE', $like)
->or_where('email', 'LIKE', $like)
->or_where_close()
->limit($limit)
->offset($offset)
->order_by($col, $by)
The problem is, that the LIMIT and OFFSET are in an unnecessary subquery, and are not applied for the whole query.
Can anyone help?
Thanks!
Donate
