laravel eloquent 中的条件选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25865132/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional Select Statement in laravel eloquent
提问by Nur Uddin
I have a raw query like that
我有一个这样的原始查询
SELECT IF(`user_group` = '1', `total_score`, `score`) FROM `user`
Now how I can convert this query in laraveleloquentORM
现在我如何在laraveleloquentORM 中转换这个查询
回答by waseem asgar
Convert the MYSQL CASE INTO LARAVEL Query
将 MYSQL CASE 转换为 LARAVEL 查询
$query = DB::raw("(CASE WHEN user_group='1' THEN 'Admin' WHEN user_group='2' THEN 'User' ELSE 'Superadmin' END) as name");
and simply execute this query in
并简单地在
DB::table('tablename')->select($query)->get();
or
或者
YourModelClass::select($query)->get();
You will get the result.
你会得到结果。
回答by Sagar Rabadiya
DB::table('users')->select('IF(`user_group` = '1', `total_score`, `score`)')->get();
this will work
这会起作用
回答by Mihir Bhende
Applicable if you need to have conditional join and select :
如果您需要有条件加入并选择，则适用：
Using mysql's native conditionals can be a good way. You might be in a situation where if a particular condition is truthy in PHP then you need to join that table otherwise do not join.
使用 mysql 的本机条件可能是一个好方法。您可能会遇到这样的情况：如果 PHP 中的特定条件为真，则您需要加入该表，否则不加入。
For example :
例如 ：
If $loggedInUser is admin, then you want to get student attendence otherwise just show marks.
如果 $loggedInUser 是管理员，那么您希望让学生出席，否则只显示分数。
you can have(PS below is a pseudo code just for reference) :
你可以有（PS下面的伪代码仅供参考）：
<?php
// Having the column selection only when a particular condition is true
// Else have its value as NULL(You can have NA also)
if($loggedInUser->role == 'admin'){
$attendanceColumnSelect = DB::raw('attendance.total as total_attendance');
}
else{
$attendanceColumnSelect = DB::raw('NULL as total_attendance');
}
// Students query with joins which must be there always
$studentsQuery= Students::select('name', 'class', 'age', $attendanceColumnSelect)
->join('someothertable', 'someothertable.student_id', '=', 'student.id');
// Adding join of attendance only when required for admin role
if($loggedInUser->role == 'admin'){
$studentsQuery->join('attendance', 'attendance.student_id', '=', 'student.id');
}
// Getting final data
$finalResult = $studentsQuery->get();
?>
If you try to do this way :
如果您尝试这样做：
<?php
$finalResult = DB::select("
SELECT students.name,
students.class,
students.age,
IF('$loggedInUser->role' = 'admin', attendance.total, NULL) as total_attendance
FROM students
INNER JOIN someothertable on someothertable.student_id = student.id
INNER JOIN attendance on attendance.student_id = student.id
");
?>
Then you have to have the attendance join even when you know the condition is false because otherwise it will have 'unknown column attendance.total' error.
然后，即使您知道条件为假，您也必须加入出勤，否则会出现“未知列出席率.total”错误。
From my perspective, if we know we do not want a particular column, I would just not join that table. If you do an EXPLAIN on above raw query, you will find MySQL will need attendance table even when the If condition in select is false.
从我的角度来看，如果我们知道我们不想要某个特定的列，我就不会加入该表。如果您对上述原始查询进行解释，您会发现即使在 select 中的 If 条件为 false 时，MySQL 也需要出勤表。
Please feel free to comment if you find this incorrect or any better suggestions.
如果您发现这不正确或有更好的建议，请随时发表评论。
