SearchPanes says no data

Airprimus

Hello,
I am currently switching to Datatables Editor and now I have the effect that the SearchPanes do not show any data.

Datatables: v1.10.22
SearchPane: v1.2.0
Select: 1.3.1

And here is the code:

require "../lib/DataTables.php";

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;  
    
Editor::inst($db, 's_articles')
    ->field(
        Field::inst('s_articles_supplier.name'),
        Field::inst('s_articles_details.suppliernumber'),
        Field::inst('s_articles_details.ordernumber'),
        Field::inst('s_articles.name'),
        Field::inst('s_articles_details.stockmin'),
        Field::inst('s_articles_details.instock'),
        Field::inst('s_articles_attributes.attr7'),
        Field::inst('s_articles_attributes.attr6'),
        Field::inst('s_articles_attributes.lieferant')
    )

    ->leftJoin('s_articles_details', 's_articles.id', '=', 's_articles_details.articleID')
    ->leftJoin('s_articles_attributes', 's_articles_details.id', '=', 's_articles_attributes.articledetailsID')
    ->leftJoin('s_articles_supplier', 's_articles.supplierID', '=', 's_articles_supplier.id')
    ->process($_POST)
    ->json();

var editor;

$(document).ready(function () {
  editor = new $.fn.dataTable.Editor({
    ajax: "./pdo/db_inventory.php",
    table: "#inventory",
    fields: [
      {
        label: "Hersteller:",
        name: "s_articles_supplier.name",
      },
      {
        label: "Lieferant:",
        name: "s_articles_attributes.lieferant",
      },
      {
        label: "Hersteller-Nr:",
        name: "s_articles_details.suppliernumber",
      },
      {
        label: "Shop-Nr:",
        name: "s_articles_details.ordernumber",
      },
      {
        label: "Bezeichnung:",
        name: "s_articles.name",
      },
      {
        label: "Min.:",
        name: "s_articles_details.stockmin",
      },
      {
        label: "Lager:",
        name: "s_articles_details.instock",
        attr: {
          type: "number",
        },
      },
      {
        label: "Regal:",
        name: "s_articles_attributes.attr7",
      },
      {
        label: "Bemerkung:",
        name: "s_articles_attributes.attr6",
      },
    ],
  });

  $("#inventory").on("click", "tbody td.editable", function (e) {
    editor.bubble(
      this,
      [
        "s_articles_details.stockmin",
        "s_articles_details.instock",
        "s_articles_attributes.attr7",
        "s_articles_attributes.attr6",
      ],
      {
        title: "Ihre Eingabe:",
      }
    );
  });

  $("#inventory").DataTable({
    ajax: {
      url: "./pdo/db_inventory.php",
      type: "POST",
    },
    columns: [
      {
        data: "s_articles_supplier.name",
      },
      {
        data: "s_articles_attributes.lieferant",
      },
      {
        data: "s_articles_details.suppliernumber",
      },
      {
        data: "s_articles_details.ordernumber",
      },
      {
        data: "s_articles.name",
      },
      {
        data: "s_articles_details.stockmin",
        className: "editable",
      },
      {
        data: "s_articles_details.instock",
        className: "editable",
      },
      {
        data: "s_articles_attributes.attr7",
        className: "editable",
      },
      {
        data: "s_articles_attributes.attr6",
        className: "editable",
      },
    ],
    columnDefs: [
      {
        searchPanes: {
          show: true,
          layout: "column-2",
        },
        targets: [0, 2, 3],
      },
    ],

    responsive: true,
    dom: "Pfrtip",
    // '<"dtsp-verticalContainer"<"dtsp-verticalPanes"P><"dtsp-dataTable"frtip>>',
    serverSide: true,
    select: true,
    deferRender: true,
    language: {
      sEmptyTable: "Keine Daten in der Tabelle vorhanden",
      sInfo: "_START_ bis _END_ von _TOTAL_ Einträgen",
      sInfoEmpty: "0 bis 0 von 0 Einträgen",
      sInfoFiltered: "(gefiltert von _MAX_ Einträgen)",
      sInfoPostFix: "",
      sInfoThousands: ".",
      sLengthMenu: "_MENU_ Einträge anzeigen",
      sLoadingRecords: "Wird geladen...",
      sProcessing: "Bitte warten...",
      sSearch: "Globale Suche",
      sZeroRecords: "Keine Einträge vorhanden.",
      oPaginate: {
        sFirst: "Erste",
        sPrevious: "Zurück",
        sNext: "Nächste",
        sLast: "Letzte",
      },
      oAria: {
        sSortAscending: ": aktivieren, um Spalte aufsteigend zu sortieren",
        sSortDescending: ": aktivieren, um Spalte absteigend zu sortieren",
      },
      searchPanes: {
        emptyPanes: "There are no panes to display. :/",
      },
    },
  });
  $("#inventory").DataTable().searchPanes.rebuildPane();
});

