Relate ost_thread_entry to ost_ticket

gmalsack

Hello everyone!

Our customer service manager wants to be able to do a key word search on the body of tickets. i.e. the body of all threads in a ticket.

Does anyone know how to relate the thread entries to the tickets they belong to?

ntozier

Something like this SQL query where [ticket_id] is the ticket that you want to match against.

SELECT * FROM `ost_thread_entry` 
INNER JOIN `ost_thread` ON (`ost_thread`.`id` = `ost_thread_entry`.`thread_id`) 
INNER JOIN `ost_ticket` ON (`ost_ticket`.`ticket_id` = `ost_thread`.`object_id`) 
WHERE `ost_ticket`.`ticket_id` = [ticket_id];

gmalsack

ntozier THANKS!!!!

ntozier

Very welcome. You will still have to do your LIKE comparison for searching but I figure that this is a good place to start.

gmalsack

Well this is weird.... I've created the report page using the query you've provided. However the resulting thread_id doesn't match the ticket_id. I replaced the where clause with ost_thread_entry.body like "%keyword%", then tried using the thread_id as the ticket_id. It seems to be 1 decimal higher. i.e. opening scp/tickets.php?id=28444 doesn't bring up the keywords that were found, however opening scp/tickets.php?id=28443 does. Seems to be the same for all of the results....

ntozier

Admittedly I'm not super good at SQL and joins. Maybe someone else or a dev can assist you further
@KevinTheJedi any suggestions?

KevinTheJedi

@gmalsack

Our customer service manager wants to be able to do a key word search on the body of tickets. i.e. the body of all threads in a ticket.

This is already supported..? Just type something in the search box and click the search icon. This will search in thread entries as well.

Type and Click

Results

Proof

Cheers.

gmalsack

Thanks @KevinTheJedi However, when we performed this same search it wasn't returning the results. Only tickets where the key word entered by our CS Manager was in the subject. Tickets with the keyword in the body didn't show up in the list.

Anyway long story short, I've already created the search interface and have satisfied the CS managers need. However, he's found that searching by keyword isn't giving him the results he would like. Thus he's changed his request from searching for key words to tagging a ticket with a pre-defined category/type/label... That's where I'm at now. Trying to figure out how to do that. See post https://forum.osticket.com/d/97655-close-ticket-to-a-custom-queue if you have suggestions.