Mjoin not working correctly

Mjoin not working correctly

itarodrigoitarodrigo Posts: 15Questions: 6Answers: 0
edited November 2021 in Editor

Debugger code (debug.datatables.net):

$editor = Editor::inst($db, 'ranking')
    ->fields(
        Field::inst('ranking.id'),
        Field::inst('ranking.entidade_id'),
        Field::inst('ranking.cbo_id'),
        Field::inst('ranking.quantidade'),
        Field::inst('ranking.zona'),
        Field::inst('ranking.complexidade'),
        Field::inst('entidade.municipio_descricao'),
        Field::inst('ranking_cbo.ranking_id')
    )
    ->leftJoin('entidade', 'entidade.id', '=', 'ranking.entidade_id')
    ->leftJoin('ranking_cbo', 'ranking.id', '=', 'ranking_cbo.ranking_id')
    ->join(
        Mjoin::inst('cbo')
        ->link('ranking.id', 'ranking_cbo.ranking_id')
        ->link('cbo.id', 'ranking_cbo.cbo_id')
        ->order('no_ocupacao asc')
        ->fields(
            Field::inst('id')
                ->options(Options::inst()
                    ->table('cbo')
                    ->value('id')
                ),
            Field::inst('no_ocupacao')
        )
    )
    ->process($_POST)
    ->json();
$(document).ready(function(){
    var editor;

    $("#cadastro").addClass('active menu-open');
    $("#ranking").addClass('active');

    editor = new $.fn.dataTable.Editor({
      ajax: "tabelas/ranking.php",
      table: "#tabela",
      fields: [
        { label: "Entidade:", name: "ranking.entidade_id", type: "select2", opts: {
          placeholder: "Selecione a entidade",
            initialValue: true,
            ajax: {
              url: "dados/entidade.php",
              dataType: "json",
              delay: 250,
              processResults: function(data){
                return{
                  results: data
                };
              },
              cache: true
            }
          }
        },
        {
          label: "CBO:",
          name: "cbo[].id",
          type: "select2",
          opts: {
            multiple: true,
            placeholder: "Selecione o cbo",
            initialValue: true,
            ajax: {
              url: 'dados/cbo.php',
              dataType: 'json',
              delay: 250,
              processResults: function(data){
                return{
                  results: data
                };
              },
              cache: true
            }
          },
        },
        { label: "Zona:", name: "ranking.zona", type: "select", options: [
          { label: "Urbana", value: "1" },
          { label: "Rural", value: "0" },
        ] },
        { label: "Complexidade:", name: "ranking.complexidade", type: "select", options: [
          { label: "Atenção Básica", value: "0" },
          { label: "Especialidade", value: "1" },
        ] },
        { label: "Quantidade:", name: "ranking.quantidade" },
      ],
      i18n: {
        create: {
          button: "Novo",
          title:  "Criar novo registro",
          submit: "Criar"
        },
        edit: {
          button: "Editar",
          title:  "Editar registro",
          submit: "Atualizar"
        },
        remove: {
          button: "Delete",
          title:  "Delete",
          submit: "Delete",
          confirm: {
            "_": "Are you sure you wish to delete %d rows?",
            "1": "Are you sure you wish to delete 1 row?"
          }
        },
        error: {
          "system": "Ocorreu um erro (Mais informações)"
        },
        multi: {
          "title": "Múltiplos valores",
          "info": "The selected items contain different values for this input. To edit and set all items for this input to the same value, click or tap here, otherwise they will retain their individual values.",
          "restore": "Undo changes",
          "noMulti": "This input can be edited individually, but not part of a group."
        },
        datetime: {
          "previous": 'Anterior',
          "next":     'Próximo',
          "months":   [ 'Janeiro', 'Fevereiro', 'Março', 'Abril', 'Maio', 'Junho', 'Julho', 'Agosto', 'Setembro', 'Outubro', 'Novembro', 'Dezembro' ],
          "weekdays": [ 'Dom', 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab' ],
          "amPm":     [ 'am', 'pm' ],
          "unknown":  '-'
        }
      }
    });
 
    $('#tabela').DataTable({
      responsive: true,
      fixedHeader: true,
      language: {searchPlaceholder: "Descrição ou CNES",
        processing:     "Processando...",
        search:         "Pesquisar:",
        lengthMenu:     "Mostrando _MENU_ registros",
        info:           "Mostrando de _START_ a _END_ de um total de _TOTAL_ registros",
        infoEmpty:      "Mostrando 0 até 0 de 0 registros",
        infoFiltered:   "(Filtrados de _MAX_ registros)",
        InfoThousands:  ".",
        infoPostFix:    "",
        loadingRecords: "Carregando registros...",
        zeroRecords:    "Nenhum registro encontrado",
        emptyTable:     "Tabela está vazia",
        paginate: {
          first:      "Primeiro",
          previous:   "Anterior",
          next:       "Próximo",
          last:       "Último"
        },
        aria: {
          sortAscending:  ": ordenar a coluna de forma ascendente",
          sortDescending: ": ordenar a coluna de forma descendente"
        }
      },
      dom: "Bfrtip",
      ajax: {
        url: "tabelas/ranking.php",
        type: "POST"
      },
      serverSide: true,
      columns: [
        { data: "entidade.municipio_descricao", className: "text-right" },
        { data: "cbo", render: "[, ].no_ocupacao", searchable: false },
        { data: "ranking.zona",
          render: function (val, type, row) {
            if (val == '0') return "Rural"
            else if (val == '1') return "Urbana"
            else return "";
          }, searchable: false, className: 'text-right'
        },
        { data: "ranking.complexidade",
          render: function (val, type, row) {
            if (val == '0') return "Atenção Básica"
            else if (val == '1') return "Especialidade"
            else return "";
          }, searchable: false, className: 'text-right'
        },
        { data: "ranking.quantidade", className: "text-right" },
      ],
      select: true,
      buttons: [
        { extend: "create", editor: editor  },
        { extend: "edit", editor: editor  },
        { extend: "remove", editor: editor  },
      ],
    });

Description of problem:

The result not group by first column, this repeat the results.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    The query being performed there will show one row for every entry in the ranking database table. From the information I see above it looks like you have three rows in ranking which join to entidade where entidade.municipio_descricao is ALAGOINHA - PE. Is that correct?

    Perhaps you can show me what is in your ranking table? i.e. just a simple select on it.

    Allan

  • itarodrigoitarodrigo Posts: 15Questions: 6Answers: 0

    ranking table

  • itarodrigoitarodrigo Posts: 15Questions: 6Answers: 0

    ranking_cbo

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Thank you - I'm not sure what entidade_id is in terms of the name shown in the table, but it certainly shows that three joins are performed to value 19. So I would expect, based on the code above, so see three rows in the ranking table with entidade id 19 shown.

    Is that not what you want?

    Allan

  • itarodrigoitarodrigo Posts: 15Questions: 6Answers: 0
    edited December 2021

    I want the results like the image above

    ALAGOINHA have 3 CBO selected, so this repeat 3 times.
    BELEM DE MARIA have 2 CBO selected, so this repeat 2 times.

    Each CBO I select, create a new row on table.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Sorry, I don't have your full data set, so I'm needing to ask a few questions as we go here. Perhaps you could give me a dump of your three SQL tables?

    ALAGOINHA - is that a single record in your entidade table? If so, it sounds to me like your Editor PHP instance should be using entidade as the main table and not ranking.

    Allan

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Thank you.

    I think my second paragraph in my previous reply is correct having seeing the data now. The entidade table is the one which should be the main table that is being edited, unless I've misunderstood something?

    Allan

Sign In or Register to comment.