One JSON with multiple queries

Question

So guys updating my problem: I have a Delphi 2007 function that "ImportStudentMisses":

procedure TWeb.ImportStudentMisses;
var
  SLMisses, SLClasses: TStringList;
  i: Integer;
  idClass, idStudent: integer;
begin
  SLMisses := TStringlist.Create;
  SLMisses.Duplicates := dupIgnore;
  SLMisses.Sorted := true;

  SLClasses := TStringlist.Create;
  SLClasses.Duplicates := dupIgnore;
  SLClasses.Sorted := true;

  try
    FTexto := '[INTERNET] - Fase 1';
    Synchronize(DoStatus);

    qryDestino.SQL.Clear;
    qryDestino.SQL.Add('SELECT ClassID, StudentID ');
    qryDestino.SQL.Add(' FROM ' + Config.InternetConfig.prefix + 'MissOneClass ');
    qryDestino.SQL.Add(' ORDER BY ClassID, StudentID');
    qryDestino.Open;
    while not qryDestino.eof do
    begin
      SLAulas.Add(formatfloat('00000000', qryDestino.Fields[0].AsInteger));
      SLFaltas.Add(formatfloat('00000000', qryDestino.Fields[0].AsInteger) + '-' + qryDestino.Fields[1].asString);
      qryDestino.Next;
    end;

    FTexto := '[INTERNET] - Fase 2';
    Synchronize(DoStatus);

    qryDestino.Close;
    qryDestino.SQL.Clear;
    qryDestino.SQL.Add('SELECT Date, DisciplineID, StudentID ');
    qryDestino.SQL.Add(' FROM ' + Config.InternetConfig.prefix + 'MissTwoClasses ');
    qryDestino.SQL.Add(' ORDER BY Date, DisciplineID, StudentID');
    qryDestino.Open;
    while not qryDestino.eof do
    begin //local Table
      qryOrigem.SQL.Clear;
      qryOrigem.SQL.Add('SELECT lNum FROM Class');
      qryOrigem.SQL.Add('WHERE lDiscipline = ' + IntTostr(qryDestino.Fields[1].AsInteger));
      qryOrigem.SQL.Add('  AND lDate = ' + IntTostr(trunc(qryDestino.Fields[0].AsDateTime)));
      qryOrigem.Open;
      qryOrigem.First;
      while not qryOrigem.eof do
      begin
        idClass := qryOrigem.Fields[0].AsInteger;
        if idClass > 0 then
        begin
          SLClasses.Add(FormatFloat('00000000', idClass));
          SLMisses.Add(formatfloat('00000000', idClass) + '-' + qryDestino.Fields[2].asString);
        end;
        qryOrigem.Next;
      end;
      qryOrigem.Close;

      qryDestino.Next;
    end;

    FTexto := '[INTERNET] - Fase 3';
    Synchronize(DoStatus);

    qryDestino.Close;
    qryDestino.SQL.Clear;
    qryDestino.SQL.Add('SELECT Date, StudentID ');
    qryDestino.SQL.Add(' FROM ' + Config.InternetConfig.prefix + 'MissTheDay ');
    qryDestino.SQL.Add(' ORDER BY Date, StudentID');
    qryDestino.Open;

    while not qryDestino.eof do
    begin //Local Table
      qryOrigem.SQL.Clear;
      qryOrigem.SQL.Add('SELECT lNum FROM Class');
      qryOrigem.SQL.Add('WHERE lDate = ' + IntTostr(trunc(qryDestino.Fields[0].AsDateTime)));
      qryOrigem.Open;
      qryOrigem.First;
      while not qryOrigem.eof do
      begin
        idClass := qryOrigem.Fields[0].AsInteger;
        if idClass > 0 then
        begin
          SLClasses.Add(FormatFloat('00000000', idClass));
          SLMisses.Add(formatfloat('00000000', idClass) + '-' + qryDestino.Fields[1].asString);
        end;
        qryOrigem.Next;
      end;
      qryOrigem.Close;
      qryDestino.Next;
    end;
    qryDestino.Close;
    qryDestino.SQL.Clear;

    qryOrigem.Close;


    qryOrigem.SQL.Clear;
    qryOrigem.SQL.Add('DELETE FROM Misses WHERE lClass = :pClass');
    for i := 0 to SLClasses.Count - 1 do
    begin
      idClass := StrToIntDef(SLClasses[i], 0);
      qryOrigem.Params[0].AsInteger := idClass;
      qryOrigem.ExecSQL;
    end;

    qryOrigem.SQL.Clear;
    qryOrigem.SQL.Add('INSERT INTO Misses');
    qryOrigem.SQL.Add(' (lClass, lStudent) VALUES ');
    qryOrigem.SQL.Add(' (:pClass, :pStudent)');

    for i := 0 to SLMisses.Count - 1 do
    begin
      idClass := StrToIntDef(Copy(SLMisses[i], 1, 8), 0);
      idStudent := StrToIntDef(Trim(Copy(SLMisses[i], 10, 8)), 0);

      if (idClass > 0) and (idStudent > 0) then
      begin
        try
          qryOrigem.Params[0].AsInteger := idClass;
          qryOrigem.Params[1].AsInteger := idStudent;
          qryOrigem.ExecSQL;
        except
          on e: Exception do
          begin
            FTexto := '[INTERNET] - Error ' + E.Message;
            Synchronize(DoStatus);
          end;
        end;
      end;
    end;
  except
    on E: Exception do
    begin
      FTexto := '[INTERNET] - ' + E.Message;
      Synchronize(DoStatus);
      reconect;
    end;
  end;
  SLMisses.Free;
end;

This function import all the students that miss a class in a respective day. And I have to make this function become a JSON like this:

[{"ClassID":10,"StudentsID":[1,2]},{"ClassID":20,"StudentsID":[3,4]}]

As you can see the JSON has the ("ClassID":10) and a list of students that are missing that class ("Students":[1,2,3,4,5]). I've already tryied the code below to make the JSON that I wanted.

$query = $database->query( "SELECT ClassID, GROUP_CONCAT(StudentID) AS StudentID FROM {$pfx}MissOneClass
GROUP BY ClassID ORDER BY ClassID");
$list = array();
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $list[] = array('ClassID' => $row['ClassID'], 
    'StudentID' => array_map('intval', explode(',', $row['StudentID'])));
    }
echo json_encode($list);

And I've got this JSON back:

[{"ClassID":1,"StudentsID":[1,2,3,4]},{"ClassID":2,"StudentsID":[3,4,23]}]

The JSON is the JSON that I needed. But I'be been told that I have to use all the tables not only "MissOneClass" as I did.

This JSON will be consumed by a JAVA application that will INSERT the JSON data on the DB. And that's the role problem.

Thank you!!!

Answer 1

In Union, the number of columns should be the same in all 3 tables you are using. check the different between union and union all. run the query on your database to check the result.

SELECT ClassID, StudentID, null as Date, null as DisciplineID
FROM MissOneClass
union all
SELECT null as ClassID,StudentID, Date, DisciplineID
FROM MissTwoClasses
union all
SELECT null as ClassID, StudentID, Date, null as DisciplineID
FROM MissTheDay
ORDER BY Date, DisciplineID, ClassID, StudentID

You can also use join, it is still confusing in what way you want to merge your JSON these 2 will give different results. I am assuming student id is a foreign key in this case.

SELECT ClassID, StudentID, Date, DisciplineID
FROM MissOneClass mo full outer join MissTwoClasses mt on mt.StudentID = mo.StudentID full outer join MissTheDay md md.StudentID = mo.StudentID