Mjoin not working correctly

itarodrigo

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.

allan

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

itarodrigo

ranking table

itarodrigo

ranking_cbo

allan

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

itarodrigo

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.

allan

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

itarodrigo

https://bdponline.com.br/tabelas/bdponline.sql

allan

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