SQL JOIN on DataTables?
SQL JOIN on DataTables?
birdj1
Posts: 11Questions: 2Answers: 0
Hi,
Is it possible to generate a DataTable from multiple SQL tables as my database is arranged over structure of 2 tables. I noticed this was possible with the paid Editor program and I wondered if it were possible on the free version of DataTables.
If not, can anyone suggest an alternative library that supports JOIN statements?
Thank you very much,
Jordan
This discussion has been closed.
Answers
You can join as many tables as you like using Editor or your own SQL queries! Just use Ajax.
https://datatables.net/examples/ajax/
@rf1234 Hi, thanks for the reply! Is it possible to insert my own query? I've only ever used the configuration example within the Server-Side Processing demos which I take it build queries on the fly?
Don't know about the demos but yes as I said you can use your own queries and feed the result of your queries into Ajax so that it gets displayed by Data Tables on the front end.
@rf1234 Would that support the server side processing? As the tables have 30,000 plus columns so a "SELECT *" would crash the site. I need to make use of DataTables' server side processing for sorting, searching, and pagination
That should work because server side processing is a Data Tables feature. But I would say there is a caveat. If you really want to do server side processing without Editor you will need to do quite a bit of work on the server side yourself. If you use Editor you will only have to write one or two lines of code to use server side. It is really easy and works immediately.
This is what you need to deal with if you do server side processing without Editor:
https://datatables.net/manual/server-side
Sounds to me that you need to create a view of just the columns need for your data table then create a stored procedure that does your paging and searching, etc
I am in no way associated with Data Tables and I don't get commission
But I like the product very much. Let me put it like this: If your hourly labor costs are $5+ you better get an Editor license and don't code it all yourself ...
The Editor seems like a great product with a lot of useful features for applications but it's a shame that I'd have to buy an entire license for all of them, just to use one SQL feature
Hi,
I am the author of DataTables, and I'm here to tell you... [game show host chatter...]
In all seriousness, DataTables itself is a client-side Javascript library. It doesn't "care" where the data comes from - an SQL server with joined tables, an XML file, ducks carrying hard disks. As long as the data is given to it as JSON (or something that can be converted to a Javascript array/object) then DataTables will be able to display it.
So the key thing here is that you need to write a server-side script that will query the database in the way you want and output JSON. Then DataTables can make an Ajax request to get that data and display it.
The goal of DataTables is not to provide the data access scripts on the server-side. There are more server-side languages and environments than I can count!
Allan
If you're using PHP for your server-side script, please see this answer on StackOverflow.
See more articles about jQuery DataTables on gyrocode.com.
That should be qualified with: assuming you need server-side processing
. Its relatively unusual that it is actually required, but that doesn't stop everyone from trying it! You should only need it if you have tens of thousands or more records.
Allan
@birdj1: You mentioned you have 30,000 records. This might still work fine without server side processing. I have a table with 65,000 records which still works acceptably without server side processing. It works a bit faster with server side processing ... and for me it is only one extra line of code to enable it ...
Server side processing may also have downsides. Let me give you an example. The searching is not done on the client side but on the server BEFORE getFormatting. This has consequences. If you search a server side table for a date you would need to enter a date in the mysql format, YYYY-MM-DD. Most users will not really like this. In particular when they see the date as DD.MM.YYYY as in a German language front end. Or you search for a float number. The decimal point has to be a decimal point in that case. It must not be a decimal comma as you would have in a German language front end.
@allan: Are you aware of the issues with the searching when using server side processing? Is there a solution for this? e.g. a way to transform the search string in javascript before it gets passed on to the server.
You can use
ajax.datato do that, but really it would be super complicated to do that. Consider what to do if the user types in "08", do you search for a date of the 8th of the month, August, or just look for that string in all columns?The only way to do it completely is to have the SQL server do whatever formatting you need and then search the result, rather than attempting to do the search anywhere else.
Allan
well that also sounds pretty complicated too, if you want to use Editor's php libraries. Would require different updatable views depending on the user's language. It would be a lot easier if Editor's serverSide processing searched the results AFTER getFormatting, wouldn't it?
Certainly would be - but the problem with that is that the entire data set needs to be retrieved from the database so it could be formatted and filtered. It would also require the filtering (and thus paging) to be done outside of the database. Any performance benefit of using server-side processing would more or less be mitigated using that approach I'm afraid.
Allan
makes sense, thanks for the clarification!
https://stackoverflow.com/questions/31004884/how-to-join-two-tables-with-ssp-class-php/31007028#31007028 see this topic - very useful