All Ticketmatic data is stored in a Postgres relational database. The tables in this database are not directly accessible. Ticketmatic instead provides a set of publicly available views on the database, called the
public data model. The public data model gives you fine grained, low level access to the data stored in Ticketmatic. Access is limited to read-only. For higher level access or read/write access use the Ticketmatic API.
Using the public data model you can:
- create custom Filters
- create custom Field definitions to be used in Views, Ticket layouts or Documents
- create custom Reports
- use the console to perform ad-hoc queries
- perform ad-hoc queries through the API by using the tools/queries api endpoint
The views in the public data model are contained in the namespace
tm, so you should use this as prefix to reference a view in the public data model, for example
The public data model is roughly structured in 4 parts:
- events, tickets and prices
- orders and saleslog
Events, tickets and prices
This part contains the information on the defined events, tickets and pricing. The most important views are:
Orders and saleslog
This part contains the information on actual orders and sold tickets. The most important views are:
This part contains information on the contacts. The most important views are:
This part contains information on the settings. Some important views are:
Below you will find some examples to get you started. Go to Settings -> Console to try out these examples for yourself.
To retrieve a list of all the Pricetypes available:
1 select * from tm.pricetype
To retrieve a list of the last 10 payments:
1 select 2 id, 3 paidts, 4 orderid, 5 amount 6 from tm.payment 7 order by id desc 8 limit 10
You can join tables to combine info. For example to retrieve contact info for the last 10 orders:
1 select 2 tm.order.id, 3 contact.firstname, 4 contact.lastname 5 from tm.order 6 left join tm.contact on tm.order.contactid=contact.id 7 order by tm.order.id desc 8 limit 10
To make a summary for the number of orders per month for orders in 2014:
1 select 2 date_trunc('month',tm.order.createdts), 3 count(*) 4 from tm.order 5 where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01' 6 group by date_trunc('month',tm.order.createdts) 7 order by date_trunc('month',tm.order.createdts)
To get a list of all contacts that created an order in 2014:
1 select 2 id, 3 firstname, 4 lastname 5 from tm.contact 6 where id in ( 7 select distinct contactid from tm.order where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01' 8 ) 9 order by lastname, firstname
Multilanguage fields have a different value for each language. The name for an event is an example of a multilanguage field. In the public data model you will always retrieve the value of these fields for all active languages. As a convention, we use
<fieldname><languagecode> as name for the language-specific value for the field.
In an account with languages en and nl active, you can try out:
1 select id, nameen, namenl from ev.event
This will return both the english and the dutch translation of the name field for an event.
Consult the reference for each table for more detailed info on available fields and foreign keys.