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

Queries from Forms

Has anyone figured out a way to create a query to pull data from the forms? OST_FORM_ENTRY_VALUES does not have anything that links it with the ticket that it was opened under. I've gone through most of the tables and don't see any that do this. Am I overlooking one of them?

Comments

  • I believe you'll need a join with form_entry where the form_id is 2 (tickets form), and the object_type is T (Tickets), the object_id will be the ticket ID. Yours might be different, to do it properly you'd need to join with 'form', because there are two on mine, L1 for statuses and T for ticket.
    Now the entry values table can be joined on the entry_id, you'll need to know the field ID to get useful values, so if you want to join again this time with form_field.

    Slightly more complicated than just bunging them onto the tickets table, but much more dynamic, more normalised, joins aren't that painful for a properly indexed database. Would be great if this was documented in the table definitions!

    You could inspect the foreign key relationships to figure it out I suppose... bahahahaha, I jest, there are none.





  • Holy balls.. 

    Ok, so I edited /include/class.orm.php MysqlExecutor changed the constructor to this:

        function __construct($sql, $params) {
            $this->sql = $sql;
            $this->params = $params;
            if(stripos($sql,'form') !== false){ // filter out non-form queries
                print($sql .' , ' . print_r($params,true) . "<br />");
            }
        }

    .. yeah.. 

    Turns out it's many, many queries to get data out of those fORM's.. :-(

    Woah. Try viewing a ticket. 

    At each field you see the queries being executed.  Before the header there is two sets of the exact same query being run (I assume on Ticket load).. a bit of detective work and some trial queries, extrapolating and joining with tickets, would give something like:
    SELECT t.*, v.* 
    FROM `ot_form_entry_values` v
    JOIN `ot_form_entry` e ON e.id = v.entry_id
    JOIN `ot_ticket` t ON e.object_id = t.ticket_id
    JOIN `ot_form_field` A1 ON (v.`field_id` = A1.`id`) WHERE v.`entry_id` = 
     (SELECT `ot_form_entry`.id FROM `ot_form_entry` WHERE `ot_form_entry`.`object_id` = t.ticket_id AND `ot_form_entry`.`object_type` = 'T' ORDER BY `ot_form_entry`.`sort` ASC LIMIT 1 )
    ORDER BY A1.`sort` ASC

    Damn.  That dependent subquery is SLoooOOooOw. 16 seconds on my uber-server.. (returning 200000 rows)

    Joining a bit more, and doing some optimisations, changing things into usable values (I only have default fields, Subject & Priority), I got it down to 0.0095s (50 Open tickets) with:
    SELECT t.ticket_id,s.name, t.number as public_id, d.dept_name, sla.name, v.value as subject, v2.value as priority, u.name, IFNULL(CONCAT(staff.firstname, ' ', staff.lastname),'Unassigned') as Assigned_Staff, IFNULL(team.name,'') as Team, IFNULL(ht.topic,'') as Topic
    FROM `ot_form_entry_values` v
    JOIN `ot_form_entry_values` v2 ON v.entry_id = v2.entry_id AND v2.field_id = (SELECT id FROM `ot_form_field` WHERE label = 'Priority Level')
    LEFT JOIN `ot_form_entry` e ON e.id = v.entry_id
    LEFT JOIN `ot_ticket` t ON e.object_id = t.ticket_id
    LEFT JOIN `ot_form_field` A1 ON (v.`field_id` = A1.`id`) 
    LEFT JOIN `ot_ticket_status` s ON s.id = t.status_id
    LEFT JOIN `ot_department` d ON d.dept_id = t.dept_id
    LEFT JOIN `ot_sla` sla ON sla.id = d.sla_id
    LEFT JOIN `ot_help_topic` ht ON ht.topic_id = t.topic_id
    LEFT JOIN `ot_team` team ON team.team_id = t.team_id
    LEFT JOIN `ot_user` u ON u.id = t.user_id
    LEFT JOIN `ot_staff` staff ON staff.staff_id = t.staff_id
    WHERE 
    s.state = 'open' AND
    v.`entry_id` = 
     (SELECT `ot_form_entry`.id 
      FROM `ot_form_entry` 
      WHERE `ot_form_entry`.`object_id` = t.ticket_id AND `ot_form_entry`.`object_type` = 'T' 
      ORDER BY `ot_form_entry`.`sort` ASC LIMIT 1 )
    GROUP BY t.ticket_id
    ORDER BY t.ticket_id DESC


    Yummy.
    Removing the "open" filter, took 27 seconds.. returned all 70k tickets. Woo. 

    YMMV, obviously if your prefix is "ost_", change all the "ot_"'s above into ost_ etc.. 
  • Thanks again for the help Grizly! I'll give it a shot. I haven't messed with DB queries in over a year, and even then it was all DB2, so I'm kinda relearning here.
  • No worries mate, I'm thinking it would need more abstraction to deal with i18n and things, but for basic English reports it's got some of what you might need.
    You might be able to embed that dependant query with multiple ON parameters in the JOIN.. 

    That is much faster! (Same fields selected, same data returned, 70k rows in 1.8s!)

    SELECT t.ticket_id,s.name, t.number as public_id, d.dept_name, sla.name, v.value as subject, v2.value as priority, u.name, IFNULL(CONCAT(staff.firstname, ' ', staff.lastname),'Unassigned') as Assigned_Staff, IFNULL(team.name,'') as Team, IFNULL(ht.topic,'') as Topic
    FROM `ot_form_entry_values` v 
    LEFT JOIN `ot_form_entry_values` v2 ON v.entry_id = v2.entry_id AND v2.field_id = (SELECT id FROM `ot_form_field` WHERE name = 'priority')
    LEFT JOIN `ot_form_entry` e ON e.id = v.entry_id
    LEFT JOIN `ot_ticket` t ON e.object_id = t.ticket_id AND e.`object_id` = t.ticket_id AND e.`object_type` = 'T'
    LEFT JOIN `ot_ticket_status` s ON s.id = t.status_id
    LEFT JOIN `ot_department` d ON d.dept_id = t.dept_id
    LEFT JOIN `ot_sla` sla ON sla.id = d.sla_id
    LEFT JOIN `ot_help_topic` ht ON ht.topic_id = t.topic_id
    LEFT JOIN `ot_team` team ON team.team_id = t.team_id
    LEFT JOIN `ot_user` u ON u.id = t.user_id
    LEFT JOIN `ot_staff` staff ON staff.staff_id = t.staff_id
    WHERE v.field_id IN (SELECT id FROM `ot_form_field` WHERE name IN ('priority', 'state'))
    GROUP BY t.ticket_id
    ORDER BY t.ticket_id DESC

    To add your fields, you should edit the WHERE condition to reflect the fields you want to include, and then keep rejoining.. 
    Modify SELECT Adding:
    v3.value as MyFieldColumnName

    Then after the v2 JOIN:
    LEFT JOIN `ot_form_entry_values` v3 ON v.entry_id = v3.entry_id AND v3.field_id = (SELECT id FROM `ot_form_field` WHERE name = 'YourDbFieldIdentifier')

    Then in WHERE 
    WHERE v.field_id IN (SELECT id FROM `ot_form_field` WHERE name IN ('priority','state','YourDbFieldIdentifier')

    Looks pretty good. You should be able to add as many fields as you want.
    The `name` is the backend version of the field that you set when you make one (so is language independant), or use one of the defaults: email,name,phone,notes,subject,message,priority,name,website,phone,address,name,address,phone,website,notes,state,description,
    While there appears to be duplicates, that's because the first name relates to form_id one "contact Information", the second is "Company Name" from form_id three, the third is Organization name from form_id 4..  Which you can imagine gets decoded by the ot_form_field table.
  • FYI: for automated csv generation, you could use one of the solutions here: http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
  • edited May 2016

    I changed the 7th LEFT JOIN to one particular help topic since that's the one I'm trying to pull reports for.

    LEFT JOIN `ost_help_topic` ht ON ht.topic_id = '14'

    But I'm getting "Subquery returns more than one row"

    I should also note we are not using SLA's so that table is empty.

    mysql> select count(*) from ost_sla;
    +----------+
    | count(*) |
    +----------+
    |        0 |
    +----------+

     

  • You probably want something like:

    LEFT JOIN `ost_help_topic` ht ON ht.topic_id = t.topic_id AND ht.topic_id = 14

    You can remove quite a few of the joins if you don't need the data they select. Either drop the select bit too or simply wrap it in an IFNULL like the example.
Sign In or Register to comment.