Search disabled with non alias fields

So I assigned a “Date” field to calendar_term and the search started working again. The only problem is when I add my where statement the search gets broken again and returns everything instead of nothing. Any tips on how to handle the where?

SELECT p.ID,p.post_title,pp.post_date,
CONCAT(SUBSTRING(p.calendar_end, 1, 4),'/',SUBSTRING(p.calendar_end, 5, 2),'/',SUBSTRING(SUBSTRING(p.calendar_end, 5), -2)) as calendar_term
FROM payments as p
LEFT JOIN payments_posts AS pp ON pp.post_id = p.post_id
where p.post_last_save_date = pp.post_date

Try not using your alias and keeping the calendar_term field as a date field in this plugin, just SELECT p.ID,p.post_title,pp.post_date,p.calendar_end

Then rename your ‘calendar_term’ field to ‘calendar_end’ instead.

I can’t do that. There are other fields that are based upon the current date so no matter what I have to change the logic around coming from the database. The only way I would be able to use p.calendar_end is if what was saved in the database didn’t need to be reworked. I know it doesn’t look like that field needs to be split up like that but there are other fields that definitely do. So I go back to asking why the WHERE statement breaks the search and if there is some way to get around it?

This statement works as long as you make “calendar_term” a date field.

SELECT p.ID,p.post_title,pp.post_date,
CONCAT(SUBSTRING(p.calendar_end, 1, 4),'/',SUBSTRING(p.calendar_end, 5, 2),'/',SUBSTRING(SUBSTRING(p.calendar_end, 5), -2)) as calendar_term
FROM payments as p
LEFT JOIN payments_posts AS pp ON pp.post_id = p.post_id

As soon as I add the WHERE to the bottom it returns all records instead of just one like the initial error caused by not assigning calendar_term to be a date field.

SELECT p.ID,p.post_title,pp.post_date,
CONCAT(SUBSTRING(p.calendar_end, 1, 4),'/',SUBSTRING(p.calendar_end, 5, 2),'/',SUBSTRING(SUBSTRING(p.calendar_end, 5), -2)) as calendar_term
FROM payments as p
LEFT JOIN payments_posts AS pp ON pp.post_id = p.post_id
where p.post_last_save_date = pp.post_date

Do I need to edit the way the plugin handles the query and if so what file and line number would that be? Thanks for your help.

Have you tried CONCAT(SUBSTRING(p.calendar_end, 1, 4),'/',SUBSTRING(p.calendar_end, 5, 2),'/',SUBSTRING(SUBSTRING(p.calendar_end, 5), -2)) as the alias for the calendar_term field?

You might also want to be sure your ID / post_title / post_date fields are using: p.ID, p.post_title, and pp.post_date as their corresponding aliases too.