Hi,

The following query runs fine in MySQL but I am getting an error when entering it in WpDataTables.

---

SELECT a.ID, r.post_title, a.post_title, a.guid, r.post_type, a.post_type, r.post_author
FROM (SELECT * FROM `wppm_2_posts` WHERE post_author = %CURRENT_USER_LOGIN% AND post_type = 'reviews' AND post_status = 'publish') as r
RIGHT JOIN `wppm_2_posts`as a 
ON r.post_title = a.ID
WHERE a.post_type = 'applicants'
AND a.post_status = 'publish'

---

I am getting the following error:

ERROR: cannot calculate position of as a within ) as r RIGHT JOIN `wppm_2_posts`as a ON r.post_title = a.ID WHERE a.post_type = 'applicants' AND a.post_status = 'publish'

I've seen a couple other support tickets where the solution is to create a view, however, because I'm pulling the current user's login ID, I don't think that I can use a view.

Am I missing something or is there a workaround that I can use to accomplish what I need?

Thanks!

Hi Milan,

Note: I realized I copied the wrong query before. It should have been %CURRENT_USER_ID% instead of %CURRENT_USER_LOGIN% but still getting an error.

Here is the error I get now:

ERROR: cannot calculate position of as a within ) as r RIGHT JOIN `wppm_2_posts`as a ON r.post_title = a.ID WHERE a.post_type = 'applicants' AND a.post_status = 'publish'

Does WpDataTables support subqueries? 

Attached is the query working in MySQL.

Thanks,

Jack

The table should be generated correctly as it works in MySQL. As mentioned before, I have a dynamic placeholder and a subquery... both of which wouldn't work with creating a view.

Any idea what the error is saying is the issue?

I am also not suing LIMIT or ORDER BY.

Here is the correct way of writing the query in case anyone finds themselves in a similar situation. This query works with WpDataTables

SELECT 
APPLICANTS.ID AS applicants_id
    ,APPLICANTS.post_title AS applicants_post_title
,APPLICANTS.guid AS applicants_guid
    ,APPLICANTS.post_type AS applicants_post_type
    ,REVIEWS.post_author AS reviews_author_id
    ,REVIEWS.post_type AS reviews_post_type
    ,REVIEWS.post_title AS reviews_post_title
,R_APP_SCORES.meta_value AS review_app_score
,R_APP_COMMENTS.meta_value AS review_app_comment
FROM 
`%WPDB%posts` AS APPLICANTS 
    LEFT JOIN
    `%WPDB%posts` AS REVIEWS ON
  (REVIEWS.post_author = %CURRENT_USER_ID%) AND     
(REVIEWS.post_title = APPLICANTS.ID) AND 
        (REVIEWS.post_type = 'reviews') AND 
        (REVIEWS.post_status = 'publish')
LEFT JOIN 
    `%WPDB%postmeta` R_APP_SCORES ON
(R_APP_SCORES.post_id = REVIEWS.ID) AND 
        (R_APP_SCORES.meta_key = 'review_app_score')
LEFT JOIN 
    `%WPDB%postmeta` R_APP_COMMENTS ON 
(R_APP_COMMENTS.post_id = REVIEWS.ID) AND
(R_APP_COMMENTS.meta_key = 'review_app_comment')
WHERE 
(APPLICANTS.post_type = 'applicants') AND
(APPLICANTS.post_status = 'publish')