FuelPHPのfindの困った挙動

自分のユーザーだけでDebugしてたから全然気づかなかったのですけどページャーを導入したことで激しくバグってました
原因を突き詰めたらfindがおかしなことをしているようだったのでメモを残しておきます

正しく動く場合

~~~
Pagination::set_config($config);

$data[‘posts’] = Model_Post::find(‘all’, array(
‘where’ => array(
array(‘user_id’, $this->user->id),
array(‘status’, 2)
),
‘order_by’ => array(array(‘id’,’desc’)),
‘limit’ => Pagination::$per_page,
‘offset’ => Pagination::$offset,
));
~~~

~~~
SELECT
`t0`.`id` AS `t0_c0`,
`t0`.`comment` AS `t0_c32`,
`t0`.`status` AS `t0_c33`,
`t0`.`user_id` AS `t0_c34`,
`t0`.`created_at` AS `t0_c35`,
`t0`.`updated_at` AS `t0_c36`
FROM
`posts` AS `t0`
WHERE
`t0`.`user_id` = ‘1’
AND `t0`.`status` = ‘2’
ORDER BY
`t0`.`id` DESC LIMIT 20 OFFSET 0
~~~
結果は1件表示されます

relatedを追加した場合

~~~
Pagination::set_config($config);

$data[‘posts’] = Model_Post::find(‘all’, array(
‘related’ => array(‘users’),
‘where’ => array(
array(‘user_id’, $this->user->id),
array(‘status’, 2)
),
‘order_by’ => array(array(‘id’,’desc’)),
‘limit’ => Pagination::$per_page,
‘offset’ => Pagination::$offset,
));
~~~

~~~
SELECT
`t0`.`id` AS `t0_c0`,
`t0`.`comment` AS `t0_c32`,
`t0`.`status` AS `t0_c33`,
`t0`.`user_id` AS `t0_c34`,
`t0`.`created_at` AS `t0_c35`,
`t0`.`updated_at` AS `t0_c36`,
`t1`.`id` AS `t1_c0`,
`t1`.`username` AS `t1_c1`,
`t1`.`password` AS `t1_c2`,
`t1`.`group` AS `t1_c3`,
`t1`.`email` AS `t1_c4`,
`t1`.`last_login` AS `t1_c5`,
`t1`.`login_hash` AS `t1_c6`,
`t1`.`profile_fields` AS `t1_c7`,
`t1`.`created_at` AS `t1_c8`,
`t1`.`updated_at` AS `t1_c9`
FROM
(
SELECT
`t0`.`id`,
`t0`.`comment`,
`t0`.`status`,
`t0`.`user_id`,
`t0`.`created_at`,
`t0`.`updated_at`
FROM
`posts` AS `t0`
WHERE
`t0`.`user_id` = ‘1’
AND `t0`.`status` = 2
ORDER BY
`t0`.`id` DESC LIMIT 20 OFFSET 0
) AS `t0`
LEFT JOIN
`users` AS `t1`
ON
(`t0`.`user_id` = `t1`.`id`)
ORDER BY
`t0`.`id` DESC
~~~
SQLは微妙ですが結果は1件表示されます

ところが

リレーションしたテーブルの条件で絞りたい場合

~~~
Pagination::set_config($config);

$data[‘posts’] = Model_Post::find(‘all’, array(
‘related’ => array(‘users’),
‘where’ => array(
array(‘users.username’, $this->user->username),
array(‘status’, 2)
),
‘order_by’ => array(array(‘id’,’desc’)),
‘limit’ => Pagination::$per_page,
‘offset’ => Pagination::$offset,
));
~~~

~~~
SELECT
`t0`.`id` AS `t0_c0`,
`t0`.`comment` AS `t0_c32`,
`t0`.`status` AS `t0_c33`,
`t0`.`user_id` AS `t0_c34`,
`t0`.`created_at` AS `t0_c35`,
`t0`.`updated_at` AS `t0_c36`,
`t1`.`id` AS `t1_c0`,
`t1`.`username` AS `t1_c1`,
`t1`.`password` AS `t1_c2`,
`t1`.`group` AS `t1_c3`,
`t1`.`email` AS `t1_c4`,
`t1`.`last_login` AS `t1_c5`,
`t1`.`login_hash` AS `t1_c6`,
`t1`.`profile_fields` AS `t1_c7`,
`t1`.`created_at` AS `t1_c8`,
`t1`.`updated_at` AS `t1_c9`
FROM
(
SELECT
`t0`.`id`,
`t0`.`comment`,
`t0`.`status`,
`t0`.`user_id`,
`t0`.`created_at`,
`t0`.`updated_at`
FROM
`posts` AS `t0`
WHERE
`t0`.`status` = 2
ORDER BY
`t0`.`id` DESC LIMIT 20 OFFSET 0
) AS `t0`
LEFT JOIN
`users` AS `t1`
ON
(`t0`.`user_id` = `t1`.`id`)
WHERE
`t1`.`username` = ‘admin’
ORDER BY
`t0`.`id` DESC
~~~

先にlimitで件数を絞った上でwhereの条件がかかるので結果は0件に(´・ω・`)
何が原因だかわからず半日はまりました、そしてこのままで直す方法はわからず…

よくよく考えたらrelatedする必要がなかったのでとりあえずは解決してるんですけどこれがバグなのか仕様なのか
書き方によって解決方法があるのかないのかがわからなくてもやっとしてる感じです

There is a limitation when fetching relations and limiting their output: you can’t use complex where statements on the main object. That are queries using parenthesis for nesting conditions using where_open(). Normal (non-nested) where queries can be applied without problems.
Technically it works like this: to limit the output the query on the base model’s table is actually a subquery with a limit set on it. Any of those nested where conditions are applied on the result of the subquery and further joined relations.

Introduction – Relations – Orm Package – FuelPHP Documentation
とあるので今のところ解決方法はなく、使うなってことなんですかね〜

2013/04/5 追記

CRUD – Orm Package – FuelPHP Documentation

FuelPHP1.4の時からあったかちょっと確認ができませんが1.5では解決できているようです

コメント

タイトルとURLをコピーしました