How to write sub query sql in laravel 4.2

Question

hy I'm new to Laravel 4.2 And I have Sub Query Sql Like this

Select *, Count(*) as total_rows From(
SELECT test_id, 
SUM(IF(app.status='complete',apt.result,0)) AS complete_sum, 
SUM(IF(app.status='process',apt.result,0)) AS process_sum  
FROM application_test AS apt 
JOIN application AS app ON app.id=apt.application_id 
GROUP BY apt.test_id)

my quetion is how I can write this sub query sql on laravel 4.2

Possible duplicate of [How to select from subquery using Laravel Query Builder?](http://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder) — Bogdan, Jan 03 '16 at 11:19

score 0 · Answer 1 · answered Jan 03 '16 at 11:30

0

You can make subqueries like this.

Test::where('id', function($query){
    $query->select('other_test_id')
    ->whereIn('category_id', ['223', '15'])
    ->where('active', 1);
})->get()

If you're new on Laravel, consider to use the last version of it, the 5.2 ;)

answered Jan 03 '16 at 11:30

Jiedara

516
1
4
12

The subquery is for the `FROM` statement, there's no `WHERE` in that query. You answer does not offer a solution or guidance for this particular question regarding subqueries. – Bogdan Jan 03 '16 at 11:34
I wasn't making the query, I was giving an example of subquery :) – Jiedara Jan 03 '16 at 11:36
I understand, but it may be misleading for inexperienced users. Because while the `where` method of the Query Builder offers the option to create a subquery via a closure, the `table` method (which would be used in this case) does not. Raw queries are needed here, which is why the question is flagged as a duplicate, because this has been asked and answered before. – Bogdan Jan 03 '16 at 11:39
@Bogdan very true, so my answer really is confusing ... :/ – Jiedara Jan 03 '16 at 11:43

score 0 · Answer 2 · answered Sep 05 '16 at 05:53

Convert the MYSQL CASE INTO LARAVEL Query

$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.

Note: Syntax is based on Laravel 5.0

How to write sub query sql in laravel 4.2

2 Answers2