Introduction

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 https //apps ticketmatic com/docs/api in order to retrieve data using the public data model, you use sql queries http //en wikipedia org/wiki/sql the supported sql syntax strongly conforms to the ansi sql 2008 standard http //en wikipedia org/wiki/sql 2008 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 https //www ticketmatic com/docs/api/tools/queries/ 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 tm pricetype structure the public data model is roughly structured in 4 parts events, tickets and prices orders and saleslog contacts settings events, tickets and prices this part contains the information on the defined events, tickets and pricing the most important views are tm event docid\ lzr3vbnf596zjjb69xllb tm tickettype docid\ cd8xjlojjt5o0drcpbnya tm ticket docid\ ng g3imt5nlu1ch0cq7bg tm pricetype docid\ d0zocwtsnznwvndp0ybfx tm pricelist docid 7lxhtzddwnza74oorm3hd tm tickettypeprice docid\ rsax5ugwv3f7ijegsonor orders and saleslog this part contains the information on actual orders and sold tickets the most important views are tm order docid jxcubxabuusuhkqcpean tm orderlog docid\ eael oek hpbdmidrjm5u tm payment docid\ ykfyakqenmlduujunmh f tm ticket docid\ ng g3imt5nlu1ch0cq7bg tm saleslog docid\ lqr90y r48izmxxvaugm tm saleslogitem docid\ e vijlzedvy5plkb53sdw tm batchprint docid\ jlxzmspzaj cnj0uvoeka contacts this part contains information on the contacts the most important views are tm contact docid\ ufix35lrtf5e4t erzomr tm contactaddress docid\ z11mfildjttv2jsgo2ent settings this part contains information on the settings some important views are tm paymentscenario docid 7dbvctoqsvqfzhsuczp m tm saleschannel docid\ puqmzwy lt3yr5ougfneo some examples 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 select from tm pricetype to retrieve a list of the last 10 payments select id, paidts, orderid, amount from tm payment order by id desc limit 10 you can join tables to combine info for example to retrieve contact info for the last 10 orders select tm order id, contact firstname, contact lastname from tm order left join tm contact on tm order contactid=contact id order by tm order id desc limit 10 to make a summary for the number of orders per month for orders in 2014 select date trunc('month',tm order createdts), count( ) from tm order where tm order createdts>='2014 01 01' and tm order createdts<'2015 01 01' group by date trunc('month',tm order createdts) order by date trunc('month',tm order createdts) to get a list of all contacts that created an order in 2014 select id, firstname, lastname from tm contact where id in ( select distinct contactid from tm order where tm order createdts>='2014 01 01' and tm order createdts<'2015 01 01' ) order by lastname, firstname multilanguage fields 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 select id, nameen, namenl from ev event this will return both the english and the dutch translation of the name field for an event more info consult the reference for each table for more detailed info on available fields and foreign keys