Creating Custom Query in OpenTicket

Your question is exceptionally vague. Usually if you provide a hint as to what you are trying to accomplish, we can guide you in the right direction. That being said, I will tell you some generic information related to what you are asking.

Most of the reservation data is held in the wp_qsot_event_zone_to_order table. This is what links an order, to a ticket type, to an order item, to an event. From this table you can divine most of the basic information you need to seek further information about a ticket.

Once you identify the appropriate rows in this table, you have several things at your disposal. You have an order_id, order_item_id, event_id, and product_id (ticket_type_id). Using each of those ids, you can find out the discreet information about each moving part, using the conventional wordpress methods.

With the order_id, you can look in the wp_postmeta table, and get all the information about the order. This includes the total, shipping method, billing information, and various other mundane facts. With the new WooCommerce, you may find it better to use the functions built into their plugin combined with the order_id instead though, at least for simplicity sake.

Using the order_item_id, you can grab the information about a specific order item attached to an order. This information resides in the wp_woocommerce_order_items and wp_woocommerce_order_itemmeta tables. Similar to the orders themselves though, you will find it much easier to grab this information from an order item, by using the built in woocommerce functions. Most likely you will need to load the order first, then jump to the order item.

With the product_id (ticket_type_id) you can use the wp_postmeta table to find all the information about the ticket product. This includes price, stock, sale information, and other product specific information. Much like the order though, you will find it easier to grab all that information if you use the WooCommerce functions to do so, especially since the latest major version of the plugin.

The event_id can be used to grab most of the relevant information from the event. The most important information from there is the _event_area_id, which can be used to load the event area. Once you have that, you can load the event area. The event-area itself holds the rest of the event information, such as capacity, ticket-type, and venue.

All in all, this should give you an idea of how the data all works together. If you have a specific question, or a specific end goal, you can share that, and I could offer some further guidance.

Loushou

Hello sorry I did not mean to be so vague, but wrote the post in a bit of a rush.
Basically I have been writing a custom post query so that I can have a “More Dates” area below the event. I did run the meta array and find the _event_area_id but as the top level event does not seem to have an _event_area_id I could not use this as the query.
At the moment I am using categories, but this doesn’t seem to be that stable, as it looks like when you update the parent event it clears the categories of the child events? Is there a better way to go about this query?

function ycd_get_workshops($atts)
{
	$atts = shortcode_atts(
		array(
			'category' => '44',
		), $atts, 'get_workshops' );
	
	
	
	$return='';
	
	global $post;

	$currentID = get_the_ID();
	$currentTitle = get_the_title();
    $currentImage = the_post_thumbnail_url(thumbnail);
    $cur_cat = $atts['category'];	
    
	
	$args = array(
	'post_type' => 'qsot-event',
	'post_status' => 'publish',
	'numberposts' => '-1',
    'cat' => $cur_cat
  );

$workshops = get_posts($args);
if(count($workshops))
{
	$return .='<div class="moreworkshops"><h3>More Dates Available<h3><ul>';

	foreach($workshops as $post): setup_postdata($post);
		$id = get_the_ID();
		$url = get_permalink($id);
		$name = get_the_title();
	    $start = get_post_meta($id, '_start', true);
	    $end = get_post_meta($id, '_end', true);
	    
	    $eventAreaID = get_post_meta($id, '_event_area_id', true);  
	
	if(!empty($eventAreaID))
	{
	
	   $return .='<li><img src="' . $currentImage . '" alt="/>' . $name . '"<a href="' . $url . '"target="_blank">' . $name . '</a> </li>';
	
	}
   endforeach; wp_reset_postdata();
    $return .= '</ul></div>';
}
  else {
    $return .= '<p>No Workshops Found.</p>';
	
  }
  

  return $return;
}

add_shortcode('get_workshops', 'ycd_get_workshops')

Hey @rmsgreig,

If you want to show a list of events that are of the same parent event, that is pretty easy. Each event post has a post_parent id. That id corresponds with the parent event. You can then use that parent id to find all the child events of that parent event. Then you also want to limit the results to only those in the future. You can do all this with the core WordPress, like so:

function get_other_events( $child_event_id, $limit ) {
  $child_event = get_post( $child_event_id );

  $other_child_events = get_posts( array(
    'post_type' => 'qsot-event',
    'post_status' => 'publish',
    'post_parent' => $child_event->post_parent,
    // events that start in the future
    'meta_query' => array(
      array(
        'key' => '_start',
        'value' => date( 'c' ), // now
        'compare' => '>',
      ),
    ),
    'meta_key' => '_start',
    'orderby' => 'meta_value',
    'meta_type' => 'DATETIME',
  ) );

  return $other_child_events;
}

Also if you want to get a list of all the events that happen at the same event-area, you can do something similar:

function get_other_events( $child_event_id, $limit ) {
  $child_event = get_post( $child_event_id );
  $event_area_id = get_post_meta( $child_event->ID, '_event_area_id', true );

  $other_child_events = get_posts( array(
    'post_type' => 'qsot-event',
    'post_status' => 'publish',
    // events that start in the future
    'meta_query' => array(
      array(
        'key' => '_event_area_id',
        'value' => $event_area_id,
      ),
      array(
        'key' => '_start',
        'value' => date( 'c' ), // now
        'compare' => '>',
      ),
    ),
    'meta_key' => '_start',
    'orderby' => 'meta_value',
    'meta_type' => 'DATETIME',
  ) );

  return $other_child_events;
}

Hope this helps simplify what you need,
Loushou

• This reply was modified 7 years, 1 month ago by loushou.

great thanks, I will try these out tomorrow

Hello,
Sorry for the delay in responding, I tried both these queries out yesterday and the first one returns different events not all the events of the same name with different dates and the second returns no results at all, any ideas?

Many Thanks

Hello, just wondered if you had an update on this please?

Do you think there would be a reason the query would not be returning results?
Thanks

I can not explain that until 1904, but it works for example. In 1899 he resets the date to the current day