24942

How to use conditional for this query in Laravel 5.2

Question:

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 <hr />

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

Answer1:

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();

Recommend

  • Accessing a html page that is in a tab of a html page using Windows Phone 8 app
  • Cannot find a differ supporting object '[object Object]'
  • Pandas ordered categorical data on exam grades 'D',…,'A+'
  • Splitting time series data into groups based on a changes in state on a column in a python pandas da
  • How to create a UITableView with editable components?
  • Data.table: Add rows for missing combinations of 2 factors without losing associated descriptive fac
  • How to stop tomcat 7 with maven in eclipse
  • Search Google Drive folder from Google Site using Apps Script
  • jquery pie chart with drilldown/clickable pie parts
  • Do I have to rewrite an html header everytime I want to use it?
  • Grouping by blank nodes
  • Insert records if not exist SQL Server 2005
  • Creating UDF with VSTO in Excel
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • how to query for min or max inet/cidr with postgres
  • Selectively hide background elements when overlayed with transparent div
  • Unique Permutations - with exceptions
  • sweetalert2 inputoptions from file in select example
  • Cloud Code function running twice
  • Jquery popup on mouse over of calendar control
  • Button text different than value submitted in query string
  • Alternative to overridePendingTransition() - Android
  • Is there a perl module to validate passwords stored in “{crypt}hashedpassword” “{ssha}hashedpassword
  • Rails Find when some params will be blank
  • MongoError: Incorrect arguments
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • Dialing with Intent.ACTION_CALL stopps at # in phone number
  • Highlight one bar in a series in highcharts?
  • Lost migrations and Azure database is now out of sync
  • Is there any way to access browser form field suggestions from JavaScript?
  • Android screen density dpi vs ppi
  • Dynamically accessing properties of knockoutjs observable array
  • script to move all files from one location to another location
  • WinForms: two way TextBox problem
  • Can I make an Android app that runs a web view in Chrome 39?
  • Turn off referential integrity in Derby? is it possible?
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF