search is not working in mysql adapter

[xeleniumz]

hello thank you for the great library everything is work fine except when i try to use seach in datatables , here is the result error when search

Executing (default): DESCRIBE `student`;
Executing (default): SELECT count(*) AS `count` FROM `student` AS `student`;
Executing (default): SELECT count(*) AS `count` FROM `student` AS `student` WHERE 0 = 1;

how t o fix it ?

[alwint3r]

Hi @xeleniumz,

Can you show me the way to reproduce this error? Or at least can you show me the jquery datatables configuration along with the library usage code?

I can't go anywhere without these information.

[xeleniumz]

Hi @alwint3r thank you for fast response i've use mysql adapter with sequelize and use sequelize-datatable for show and query data this is my jquery code in my view

 $(document).ready(function() {
          'use strict';
                        var oTable = $('#oTable').DataTable({
                           "serverSide": true,
                            "processing" : false,
                            "ajax": {
                                "url": "/api/std",
                                "type": "GET",
                            },
                            "columns": [
                                        { "data": "std_id",  },
                                        { "data": "std_title"  },
                                        { "data": "std_firstname" },
                                        { "data": "std_lastname"},
                                        { "data": "std_class"},
                                                       { "data": "std_room"},

                                    ]

                            }); 

                })

and this is my ApiController when page load everything is working fine and i can sort data from datatable but when i want to search data it can't show anything and response no data in the table

import datatable from 'sequelize-datatables'

const ApiController = {

    async std(req, res) {

        const db = req.db.kkr.student
        let oTable = await datatable(db, req.query, {}, {})
        res.setHeader('Content-Type', 'application/json');
        res.json(oTable)

  }

}

export default ApiController

this is query result when datatable load

Executing (default): SELECT count(*) AS `count` FROM `student` AS `student`;
Executing (default): SELECT count(*) AS `count` FROM `student` AS `student`;
Executing (default): SELECT `id`, `std_id`, `std_title`, `std_firstname`, `std_lastname`, `std_room`, `std_class` FROM `student` AS `student` ORDER BY `student`.`std_id` ASC LIMIT 0, 10;

this is query when i try to search with some keywords

Executing (default): DESCRIBE `student`
Executing (default): SELECT count(*) AS `count` FROM `student` AS `student`;
Executing (default): SELECT count(*) AS `count` FROM `student` AS `student` WHERE 0 = 1;

Thank you for your advice and sorry for my bad english

[alwint3r]

Hi @xeleniumz,

Thank you for the info. I'll look in to it and post some update on this issue as soon as possible.

[faerulsalamun]

Maybe different format when get data query @xeleniumz

Example format when koa catch data with ctx.request.query

{ 
  draw: '1',
  'columns[0][data]': '',
  'columns[0][name]': '',
  'columns[0][searchable]': 'true',
  'columns[0][orderable]': 'false',
  'columns[0][search][value]': '',
  'columns[0][search][regex]': 'false',
  'columns[1][data]': 'name',
  'columns[1][name]': '',
  'columns[1][searchable]': 'true',
  'columns[1][orderable]': 'true',
  'columns[1][search][value]': '',
  'columns[1][search][regex]': 'false',
  'columns[2][data]': '',
  'columns[2][name]': '',
  'columns[2][searchable]': 'true',
  'columns[2][orderable]': 'false',
  'columns[2][search][value]': '',
  'columns[2][search][regex]': 'false',
  'order[0][column]': '1',
  'order[0][dir]': 'asc',
  start: '0',
  length: '10',
  'search[value]': '',
  'search[regex]': 'false',
  _: '1499000044467' 
}

CMIIW

Stupid solution until the author can find the problem :)

