n1crack / datatables

Simplify your Datatables server-side processing effortlessly using our lightning-fast PHP library, streamlining your workflow seamlessly.
https://datatables.ozdemir.be/
MIT License
267 stars 90 forks source link

Mysql table with dashes in name causes error #102

Closed 13ran closed 5 months ago

13ran commented 5 months ago

Tables with dashed column names causes an error

This works:

$dt->query("SELECT
    `test-test` AS `test`
FROM
    `test`");

This causes an error:

$dt->query("SELECT
    `test-test`
FROM
    `test`");
n1crack commented 5 months ago

I'm checking.

n1crack commented 5 months ago

I think I made it on purpose since it is not recommended to use dash in the columns. Using dashes (-) in column names is generally discouraged because many programming languages, databases, and tools interpret the dash as a subtraction operator or other special character.

you can use underscore instead.

13ran commented 5 months ago

If the column name has `` around it, it shouldn't cause any problems. I'd really like to avoid renaming all the columns in my database as it already has millions of rows and selecting as another name causes issues when I try and filter the column

n1crack commented 5 months ago

what about using like this:

$dt->query("SELECT
    `test-test` AS `test_test`
FROM
    `test`");

In this case, you dont have to rename your columns.

13ran commented 5 months ago

Causes issues with filtering as test_test doesnt exist in the database. Unless theres some way I can change the column?

$dt->filter('test_test', function () {
     return $this->column = '`test-test`';
});

That doesn't work but is there a way to do that?

n1crack commented 5 months ago

Not sure whats wrong.. Its working for me as expected.

image

n1crack commented 5 months ago

image

and this is the default filtering

n1crack commented 5 months ago

Can you show me the error you get ? maybe there is another problem ?

13ran commented 5 months ago

Sorry you are correct it does work. My request was just timing out because it was doing a LIKE search.

How do I make it do an exact search? This is the data being sent to the server

columns[9][data]: phone_number
columns[9][name]: 
columns[9][searchable]: true
columns[9][orderable]: true
columns[9][search][value]: ^123456$
columns[9][search][regex]: true

Also is there a way to filter on multiple columns with OR? Say for example the column being searched is phone_number I would like the query to be where phone_number = 'value' or some_other_column = 'value'

n1crack commented 5 months ago

When you type in search input, all columns that has searchable: true are being search with "LIKE" and merged with 'OR'.

Lets say, you need to filter by full text, then you can bypass default filter like :

    $dt->filter('test_test', function () {
        return $this->column->name.' = '. $this->escape($this->searchValue());
    });
13ran commented 5 months ago

Thanks. Now how would I make that filter apply only when searchValue() has a value?

n1crack commented 5 months ago

Ah sorry, here it is:

   $dt->filter('test_test', function () {
        if ($this->searchValue() === '') {
            return;
        }
        return $this->column->name.' = '. $this->escape($this->searchValue());
    });
13ran commented 5 months ago

I just looked at the getQuery() log and that still causes the search term to be added to the query when its empty

n1crack commented 5 months ago

Yea you are right, can you try this one ?

   $dt->filter('test_test', function () {
        if ($this->searchValue() === '') {
            return '';
        }
        return $this->column->name.' = '. $this->escape($this->searchValue());
    });
13ran commented 5 months ago

That works. Thanks again for all the help!!