Level 75
Add in a > 48 in a where. If you don't want to include failed.
Jan 3, 2020
35
Level 1
Laravel Having clause issue.
This is my code below , I am trying to get sum all value where mark should be grater than $passMark(50) with specific username. Like username(jack_89) has 5 subjects and he gets 48 (failed mark) in one subject. I need if any student failed any or more than one subject count will not be happened.
$data = Exam::with('student')
->select('student_username', 'class', 'section', DB::raw('sum(mark) as total'), 'mark')
->where('section', $request->section)
->where('class', $request->sclass)
->where('exam_type', $request->exam_type)
->whereBetween('created_at', [
Carbon::now()->startOfYear(),
Carbon::now()->endOfYear(),
])
->groupBy('student_username', 'class')
->orderBy('total', 'desc')
->having('mark', '>', '$passMark')
->get();
return $data;
its always count all marks even student gets failed marks below 50 , thank you
Level 1
@jlrdw I dont understand, Could you write in details please ?
Level 75
Try first, put order by last, having follows a groupby.
Level 1
@jlrdw same result.
Level 75
You reversed these two lines:
->orderBy('total', 'desc')
->having('mark', '>', '$passMark')
Level 1
@jlrdw after your suggestion I did my code as like as
$data = Exam::with('student')
->select('student_username', 'class', 'section', DB::raw('sum(mark) as total'), 'mark')
->where('section', $request->section)
->where('class', $request->sclass)
->where('exam_type', $request->exam_type)
->whereBetween('created_at', [
Carbon::now()->startOfYear(),
Carbon::now()->endOfYear(),
])
->groupBy('student_username', 'class')
->having('mark', '>', '$passMark')
->orderBy('total', 'desc')
->get();
return $data;
Level 73
Level 75
I think this:
->having('mark', '>', '$passMark')
Needs to be a where clause under the select.
Level 75
Wow @nakov I missed those quotes. Still a having follows a group by. Above was just a copy and paste.
Level 1
Level 73
@masumluf does the variable exists at all? Or just add a number like 50 or whatever the case, otherwise you are comparing it to the string $passMark
Level 75
Change to
->groupBy('student_username', 'class')
->having('mark', '>', 48)
->orderBy('total', 'desc')
->get();
And tell us what happens
And what error
Level 1
Level 73
it exists in the view, not in the controller :)
Level 75
You did not mention what error.
Level 1
@jlrdw no error i can see the result is counting all number even the student failed (got mark below 50)
Level 1
@nakov but it works somehow :O , but i put 50 instead of the variable same result. my query is not skipping failed student, it still counting all result even anybody failed .
Level 75
Try deleting
->having('mark', '>', 48)
And put this in the above part:
->where('section', $request->section)
->where('class', $request->sclass)
->where('exam_type', $request->exam_type)
->where('mark', '>', 48)
Clear browser cache between changes.
Level 1
@jlrdw it is not counting the failed mark, but i can see the student still in the passed student list. :) If student failed any course or more than one course he should not be in count at all. he will be skip. becuase i need only all subject passed student list with sum total number.
Level 75
change sum to count, by using sum, you are getting greater than 48 all the time.
Level 1
@jlrdw same, only one subject which is below 50 marks skipping and if i use count i cant see total marks, it shows only number of mark .
Level 75
You have to use something else as count, like
DB::raw('count(mark) as mycount'), 'mark')
Just play around with what you call things in the query.
Aren't you after a count?
Also do another query, just looking for a failed mark, wouldn't that be easier.
Level 1
Yeah I did samething but no changes .
Level 1
Can I use this query inside eloquent ?
GROUP BY
student_username,
(CASE WHEN mark> 50 THEN 0 ELSE markEND)
Level 75
Okay you need sum, I wasn't sure which.
Level 75
See:
https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
Has a great example of db facade usage:
In my example above did you put 48 or 50.
Level 1
i used 50
Level 75
Remember these types of queries can take some trial and error.
Level 1
@nakov any suggestion?
Level 75
See https://stackoverflow.com/questions/17186677/how-to-use-group-by-after-the-having-clause-mysql
And try:
->groupBy('student_username')
->having('mark', '>', 50)
->groupBy('class')
->orderBy('total', 'desc')
->get();
You are going to have to use some trial and error here.
I suggest you get SQLeo, to help build queries.
Please or to participate in this conversation.