从查询转换为Yii2的ModelSearch [英] Convert from query to ModelSearch of Yii2
问题描述
我是新的Yii2,和我有一个正确的结果的查询:
I'm new in Yii2, and I have a query with right result:
SELECT DISTINCT workloadTeam.project_id, wp.project_name, workloadTeam.user_id, workloadTeam.commit_time, wp.workload_type FROM
(SELECT p.id, p.project_name, w.user_id, w.commit_time, w.comment, w.workload_type
FROM workload as w, project as p
WHERE w.user_id = 23 AND p.id = w.project_id) wp
INNER JOIN workload as workloadTeam ON wp.id = workloadTeam.project_id
但在我ModelSearch.php,我写道:
But in my ModelSearch.php, I wrote:
$user_id = Yii::$app->user->id;
$subquery = Workload::find()->select('p.id', 'p.project_name', 'w.user_id', 'w.commit_time', 'w.comment', 'w.workload_type')
->from(['project as p', 'workload as w'])
->where(['user_id' => $user_id, 'p.id' => 'w.project_id']);
$query = Workload::find()
->select(['workloadTeam.project_id', 'wp.project_name', 'workloadTeam.user_id', 'workloadTeam.from_date', 'workloadTeam.to_date', 'workloadTeam.workload_type', 'workloadTeam.comment'])
->where(['', '', $subquery]);
$query->join('INNER JOIN', 'workload as workloadTeam', 'wp.id = workloadTeam.project_id');
据happended错误:
It happended error:
SELECT COUNT(*) FROM `workload` INNER JOIN `workload` `workloadTeam` ON wp.id = workloadTeam.project_id WHERE `` (SELECT p.project_name `p`.`id` FROM `project` `p`, `workload` `w` WHERE (`user_id`=20) AND (`p`.`id`='w.project_id'))
和我不能用正确的查询,上面固定。 您对此有什么解决办法?
And I can't fix it with right query above. You have any solution about this?
推荐答案
是在Yii开发调试工具栏显示这个错误?那么你的查询(你提到的错误)可能只能从它上市前的查询次数。
Is this error shown in the Yii-debug toolbar? Then your query (which you mentioned as error) is probably only the count from the query which is listed before.
您错过了从添加子查询中子句就像你在你的工作的SQL表示。在
其中,
子句只是放错了地方添加这一点。将子查询中其中,
的条件下,如果你有标量的结果,因为你必须使用这个结果与像 =
,> =
,在
...
You missed to add the sub-query in from
clause like you shown in your working sql. Add this in your where
clause were just the wrong place. Put sub-queries in where
conditions, if you have scalar results, because you have to use this result with operands like =
, >=
, in
...
这可以工作:
$user_id = Yii::$app->user->id;
$subquery = Workload::find()->select([
'p.id as id',
'p.project_name as project_name',
'w.user_id as user_id',
'w.commit_time as commit_time',
'w.comment as comment',
'w.workload_type as workload_type'
])
->from([
'project as p',
'workload as w'
])
->where([
'user_id' => $user_id,
'p.id' => 'w.project_id'
]);
$query = Workload::find()
->select([
'workloadTeam.project_id',
'wp.project_name',
'workloadTeam.user_id',
'workloadTeam.from_date',
'workloadTeam.to_date',
'workloadTeam.workload_type',
'workloadTeam.comment'
])
->from([$subquery => 'wp']); //you were missing this line
$query->join('INNER JOIN', 'workload as workloadTeam', 'wp.id = workloadTeam.project_id');
但是,你没有在主查询中使用的任何选择从工作量
表 $查询
..
由于我不知道什么是你的目标实现,我不能帮你在这个话题...
Since I don't know what's your goal to achieve I can't help you at this topic...
这篇关于从查询转换为Yii2的ModelSearch的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!