Thanks

Lars

colin

Odd, it should work - see here. It could be something to do with the Ajax call, and your searchPanes.rebuildPane() at the end of the code. Try remove that call as I don't think it's needed, or possibly move it into initComplete, as the data would be loaded by then.

Colin

Airprimus

Hello Colin,

unfortunately it does not work with that either. Neither per initComplete nor completely without. The SearchPanes always remain empty.

I have no more idea at the moment.

Lars

colin

Even odder! We're happy to look at your page if you can link to it. If not, could you update this example here to demonstrate your issue, please.

Colin

sandy

Hi @Airprimus ,

If you look at the example that colin linked above, under the server script tag you will see that you need to add the SearchPanes initialisation to your Controller. If you do that then you should start to see some data coming through.

If that doesn't work, please update the example above.

Thanks,
Sandy

Airprimus

Hello,

I have initialized the SearchPanes like in the example, now the SearchPane is also displayed but every time I set a filter there is an SQL error. According to the debug it tries to search with the label instead of the value. Is this a known error?

Enclosed the debug: https://debug.datatables.net/abayus

bindings: [{name: ":where_1", value: "40", type: null}]
0: {name: ":where_1", value: "40", type: null}
query: "SELECT s_articles.supplierID as 'value', COUNT(*) as count FROM s_articles LEFT JOIN s_articles_details ON s_articles.id = s_articles_details.articleID WHERE (s_articles_supplier.name = :where_1 ) GROUP BY s_articles.supplierID"
error: "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 's_articles_supplier.name' in 'where clause'

sandy

Hi @Airprimus ,

Could you please show me your current controller and client side initialisation? Hopefully that will help me to see where something may be going wrong.

Thanks,
Sandy

Airprimus

Hello, Sandy,

attached the controller and the JS. I hope it helps in the search.

Controller:

require "../lib/DataTables.php";

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\SearchPaneOptions; 
    
Editor::inst($db, 's_articles')
    ->field(
        Field::inst('s_articles_supplier.name')
        ->searchPaneOptions(SearchPaneOptions::inst()
        ->value( 's_articles.supplierID')
        ->label('s_articles_supplier.name')
        ->leftJoin('s_articles_supplier', 's_articles.supplierID', '=', 's_articles_supplier.id')
        ),
        Field::inst('s_articles_details.suppliernumber'),
        Field::inst('s_articles_details.ordernumber'),
        Field::inst('s_articles.name'),
        Field::inst('s_articles_details.stockmin'),
        Field::inst('s_articles_details.instock'),
        Field::inst('s_articles_attributes.attr7'),
        Field::inst('s_articles_attributes.attr6'),
        Field::inst('s_articles_attributes.lieferant'),
        Field::inst('s_articles_attributes.top_art_lief')
    )

    ->leftJoin('s_articles_details', 's_articles.id', '=', 's_articles_details.articleID')
    ->leftJoin('s_articles_attributes', 's_articles_details.id', '=', 's_articles_attributes.articledetailsID')
    ->leftJoin('s_articles_supplier', 's_articles.supplierID', '=', 's_articles_supplier.id')

    //->where('s_articles_attributes.top_art_lief', 1)
    ->debug(true)
    ->process($_POST)
    ->json();

