Hi, Stacey

Thanks for reaching out to us.

In some situations, using certain functions within SQL, the wpDataTables server has problems with parsing the query and building new queries dynamically (rarely happens, but does sometimes).

Our logic is based on a PHP SQL parser which has full support for the SQL dialect for the following statement types

SELECT, INSERT, UPDATE, DELETE, REPLACE, RENAME, SHOW, SET, DROP, CREATE INDEX, CREATE TABLE, EXPLAIN and DESCRIBE.

Some of them are disabled for security reasons.

Filtering, sorting, calculation functions and search may not work properly if you include:

Accent graves ( ` ) around the table name, JOIN functions, UNION functions, CONCAT functions, sub-queries -

Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.
-

To avoid this, can you please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.

If you need help with that, here is our video on how to use MySQL Views with our plugin.

Let us know if this helped.

Stacey, my apologies.

I realised something else after the moment I sent you the first response there.

In your SQL Query, some corrections need to be made.

SELECT posts_terms.ID AS terms_ID,
       CONCAT('<a href="',posts_terms.guid,'">',posts_terms.post_title,'</a>') AS terms_title_with_link_to_post,
       LEFT( posts_terms.post_content, 100) AS terms_post_content_limited_100_chars
FROM wp_8_posts AS posts_terms
WHERE 1=1 
   AND posts_terms.post_type = 'terms'

- For example, where you placed "LEFT" , it is missing a part of the expression, it should be "LEFT JOIN";

and at the end, you need the condition as "ON".

Here is an example how a LEFT JOIN should look :

1. SELECT customers.customer_id, cust_name, price, date  
2. FROM customers  
3. LEFT JOIN orders ON customers.customer_id = orders.customer_id; 

A more detailed example on this page.

Please note: Using this plugin feature requires at least a basic knowledge of SQL. It is assumed that you can create the table in some MySQL data manager (e.g., PHPMyAdmin, MySQL Workbench), and prepare a SQL query that will return the data you need. If you do not know how to use MySQL, refer to the “Create a MySQL-query-based table by generating a query to MySQL database” section. It will explain how to create complete tables or MySQL queries without this basic knowledge. Please be advised that preparing MySQL queries for you is not included in the plugin support.

Additionally, here is our documentation page on creating SQL tables with our plugin. 

Hi, Stacey

My apology, i didn't realise you got the context of the query from our constructor.

Please note that our query constructors are not ‘ultimate generators’, and they should be treated as helpers for constructing a suggestion of a query, by trying to ‘guess’ what you want. We are constantly working to improve them, but SQL is such a complicated and flexible language that fully automating the process for constructing queries is hardly possible. Consequently, the more complicated your request is, the higher is the probability that it will not return exactly what you need. Play around with the resulting query to achieve better results.

- Let us know how it goes, and we will be here to assist as much as we can.