我需要使用带有PHP ActiveRecord的自定义查询,因为我需要UNION
语句,即使我在两个查询中都查询同一个表。我希望能够在结果中包括我在模型上作为belongs_to
的列。
这是我的型号代码:
class FeedActivity extends MyModel {
static $table_name = 'feed_activity';
static $belongs_to = array(
array('user_reference', 'class_name' => 'User', 'foreign_key' => 'user_reference_id'),
array('follow', 'class_name' => 'Follow', 'foreign_key' => 'follow_id'),
array('challenge_user', 'class_name' => 'ChallengeUser', 'foreign_key' => 'challenge_user_id'),
);
}
我的问题是:
$list = FeedActivity::find_by_sql('
(
SELECT
*
FROM
feed_activity
WHERE
challenge_user_id IS NOT NULL
)
UNION ALL
(
SELECT
feed_activity.*
FROM
feed_activity
JOIN
follow ON follow.id_follower = ' .(int) $user_id . '
AND follow.id = feed_activity.follow_id
WHERE
follow_id IS NOT NULL
LIMIT 0, 5
)
ORDER BY created_at DESC
LIMIT 0, 10
');
有没有想过我该怎么做?
感谢
睡了一夜之后,我找到了一个快速简单的解决方案。我对MyModel
(我所有模型的超类)实现了以下方法:
public static function find_by_sql_including($sql, $include = null)
{
return static::table()->find_by_sql($sql, null, true, $include);
}
现在我做这样的查询:
FeedActivity::find_by_sql($sql, array('user_reference','follow','user_challenge'));