JS

var editor;
var artikelart = 0; //0 = Alle Artikel, 1 = Top Artikel

$(document).ready(function () {
  function colData(column) {
    var data = [];

    column
      .data()
      .unique()
      .sort()
      .each(function (d, j) {
        data.push({
          id: j,
          text: d,
        });
      });

    return data;
  }

  editor = new $.fn.dataTable.Editor({
    ajax: "./pdo/db_inventory.php",
    table: "#inventory",
    fields: [
      {
        label: "Hersteller:",
        name: "s_articles_supplier.name",
      },
      {
        label: "Lieferant:",
        name: "s_articles_attributes.lieferant",
      },
      {
        label: "Hersteller-Nr:",
        name: "s_articles_details.suppliernumber",
      },
      {
        label: "Shop-Nr:",
        name: "s_articles_details.ordernumber",
      },
      {
        label: "Bezeichnung:",
        name: "s_articles.name",
      },
      {
        label: "Min.:",
        name: "s_articles_details.stockmin",
      },
      {
        label: "Lager:",
        name: "s_articles_details.instock",
        attr: {
          type: "number",
        },
      },
      {
        label: "Regal:",
        name: "s_articles_attributes.attr7",
      },
      {
        label: "Bemerkung:",
        name: "s_articles_attributes.attr6",
      },
    ],
  });

  $("#inventory").on("click", "tbody td.editable", function (e) {
    editor.bubble(
      this,
      [
        "s_articles_details.stockmin",
        "s_articles_details.instock",
        "s_articles_attributes.attr7",
        "s_articles_attributes.attr6",
      ],
      {
        title: "Ihre Eingabe:",
      }
    );
  });

  $("#inventory").DataTable({
    ajax: {
      url: "./pdo/db_inventory.php",
      type: "POST",
    },
    autoWidth: false,
    columns: [
      {
        data: "s_articles_supplier.name",
      },
      {
        data: "s_articles_attributes.lieferant",
      },
      {
        data: "s_articles_details.suppliernumber",
      },
      {
        data: "s_articles_details.ordernumber",
      },
      {
        data: "s_articles.name",
      },
      {
        data: "s_articles_details.stockmin",
        className: "editable",
      },
      {
        data: "s_articles_details.instock",
        className: "editable",
      },
      {
        data: "s_articles_attributes.attr7",
        className: "editable",
      },
      {
        data: "s_articles_attributes.attr6",
        className: "editable",
      },
      {
        data: "s_articles_attributes.top_art_lief",
      },
    ],
    columnDefs: [
      {
        targets: 3,
        render: function (data, type, row) {
          return (
            '<a href="/backend/?c=article&nr=' +
            data +
            '" target="_black"><span class="btn btn-outline-success btn-sm" role="button" style="width:100%;">' +
            data +
            "</span></a>"
          );
        },
      },
      {
        targets: 9,
        visible: false,
      },
    ],
    /*searchPanes: {
      layout: "columns-1",
    },*/
    responsive: true,
    // dom: '<"dtsp-verticalContainer"<"dtsp-verticalPanes"P><"dtsp-dataTable"Bfrtip>>',
    dom: "PBfrtip",
    serverSide: true,
    processing: false,
    buttons: [
      {
        text: "Alle Artikel",
        action: function (e, dt, node, config) {
          if (artikelart == 0) {
            this.text("Top Artikel");
            dt.column(9).search("1").draw();
            artikelart = 1;
          } else if (artikelart == 1) {
            this.text("Alle Artikel");
            dt.column(9).search("").draw();
            artikelart = 0;
          }
        },
      },
    ],
    language: {
      sEmptyTable: "Keine Daten in der Tabelle vorhanden",
      sInfo: "_START_ bis _END_ von _TOTAL_ Einträgen",
      sInfoEmpty: "Keine Daten vorhanden",
      sInfoFiltered: "(gefiltert von _MAX_ Einträgen)",
      sInfoPostFix: "",
      sInfoThousands: ".",
      sLengthMenu: "_MENU_ Einträge anzeigen",
      sLoadingRecords: "Wird geladen ..",
      sProcessing: "Bitte warten ..",
      sSearch: "Suchen",
      sZeroRecords: "Keine Einträge vorhanden",
      oPaginate: {
        sFirst: "Erste",
        sPrevious: "Zurück",
        sNext: "Nächste",
        sLast: "Letzte",
      },
      oAria: {
        sSortAscending: ": aktivieren, um Spalte aufsteigend zu sortieren",
        sSortDescending: ": aktivieren, um Spalte absteigend zu sortieren",
      },
      select: {
        rows: {
          _: "%d Zeilen ausgewählt",
          0: "",
          1: "1 Zeile ausgewählt",
        },
      },
      buttons: {
        print: "Drucken",
        colvis: "Spalten",
        copy: "Kopieren",
        copyTitle: "In Zwischenablage kopieren",
        copyKeys:
          "Taste <i>ctrl</i> oder <i>\u2318</i> + <i>C</i> um Tabelle<br>in Zwischenspeicher zu kopieren.<br><br>Um abzubrechen die Nachricht anklicken oder Escape drücken.",
        copySuccess: {
          _: "%d Zeilen kopiert",
          1: "1 Zeile kopiert",
        },
        pageLength: {
          "-1": "Zeige alle Zeilen",
          _: "Zeige %d Zeilen",
        },
      },
    },

    initComplete: function () {
      var excluded_columns = [3, 4, 5, 6, 7, 8, 9];
      this.api()
        .columns()
        .every(function () {
          var title = $(this.header()).html();
          titleId = title.replace(/[\W]/g, "-");
          var column = this;

          if (excluded_columns.indexOf(column.index()) == -1) {
            var input = $(
              '<input id="' +
                titleId +
                '" style="width:90%;" placeholder="Filter ' +
                $(title).text() +
                '" />'
            )
              .appendTo($(column.footer()).empty())
              .on("change", function () {
                var data = $.map($(this).select2("data"), function (
                  value,
                  key
                ) {
                  return value.text
                    ? "^" + $.fn.dataTable.util.escapeRegex(value.text) + "$"
                    : null;
                });

                if (data.length === 0) {
                  data = [""];
                }

                var val = data.join("|");

                column.search(val ? val : "", true, false).draw();
              });

            $("#" + titleId).select2({
              data: colData(column),
              placeholder: "Suche",
              multiple: true,
              query: function (q) {
                var pageSize,
                  results,
                  that = this;
                pageSize = 5;
                results = [];
                if (q.term && q.term !== "") {
                  results = _.filter(that.data, function (e) {
                    return (
                      e.text.toUpperCase().indexOf(q.term.toUpperCase()) >= 0
                    );
                  });
                } else if (q.term === "") {
                  results = that.data;
                }
                q.callback({
                  results: results.slice(
                    (q.page - 1) * pageSize,
                    q.page * pageSize
                  ),
                  more: results.length >= q.page * pageSize,
                });
              },
            });
          }
        });
    },
  });
});

Thanks in advance

Lars

sandy

Hi @Airprimus ,

Thanks for sending those over. I think we actually fixed an issue very similar a couple of weeks ago. How old is your editor php library? I think it would be worth pulling and rebuilding a copy of that to see if it makes a difference.

SearchPanes used to only look at one left join, the fix meant that it now consults all of the left joins. Take a look at this commit. I reckon you might need that to get it working.

Thanks,
Sandy

Airprimus

Hello, Sandy,

that's exactly what it was. I downloaded the new version and now the SearchPanes are filled correctly and filters are correct.

Thanks a lot

Lars