How to convert this sql query into laravel query builder?

Question

I had a table(itemregistrationpangkat) with name(joined from other table itemregistrations), negeri(joined from other table named negeri) and year of start service and end service. I want to get the number of years a person served in a state:

  ID | Name     | negeri   |   yearstart     |    yearend
  -----------------------------------------------------------
  1  | Tom      | Kedah   |      2001       |      2002
  1  | Tom      | Kedah   |      2003       |      2007
  2  | Anne     | Melaka  |      2008       |      2012
  2  | Anne     | Melaka  |      2013       |      2018
  3  | Bill     | KL      |      2000       |      2001

I had to ask someone how to make a SQL statement for querying the information. This is the SQL statement which works like this fiddle http://sqlfiddle.com/#!9/9029438/4(this fiddle assumes that negeri and name column is in the same table):

  select m.id,m.name,m.state,sum(m.duration) 
    from (select ID,Name,state,yearend-yearstart as duration from itemregistrationpangkat)m 
    group by  m.id,m.state,m.name;

I need to modified the sql statement into laravel sql format with original DB structure. I have tried with the following code. It is quite lengthy as it is searching function using if statement:

  $query = DB::table('itemregistrations')
                ->join('sections', 'itemregistrations.SectionID', '=', 'sections.SectionID')
                ->join('categories', 'itemregistrations.CategoryID', '=', 'categories.CategoryID')
                ->join('operasi', 'itemregistrations.OperasiID', '=', 'operasi.OperasiID')
                ->join('negeri', 'itemregistrations.NegeriID', '=', 'negeri.NegeriID')
                ->join('gred', 'itemregistrations.GredID', '=', 'gred.GredID')
                ->where('itemregistrations.statusProID', '=', 1)
                ->select('itemregistrations.name','sections.sectionname', 'categories.categoryname', 'operasi.operasiname', 'itemregistrations.Nobadan', 'itemregistrations.lahir_yy', 'itemregistrations.pdrm_yy', 'gred.namagred', 'itemregistrations.itemRegistrationID');

  if($request->input('negeri_perkhidmatan') != '') {
            $query->join('itemregistrationpangkat', 'itemregistrationpangkat.itemRegistrationID', '=', 'itemregistrations.itemRegistrationID')
                ->where('itemregistrationpangkat.NegeriID', $request->input('negeri_perkhidmatan'));           
        }

  if(request('tempoh_negeri')) {

      $query->select(DB::raw("m.ItemRegistrationID, sum(m.duration) from (SELECT itemregistrationpangkat.itemRegistrationID, itemregistrationpangkat.yeartamatkhidmat - itemregistrationpangkat.yearmulakhidmat as duration FROM itemregistrationpangkat) as m"))
      ->groupBy(DB::raw("m.ItemRegistrationID"));

I have tried to modify the 'tempoh_negeri' request selection and it works without error but still doesn't get the required result.

  if(request('tempoh_negeri')) {
            $query->select(DB::raw('m.itemregistrationpangkatID, m.itemRegistrationID, sum(m.duration)'))
                  ->from(DB::raw('(SELECT itemRegistrationID, itemregistrationpangkatID, NegeriID, yeartamatkhidmat - yearmulakhidmat as duration FROM itemregistrationpangkat) AS m 
                      RIGHT JOIN itemregistrations ON itemregistrations.itemRegistrationID=m.itemRegistrationID'))
                  ->groupBy('m.itemRegistrationID');

The result is sent in json format to be displayed using ajax. It shows error 500 as the query is not working as expected.

Debugger shows this error:

   Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from `itemregistrations` 
   inner join `sections` on `itemregistrations`.`SectionID' at line 1 (SQL: 
   select m.ItemRegistrationID, sum(m.duration) 
   from (SELECT itemregistrationpangkat.itemRegistrationID, itemregistrationpangkat.yeartamatkhidmat - itemregistrationpangkat.yearmulakhidmat as duration 
   FROM itemregistrationpangkat) as m 
   from `itemregistrations` 
   inner join `sections` on `itemregistrations`.`SectionID` = `sections`.`SectionID` 
   inner join `categories` on `itemregistrations`.`CategoryID` = `categories`.`CategoryID` 
   inner join `operasi` on `itemregistrations`.`OperasiID` = `operasi`.`OperasiID`  
   inner join `negeri` on `itemregistrations`.`NegeriID` = `negeri`.`NegeriID` 
   inner join `gred` on `itemregistrations`.`GredID` = `gred`.`GredID` inner join `itemregistrationpangkat` on `itemregistrationpangkat`.`itemRegistrationID` = `itemregistrations`.`itemRegistrationID` 
   where `itemregistrations`.`statusProID` = 1 and `itemregistrations`.`CategoryID` = 1 and `itemregistrations`.`OperasiID` = 9 and `itemregistrationpangkat`.`NegeriID` = 2 group by m.ItemRegistrationID)

What is the right syntax to combine those SQL queries?

Have you created models for your application (e.g. `Section`)? — thisiskelvin, Apr 18 '19 at 08:35
I have updated my question, hope your suggestion will not be using eloquent. Thank you — joun, Apr 19 '19 at 07:55
I had edited my question to be more clear..hope someone can guide me to use the right syntax. Thanks — joun, Apr 23 '19 at 01:24
You ended up with `SELECT ... FROM ... FROM ... JOIN ...` -- There can be only one `FROM` rest need to be JOINs. Or... Maybe you wanted a subquery?? — Rick James, Apr 23 '19 at 14:43

joun · Accepted Answer · 2019-05-02T01:14:21.887

1

I had come out with the Laravel SQL to get the duration sum group by each itemregistrationID:

   if(request('tempoh_negeri')) {
            $query->select(DB::raw('m.itemRegistrationID, m.NegeriID, sum(distinct m.duration)'))
                  ->from(DB::raw('(SELECT itemRegistrationID, NegeriID, yeartamatkhidmat - yearmulakhidmat as duration FROM itemregistrationpangkat) AS m 
                        RIGHT JOIN itemregistrations ON itemregistrations.itemRegistrationID=m.itemRegistrationID'))
                  ->groupBy('m.itemRegistrationID') 
                  ->havingRaw('sum(m.duration) >= ?', [request('tempoh_negeri')]);
  }

edited May 02 '19 at 01:14

answered Apr 25 '19 at 06:48

joun

656
1
8
25

the results is removing duplicate by distinct and the group by function properly. – joun May 02 '19 at 01:15
Downvoting this for answering an unclear and absolutely useless question, which should be deleted. Only OP had the Information to solve this "problem". – Paul Spiegel May 02 '19 at 13:03

How to convert this sql query into laravel query builder?

1 Answers1