CODEWITHSUNDEEP
phpmysqlcontrollerlaravel-5
Rodney Zanoria
Rodney Zanoria

Reputation: 494

How to use conditional for this query in Laravel 5.2

Here is the scenario

Table: Users

id   | name  | grade  | subject
---- | ------|--------|------
1    | Mark  |   a    |   science
2    | Earl  |   a    |   english
3    | John  |   c    |   english
4    | Mike  |   d    |   science
5    | Matt  |   e    |   english

What I want to do is populate the grades that are non-repeating or non-duplicate grades but for english subject only

So it should just show

c
e

I've got this far

controller:

$grades = user::select('grade', DB::raw('COUNT(grade) as gradecount'))
->where('subject', 'english')
->groupBy('grade')
->having('gradecount', '=',1)
->get();

blade:

@foreach ($grades as $grade)
            {{ $grade }}
            @endforeach

However, this query gives this

a
c
e

Upvotes: 1

Views: 191

Answers (1)

Jessica Legner
Jessica Legner

Reputation: 36

You'll want to do something like this: 

SELECT grade
FROM Users
WHERE id IN (
    SELECT id 
    FROM Users
    GROUP BY grade
    HAVING COUNT( grade ) = 1
) AND subject = 'english'`

Query builder has a ->whereIn that you should be able to use. Too lazy to test, but something like this may work.

$ids = user::select('id')
->groupBy('grade')
->having('gradecount', '=',1)
->get();

$grades = user::select('grade')
->whereIn('id', $ids)
->where('subject', 'english')
->get();

Upvotes: 2

Related Questions