const searchDatas = {
      draw: ctx.request.query.draw,
      start: ctx.request.query.start,
      length: ctx.request.query.length,
      _: ctx.request.query._,
      columns: [],
      order: [],
      search: {
        value: ctx.request.query[`search[value]`],
        regex: ctx.request.query[`search[regex]`],
      },
    };

    const columnsStop = true;
    let columnsPosition = 0;
    while (columnsStop) {
      if (ctx.request.query[`columns[${columnsPosition}][data]`] === undefined) {
        break;
      } else {
        searchDatas.columns.push({
          data: ctx.request.query[`columns[${columnsPosition}][data]`],
          name: ctx.request.query[`columns[${columnsPosition}][name]`],
          searchable: ctx.request.query[`columns[${columnsPosition}][searchable]`],
          orderable: ctx.request.query[`columns[${columnsPosition}][orderable]`],
          search: {
            value: ctx.request.query[`columns[${columnsPosition}][search][value]`],
            regex: ctx.request.query[`columns[${columnsPosition}][search][regex]`],
          },
        });
        columnsPosition += 1;
      }
    }

    const ordersStop = true;
    let orderPosition = 0;
    while (ordersStop) {
      if (ctx.request.query[`order[${orderPosition}][column]`] === undefined) {
        break;
      } else {
        searchDatas.order.push({
          column: ctx.request.query[`order[${orderPosition}][column]`],
          dir: ctx.request.query[`order[${orderPosition}][dir]`],
        });
        orderPosition += 1;
      }
    }

[alwint3r]

As far as I know, these are generated by jQuery datatables.

{ draw: '1', 'columns[0][data]': '', 'columns[0][name]': '', 'columns[0][searchable]': 'true', 'columns[0][orderable]': 'false', 'columns[0][search][value]': '', 'columns[0][search][regex]': 'false', 'columns[1][data]': 'name', 'columns[1][name]': '', 'columns[1][searchable]': 'true', 'columns[1][orderable]': 'true', 'columns[1][search][value]': '', 'columns[1][search][regex]': 'false', 'columns[2][data]': '', 'columns[2][name]': '', 'columns[2][searchable]': 'true', 'columns[2][orderable]': 'false', 'columns[2][search][value]': '', 'columns[2][search][regex]': 'false', 'order[0][column]': '1', 'order[0][dir]': 'asc', start: '0', length: '10', 'search[value]': '', 'search[regex]': 'false', _: '1499000044467' }

So, I think @xeleniumz' datatables configuration is fine. I believe the issue is in this file, but I'm not sure yet, not until I test it myself.

[xeleniumz]

@faerulsalamun thank you for your solution @alwint3r problem is in that file i think problem is in model 's properties because when we search it didn't show properties ( may be )

[faerulsalamun]

@alwint3r This is what happened to me with datatable angular and pg

In my datatable when request to server and log koa with ctx.request.query

{ 
  draw: '1',
  'columns[0][data]': '',
  'columns[0][name]': '',
  'columns[0][searchable]': 'true',
  'columns[0][orderable]': 'false',
  'columns[0][search][value]': '',
  'columns[0][search][regex]': 'false',
  'columns[1][data]': 'name',
  'columns[1][name]': '',
  'columns[1][searchable]': 'true',
  'columns[1][orderable]': 'true',
  'columns[1][search][value]': '',
  'columns[1][search][regex]': 'false',
  'columns[2][data]': '',
  'columns[2][name]': '',
  'columns[2][searchable]': 'true',
  'columns[2][orderable]': 'false',
  'columns[2][search][value]': '',
  'columns[2][search][regex]': 'false',
  'order[0][column]': '1',
  'order[0][dir]': 'asc',
  start: '0',
  length: '10',
  'search[value]': '',
  'search[regex]': 'false',
  _: '1499000044467' 
}

And i see your mockup file

{
  "draw": "1",
  "columns": [
    {
      "data": "no",
      "name": "",
      "searchable": "true",
      "orderable": "true",
      "search": {
        "value": "",
        "regex": "false"
      }
    },
    {
      "data": "name",
      "name": "",
      "searchable": "true",
      "orderable": "true",
      "search": {
        "value": "",
        "regex": "false"
      }
    },
    {
      "data": "address",
      "name": "",
      "searchable": "true",
      "orderable": "false",
      "search": {
        "value": "",
        "regex": "false"
      }
    },
    {
      "data": "phone",
      "name": "",
      "searchable": "true",
      "orderable": "false",
      "search": {
        "value": "",
        "regex": "false"
      }
    },
    {
      "data": "email",
      "name": "",
      "searchable": "true",
      "orderable": "true",
      "search": {
        "value": "",
        "regex": "false"
      }
    }
  ],
  "order": [
    {
      "column": "0",
      "dir": "asc"
    }
  ],
  "start": "0",
  "length": "10",
  "search": {
    "value": "",
    "regex": "false"
  },
  "_": "1478912938246"
}

