Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

MySQL Query for getting all open tickets

Hello!

Database structure is quite confusing for me. Can anybody please advise for the correct MySQL query in case to get a list of all open tickets, including all information like title and - if possible - also the ticket body?

Thank you :-)

P.S. It should basically be the same list like when you click on "open tickets" on upper left side.

Comments

  • This should help: http://osticket.com/forum/discussion/comment/116256/#Comment_116256

    I use something similar. 

    To get the "body", you'd need to also join with the thread_entry table joined from the thread table joined via object_id = ticket_id.. it get's complicated, but it's possible. 
  • OK but there is no object_id column is os_thread_entry


  • Select Distinct os_ticket.ticket_id As TicketNumber, os_user.name As Client, os_ticket.ip_address As IP, os_department.name As Departament, os_staff.username As Staff, os_ticket.source As Source, os_ticket.created As DataOn, os_ticket.duedate As DueDate, os_ticket.closed As ClosedDate, os_ticket.status_id As STATUS, os_help_topic.topic From os_ticket Join os_staff On os_staff.staff_id = os_ticket.staff_id Join os_department On os_ticket.dept_id = os_department.id Join os_user_email On os_ticket.user_id = os_user_email.user_id Join os_user On os_user_email.id = os_user.id Inner Join os_help_topic On os_ticket.topic_id = os_help_topic.topic_id

    + Inner Join os_thread_entry On os_ticket.ticket_id = os_thread_entry.object_id

    Would theoretically be correct then?
  • I would suggest that you take a look at the orm instead, it'll future proof your code and does all the joins for you.

    You're correct that there is no object_id column in %prefix%_thread_entry, %prefix%_ticket [ticket_id] -> %prefix%_thread [object_id] -> %prefix%_thread_entry [thread_id]
  • what is orm ?
  • It's the built in DB engine that lets you generate queries and work with them in PHP.


    and here's an example that will get you the tickets id, number, when it's created, title and all entries in the ticket.

Sign In or Register to comment.