Rewrite sql to support joins

I have the following query which in oracle databases should work but current wordpress instalation uses a mysql database. Could you please help me out to rewrite the next query so it will work on mysql ?

$wpdb->get_results("SELECT a.ID, a.post_title, DAYOFMONTH(b.meta_value) as dom "
."FROM $wpdb->posts a, $wpdb->postmeta b "
."WHERE b.meta_value >= '{$thisyear}-{$thismonth}-01 00:00:00' "
."AND b.meta_value <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND   b.meta_key='Event Date'"
);

Solutions

There isn't an issue with your query in MySQL from what I can tell. Both of the following statements produce the same results (although I prefer to use the JOIN). Here is a simplified version:

SELECT  *
FROM table1 t 
    JOIN table2 t2 on t.id = t2.id
WHERE t.dt >= '2011-01-02 00:00:00';

SELECT  *
FROM table1 t, table2 t2
WHERE t.dt >= '2011-01-02 00:00:00' 
   AND t.id = t2.id;

SQL Fiddle Demo

You may have a problem with the date format of the constant. I would suggest converting the date into the same format using to_char(). I'm thinking something along the lines of:

SELECT a.ID, a.post_title, to_char(b.meta_value, 'DD') as dom "
."FROM $wpdb->posts a, $wpdb->postmeta b "
."WHERE to_char(b.meta_value, 'YYYY-MM-DD HH:MI:SS') >= '{$thisyear}-{$thismonth}-01 00:00:00' "
."AND to_char(b.meta_value, 'YYYY-MM-DD HH:MI:SS') <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND   b.meta_key='Event Date'

Or, more simply:

SELECT a.ID, a.post_title, to_char(b.meta_value, 'DD') as dom "
."FROM $wpdb->posts a, $wpdb->postmeta b "
."WHERE to_char(b.meta_value, 'YYYY-MM') = '{$thisyear}-{$thismonth}' "
."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND   b.meta_key='Event Date'

This assumes that meta-value is stored as a date. If not, you have to deal with conversion of values into dates. I'm making this assumption because you are using a function DAYOFMONTH.

Tags: Mysql / SQL / Wordpress

Similar questions

Query that joins the postmeta table twice
I believe what I'm trying to do is too complex for the standard meta_query parameter in get_posts, so I'm trying to use a custom SQL query to do the following: I have a game custom post that has custom fields for team1 and team2 stored as postmeta. Given team XXXX, I need to pull all posts that have the postmeta value of XXXX for either postmeta ke...
WP_Query hit max joins... How else can I build a search function that uses custom fields?
I'm trying to search a custom post type of "Properties", while doing so I'm also searching the custom fields using multiple (anywhere from 5-30) meta_queries. The problem is Wordpress or MySQL is throwing an error saying I hit the limit of the amount of JOIN statements that can be used. The error: [The SELECT would examine more than MAX_JOIN_SIZE r...
Optimising mysql query - many inner joins
I'm trying to optimise the following query in wordpress, as it takes nearly a minute and a half to return a result. The table relationships are expressed in the following diagram:
MySQL multiple Left Joins causing incorrect Count values
So I am trying to write a single MySQL query to replace what is currently being done in PHP with three separate queries. I have a table for Categories with only two values: an ID and a Name. There are two other tables which each have a foreign key referring to the ID from the first table. I need to get a list of all the values from the Categories t...
Mysql multiple joins to one table with different conditions -- where do they go?
I am querying for some posts in the wordpress posts table and grabbing multiple meta values with it by joining the postmeta table multiple times. I also have a number of conditions for both post columns and meta values. The query seems to be working but I am curious if I have put the conditions in the correct place. Comparing the following queries,...
How can I improve a slow custom query? (subquery + inner joins with custom fields)
I have a query that is used to return available rooms for a booking system Using Advanced Custom Fields, I have a custom post type representing reservation with various fields The plan here is to find all the booked rooms with the subquery, and then get all the available rooms by selecting the posts who are not in the result set returned by the sub...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.