|
sara.bosman
|
October 10, 2017 at 3:05 pm
I need to find events that start on a specific set of dates. Fragment of my code:
$startdates = array( '2017-11-10 09:00:00', '2017-07-03 08:00:00' );
$similar_events = EEM_Event::instance()->get_all(
array(
array(
'AND' => array(
'Datetime.DTT_order' => 1,
'Datetime.DTT_EVT_start' => array( 'IN', $startdates ),
'status' => 'publish'
)
),
'order_by' => array( 'Datetime.DTT_EVT_start' => 'DESC' )
)
);
The generated SQL:
SELECT [...] FROM wp_posts AS Event_CPT LEFT JOIN wp_esp_event_meta AS Event_Meta ON Event_CPT.ID = Event_Meta.EVT_ID LEFT JOIN wp_esp_datetime AS Datetime ON Datetime.EVT_ID=Event_CPT.ID WHERE Event_CPT.post_type = 'espresso_events' AND Event_CPT.post_status NOT IN ('auto-draft','trash') AND ( (Datetime.DTT_deleted = 0) OR Datetime.DTT_ID IS NULL) AND (Datetime.DTT_order = 1 AND Datetime.DTT_EVT_start <strong>IN ('2017-10-10 21:00:12','2017-10-10 21:00:12')</strong> AND Event_CPT.post_status = 'publish') ORDER BY Datetime.DTT_EVT_start
It just replaced all dates with the current date and time; any idea what I’m doing wrong?
-
This topic was modified 7 years ago by
 Tony. Reason: Code formatting
|
|
sara.bosman
|
October 10, 2017 at 3:07 pm
(btw: a post preview or edit would be nice; the markup syntax is quite unclear to me – looks like markdown, but apparently it isn’t)
|
|
Tony
|
October 11, 2017 at 2:48 pm
Hi Sara,
You can’t pass strings like above for the date, you need to pass a date object.
There are some details here: http://developer.eventespresso.com/important-changes-to-ee-datetime-system-coming-to-ee/
In the above example, you’re $startdates array would look something like this:
$startdates = array(
EEM_Datetime::instance()->convert_datetime_for_query('DTT_EVT_start','2017-11-10 09:00:00', 'Y-m-d H:i:s', 'UTC'),
EEM_Datetime::instance()->convert_datetime_for_query('DTT_EVT_start','2017-07-03 08:00:00', 'Y-m-d H:i:s', 'UTC')
);
To confirm, are you looking to pull events with either of those exact start dates/time?
|
|
sara.bosman
|
October 26, 2017 at 4:47 am
Sorry, missed your reaction. Yes, I’d like to find all other events in the same venue overlapping on one or more dates of this event.
|
|
Tony
|
October 26, 2017 at 4:52 am
Just to clarify what I mean.
You’re using ‘IN’ so the above is only going to pull events with a start date of 2017-11-10 09:00:00 or 2017-07-03 08:00:00.
So do you want IN or BETWEEN?
BETWEEN will pull all events with a start date between 2017-11-10 09:00:00 and 2017-07-03 08:00:00.
Either way the above should work it’s just not clear which events you want to pull from the database.
|
|
sara.bosman
|
October 26, 2017 at 5:28 am
Here’s the thing: we have a six-day event, but customers can also register for the first day only. We have created two separate events in EE: one for the full six days, and one for the first day only (called ‘try-out day’).
I want to pull out a list of attendees, but for that purpose, I also need to know the registrants of the other events in the same venue on the same datetimes (in this case: the single day event).
So the answer is: IN, not BETWEEN.
|
|
sara.bosman
|
October 26, 2017 at 5:34 am
Also, the two events do not necessarily have the same startdates, e.g. it should also be possible to create an event for days 3-4 only. Registrants for that event should appear on the list of attendees of the full 6 day event too. The only restriction is that it is always a subset of the main event.
|
|
sara.bosman
|
October 26, 2017 at 6:12 am
Actually I did find a solution based on your suggestion, although it is a little cumbersome (find all datetimes of the main event, find all events on these datetimes in the same venue, find all registrations of these events etc…).
Can’t I just find all registrations in a specific venue on a specific list of datetimes, whatever the event is, with one single query?
|
|
Josh
|
October 27, 2017 at 4:03 pm
You may be able to squeeze that into a single query with a few or more table JOINS. You may prefer to (or even need to) do more of a direct query using the wpdb class and its methods because it may get more cumbersome with the built in models.
At the very least the query will include the esp_event_venue table (relationship between event and venue),
the esp_datetime table (the datetime related to the event),
the esp_datetime_ticket table (relationship between the datetime and ticket),
the esp_ticket_table (their ticket that gives them access to the datetime),
the esp_registration_table (relationship between the ticket and the attendee ID)
and the esp_attendee_meta table (attendee name).
|
|
sara.bosman
|
October 30, 2017 at 2:26 pm
That was my plan B. Thanks for all the suggestions and the (as usual) swift response.
|