@xeleniumz Ok maybe my solution can't solved your problem, hehe

[alwint3r]

@faerulsalamun: I just noticed that your request is in JSON format. Are you using POST as the request method? My mock data is obtained from express' req.query, so I think express parsed the request for me.

By the way, I think I found the cause of this issue. I'm gonna release the fix tomorrow at best. I still need to do more tests just to be sure. @xeleniumz

[faerulsalamun]

@alwint3r I use method GET with params

My bad, the format show when i log ctx.request.query

Maybe koa have different parsed with express in query

Maybe can help https://github.com/hubsmoke/sequelize-datatable-node/commit/229819cc374cf8a61ba4024cdf796e5ace3d131c

Anyway awesome library 👍

[alwint3r]

@faerulsalamun

My solution is similar with that commit. Thanks by the way

[xeleniumz]

@alwint3r i can help you test if you want :)

[alwint3r]

@xeleniumz I pushed the fix to fix-mysql-search-issue branch. You may want to try & add more case to the automated test as well.

[xeleniumz]

@alwint3r thank you so much I will try it now 👍

[xeleniumz]

thank you so much now it's work. i think you can merge to master branch. awesome library 👍

[alwint3r]

I'll submit a release to npm if everything is ok.

On Tue, Jul 4, 2017 at 11:26 AM, xeleniumz notifications@github.com wrote:

Closed #1 https://github.com/alwint3r/sequelize-datatable-node/issues/1.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/alwint3r/sequelize-datatable-node/issues/1#event-1148940423, or mute the thread https://github.com/notifications/unsubscribe-auth/AMVjnXD9vJ1J9_T8LI_Rybhfve_IQU6zks5sKb7ngaJpZM4OLe4s .

[alwint3r]

Hi @xeleniumz, is the problem solved? I'm gonna release the patch later this day so, I'd love to hear any feedback (if there's any) before I release the patch.

[xeleniumz]

Hi @alwint3r all problem already solved thank you for your great work :) i think when use search with number it should response all of number that match in database in your search_builder.js i was modify it from

const possibleNumericTypes = [
  `INTEGER`,
  `DECIMAL`,
  `FLOAT`,
  `DOUBLE`,
  `INT`,
  `TINYINT`,
  `BIGINT`,
  `NUMBER`,
  `REAL`,
];

const possibleStringTypes = [
  `CHARACTER VARYING`,
  `VARCHAR`,
  `TEXT`,
  `CHAR`,
  `STRING`,
  `TINYTEXT`,
  `MEDIUMTEXT`,
  `LONGTEXT`,
];

to

const possibleNumericTypes = [
  `DECIMAL`,
  `FLOAT`,
  `DOUBLE`,
  `TINYINT`,
  `BIGINT`,
  `NUMBER`,
  `REAL`,
];

const possibleStringTypes = [
  `CHARACTER VARYING`,
  `VARCHAR`,
  `TEXT`,
  `CHAR`,
  `STRING`,
  `TINYTEXT`,
  `MEDIUMTEXT`,
  `LONGTEXT`,
  `INTEGER`,
  `INT`,
];

what do you think about this idea ?

[alwint3r]

Somehow the test on postgresql fails. Any reason why you remove INTEGER and INT from possibleNumericTypes ?

[xeleniumz]

because if declare INTEGER and INT in variable possibleNumericTypes when you search it will response no data in table until you enter words match with data i will give you an example when you have number in database like 1590,1591,1592 and if you search with 15 in datatables will not show anything

[alwint3r]

I see your point. My only concern is that the inter-op with other database engines supported by sequelize. Perhaps I should start another module for handling datatables with sequelize and mysql as database engine? Because as far as I know, you can't do something like searching data with 15 as search parameter among numeric data like 1590,1591,1592 in postgresql.

[xeleniumz]

in my opinion you can check database adapter before query. it can resolve this problem.

[alwint3r]

I thought so. For now, I'll just release the fix for the previous issue while I'm working on the current issue. Thanks for your feedback!

[xeleniumz]

thank you too for the great lib i'm really happy to help you improve this lib 👍