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.9.4 (stable) is now available! Go get it now

Search: added support for response searches as well

I noticed that when I searched a word which I knew was in a ticket response (was a command to fix an issue), I was expecting it to find it in the ticket which i was looking for.... er... but no results.

Eventually I found it but decided to make the system find text within the responses as well.

in include/staff/tickets.inc.php search line #108 and change as below. if you can't find 108, just search for the word 'sucks'.. :P The line added is the TICKET_RESPONE_TABLE line.
        //This sucks..mass scan! search anything that moves!
$qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
\" OR ticket.name LIKE '%$queryterm%'\".
\" OR \".TICKET_RESPONSE_TABLE.\".response LIKE '%$queryterm%'\".
\" OR ticket.subject LIKE '%$queryterm%'\".
' ) ';


The go to line #161 and change as below.
$qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,email,dept_name,'.TICKET_RESPONSE_TABLE.'.response'.
',status,source,isoverdue,ticket.created,pri.* ';
$qfrom=' FROM '.TICKET_RESPONSE_TABLE.', '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.
' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() ';

Comments

  • Updated Source for Expanded Search. Not working.

    I updated the source as you specified, but I am not getting the expanded results. Any suggestions?
  • said:

    I updated the source as you specified, but I am not getting the expanded results. Any suggestions?

    Did you copy and paste or type the suggested code?

    I find that when I copy and paste code from this website for some reason it breaks the functionality. When I type it out it works for me. I am not in a place where I can try this with my own install but plan on adding it later today. I will let you know my results.
  • Doesn't work for me either.
  • Tried manually entering code, still no go.

    Thanks for the suggestion. I tried entering the code manually instead of copying and pasting. I am using vi through an ssh connection to my web server, so I assumed manually inputting vs. copying wouldn't make a difference and it didn't.

    So in short, still no success. It would be great to get this expanded search working correctly.
  • This mod also corrupts the ticket count when you're not searching for anything. It adds the number of responses to the number of tickets -- which is strange because it means the sql is working to pull the records, but the filter isn't finding matches in the response table.
  • Here are the fixes to make this work. The ticket count is still wrong, however, when just viewing all open or closed tickets.


    //This sucks..mass scan! search anything that moves!
    $qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
    \" OR ticket.name LIKE '%$queryterm%'\".
    \" OR responses.response LIKE '%$queryterm%'\".
    \" OR ticket.subject LIKE '%$queryterm%'\".
    ' ) ';
    }


    .. then further down, make these changes (note, you can remove ticket.updated, appearing directly before pri.* below. I am using that field to show when the ticket was last updated elsewhere in the code. Leaving it won't hurt anything.)


    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,email,dept_name,responses.response'.
    ',status,source,isoverdue,ticket.created,ticket.updated,pri.* ';
    $qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
    ' LEFT JOIN '.TICKET_RESPONSE_TABLE.' responses ON ticket.ticket_id=responses.ticket_id '.
    ' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.
    ' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() ';


    More logic is necessary to correct the inaccurate ticket count when you're not searching for anything.
  • said:


    More logic is necessary to correct the inaccurate ticket count when you're not searching for anything.

    Here is the fix. In include/staff/tickets.inc.php, change this:



    //get ticket count based on the query so far..
    $total=db_count(\"SELECT COUNT(*) $qfrom $qwhere\");
    //pagenate



    to this:



    //get ticket count based on the query so far..
    $total=db_count(\"SELECT COUNT(DISTINCT ticket.ticket_id) $qfrom $qwhere\");
    //pagenate

  • After update expanded search or responses works.

    Thanks for the code updates, I can now search responses in tickets as well as the title. I'll play around and see if I can expand it further to search internal comments as well.

    Thanks again! :)
  • said:

    Thanks for the code updates, I can now search responses in tickets as well as the title. I'll play around and see if I can expand it further to search internal comments as well.

    Thanks again! :)

    I just discovered that this application apparently does not search message content either. I'm kind of at a loss as to explain why that is, as that would be the bulk of data in which one would be searching for a term.

    Perhaps the developer would like to address that?? This is even referenced in the comments of the code:


    if($searchTerm){
    //Match only what we see on the screen...
    //No messages or answers search at this level ONLY when viewing ticket.


    ..but.. there is no message level searching that I can find, so I'm not sure to what he's referring by the "ONLY when viewing" section of the comment.

    I've made changes to the base query for this, and do have it searching messages now as well, but am hesitant in posting as there must be some reason why it wasn't included by default.
  • Update to search messages

    The developer hasn't posted regarding this, so I'm going to post the mod to allow you to search the message body (not just the subject).


    //This sucks..mass scan! search anything that moves!
    $qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
    \" OR ticket.name LIKE '%$queryterm%'\".

    //djtremors mod / fixed by Montclair
    \" OR responses.response LIKE '%$queryterm%'\".

    // Montclair mod to add searching of message body.

    \" OR messages.message LIKE '%$queryterm%'\".

    \" OR ticket.subject LIKE '%$queryterm%'\".
    ' ) ';



    then further down...


    //Montclair revised query with \"lastupdated\" calculated field & message body searching.
    //Includes Assigned To mod, and corrected searching responses mod.
    //Also moved original developer's code for attachments into the main query (saw no reason to have it separated.)

    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,email,dept_name,responses.response'.
    ',status,ticket.source,isoverdue,ticket.created,pri.*'.
    ',GREATEST(ticket.created,IFNULL(ticket.updated,0),MAX(messages.created),MAX(IFNULL(messages.updated,0)),MAX(IFNULL(responses.created,0)),MAX(IFNULL(responses.updated,0))) AS lastupdated'.
    ',COUNT(attach_id) AS attachments'.
    ',messages.message';

    $qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
    ' LEFT JOIN '.TICKET_RESPONSE_TABLE.' responses ON ticket.ticket_id=responses.ticket_id '.
    ' LEFT JOIN '.TICKET_MESSAGE_TABLE.' messages ON ticket.ticket_id=messages.ticket_id '.
    ' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id '.
    ' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.
    ' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() ';

    //get ticket count based on the query so far..
    //Montclair revision to reflect accurate ticket count.
    $total=db_count(\"SELECT COUNT(DISTINCT ticket.ticket_id) $qfrom $qwhere\");
    //echo $total;

    //pagenate
    $pageNav=new Pagenate($total,$page,$pagelimit);
    $pageNav->setURL('tickets.php',$qstr.'&sort='.urlencode($_REQUEST['sort']).'&order='.urlencode($_REQUEST['order']));
    //
    //Ok..lets roll...create the actual query
    //ADD attachment count crap..
    //$qselect.=' ,count(attach_id) as attachments ';
    //$qfrom.=' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id ';

    $qgroup=' GROUP BY ticket.ticket_id';
    $query=\"$qselect $qfrom $qwhere $qgroup ORDER BY $order_by $order LIMIT \".$pageNav->getStart().\",\".$pageNav->getLimit();


    The above includes additional fields for other mods which should have no ill effect if you have not implemented them.

    I'll likely post all the mods I've assembled from others, and coded myself, and post it in a zip file later. As of now, I have a summary of the last message posted by each client displaying beneath each ticket.
  • Thanks again for the updates.
  • Wow! Thanks a lot!
  • Hmm where to edit

    where do we implement this change, which file?
  • disregard above

    ((LOL, disregard this, I missed the previous page as came in from search engine))
  • "assigned to" broken with this mod

    Hi,

    Please can you help ? I've been adding modifications to my php files and this was the final piece in the jigsaw - complete text search.

    Sadly, this has broken my "assigned to" field - which worked following these instructions : http://www.osticket.com/forums/showthread.php?t=1068

    Which parts of the "assigned to" instructions above, do I need to use (if any) with your full message search mod ? (your mod says it includes the assigned to mod, but I can't see it anywhere)

    If I try to combine this mod into the "assigned to working" tickets.inc.php file, I end up getting no results in my staff view.

    It doesn't appear to recognise the "staff.username" column in the field list.
    If I remove this from the $qselect statement then I do get the tickets in my staff view but everything appears as unassigned, with the option to "claim ticket", even though it may already be assigned.

    I've attached my working "assigned to" tickets.inc.php file, and the broken "assigned to with full text search" tickets.inc.php file.

    Any advice would be gratefully received.
    Thanks
    Steve
    tickets.inc.php.zip
    12K
  • Great job, Montclair!

    Thank you very much, Montclair.
    Mine, works like a charm.
    Keep up the good work, and Cheers!

    Best regards,
    Masino Sinaga
  • "assigned to" broken

    I'm having the exact same issue as stevewalsh is having above. I have deployed previously, the "assigned to" mod found here:
    http://www.osticket.com/forums/showthread.php?t=1068

    I now have a request to make the text of the messages searchable. So, I tried to implement the various things here, but unfortunately the best that I can get is searchable text...while the "Assigned To" field shows "not assigned" for every ticket. If I click on my link to "View Unassigned" tickets, it properly comes back with only those tickets which are actually unassigned...so I know the logic is still there....it's just not displaying properly for me.

    As stevewalsh mentioned, it seems to not like the staff.username addition to the $qselect statement. I did add back in a LEFT JOIN for the .STAFF_TABLE. which was required for the previous mod, but with that in place, my tickets window shows absolutely nothing.

    There is no sense in posting my tickets.inc.php file as it's pretty much identical to stevewalsh above.

    As stevewalsh noted, the notes say it takes into account the "Assigned To" mod, but I don't see evidence of that in the code....so there must have been a previous "Assigned To" mod which differed from the one that stevewalsh and I are using now.
  • I've got it working now with "Assigned To"

    In my tickets.inc.php, I did the following :

    //This sucks..mass scan! search anything that moves!
    $qwhere.=" AND ( ticket.email LIKE '%$queryterm%'".
    " OR ticket.name LIKE '%$queryterm%'".
    " OR responses.response LIKE '%$queryterm%'".
    " OR ticket.subject LIKE '%$queryterm%'".
    ' ) ';


    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.username,responses.response '.
    ',status,source,isoverdue,ticket.created,pri.* ';

    $qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
    ' LEFT JOIN '.TICKET_RESPONSE_TABLE.' responses ON ticket.ticket_id=responses.ticket_id '.
    ' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.
    ' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() '.
    ' LEFT JOIN '.STAFF_TABLE.' staff ON ticket.staff_id=staff.staff_id';


    I can still see who tickets are assigned to and I can search the contents of the ticket.

    I cannot search Internal Notes of the ticket...which is a bummer...hopefully will be able to figure this out and can add it.
  • strange that it doesnt search all fields in a ticket, i agree :)
    I got some other addons in the code here, so might not work - but u'll get an idea how to do it :)

    Have also integrated the client side (get info like name etc.) against active directory which i'll release later when im ready :)


    //This sucks..mass scan! search anything that moves!
    $qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
    \" OR ticket.name LIKE '%$queryterm%'\".
    \" OR ticket.subject LIKE '%$queryterm%'\".
    \" OR responses.response LIKE '%$queryterm%'\".
    \" OR messages.message LIKE '%$queryterm%'\".
    \" OR note.note LIKE '%$queryterm%'\".
    \" OR note.title LIKE '%$queryterm%'\".
    ' ) ';




    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,ticket.subject,ticket.name,ticket.email,dept_name,responses.response,staff.username '.
    ',status,ticket.source,isoverdue,ticket.created,pri.* ';
    ',GREATEST(ticket.created,IFNULL(ticket.updated,0),MAX(messages.created),MAX(IFNULL(messages.updated,0)),MAX(IFNULL(responses.created,0)),MAX(IFNULL(responses.updated,0))) AS lastupdated'.
    ',COUNT(attach_id) AS attachments, messages.message, note.note';
    $qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
    ' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.
    ' LEFT JOIN '.TICKET_RESPONSE_TABLE.' responses ON ticket.ticket_id=responses.ticket_id '.
    ' LEFT JOIN '.TICKET_MESSAGE_TABLE.' messages ON ticket.ticket_id=messages.ticket_id '.
    ' LEFT JOIN '.TICKET_NOTE_TABLE.' note ON ticket.ticket_id=note.ticket_id '.
    ' LEFT JOIN '.STAFF_TABLE.' staff ON ticket.staff_id=staff.staff_id'.
    ' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() ';


    Regards,
    Chris :)
  • Hi

    Thanks for this mod!



    I installed the Help Topic MOD and modified my staff/tickets.inc.php so the Help Topic is shown in the ticket overview. So far so good..

    But I can't search for the help topics. Can anybody tells me how to modified this part in tickets.inc.php?

                $qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
    \" OR ticket.name LIKE '%$queryterm%'\".

    //djtremors mod / fixed by Montclair
    \" OR responses.response LIKE '%$queryterm%'\".

    // Montclair mod to add searching of message body.

    \" OR messages.message LIKE '%$queryterm%'\".

    \" OR ticket.subject LIKE '%$queryterm%'\".
    ' ) ';
    }


    I tried " OR ticket.topic_id LIKE '%$queryterm%'". but with no result :(


    Regards
    slashdot

    still no idea how to search for help topics?


    sorry for pushing the thread but I need this feature and I can't figure it out on my own..
  • I love try&error ;)

    include/staff/tickets.inc.php line ~114

    \" OR topic.topic LIKE '%$queryterm%'\".



    include/staff/tickets.inc.php line ~175


    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,responses.response,staff.username '.
    ',status,ticket.source,isoverdue,ticket.created,topic,topic.topic,pri.*'.



    include/staff/tickets.inc.php line ~188

           ' LEFT JOIN '.TOPIC_TABLE.' topic ON ticket.topic_id=topic.topic_id '.




    Full version with all my MODs:


            //This sucks..mass scan! search anything that moves!
    $qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
    \" OR ticket.name LIKE '%$queryterm%'\".
    \" OR responses.response LIKE '%$queryterm%'\".
    \" OR messages.message LIKE '%$queryterm%'\".
    \" OR ticket.subject LIKE '%$queryterm%'\".
    \" OR ticket.topic_id LIKE '%$queryterm%'\".
    \" OR note.note LIKE '%$queryterm%'\".
    \" OR note.title LIKE '%$queryterm%'\".
    \" OR topic.topic LIKE '%$queryterm%'\".
    ' ) ';
    }



    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,responses.response,staff.username '.
    ',status,ticket.source,isoverdue,ticket.created,topic,topic.topic,pri.*'.
    ',GREATEST(ticket.created,IFNULL(ticket.updated,0),MAX(messages.created),MAX(IFNULL(messages.updated,0)),MAX(IFNULL(responses.created,0)),MAX(IFNULL(responses.updated,0))) AS lastupdated'.
    ',COUNT(attach_id) AS attachments'.
    ',messages.message';

    $qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
    ' LEFT JOIN '.TICKET_RESPONSE_TABLE.' responses ON ticket.ticket_id=responses.ticket_id '.
    ' LEFT JOIN '.TICKET_MESSAGE_TABLE.' messages ON ticket.ticket_id=messages.ticket_id '.
    ' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id '.
    ' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.
    ' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() '.
    ' LEFT JOIN '.TICKET_NOTE_TABLE.' note ON ticket.ticket_id=note.ticket_id '.
    ' LEFT JOIN '.TOPIC_TABLE.' topic ON ticket.topic_id=topic.topic_id '.
    ' LEFT JOIN '.STAFF_TABLE.' staff ON ticket.staff_id=staff.staff_id';


    time for lunch :D

    cheers
    slashdot
  • Latest release (RC5) supports deep search using FULLTEXT match.
  • said:

    Latest release (RC5) supports deep search using FULLTEXT match.

    I take that back - after testing fulltext search further, I've decided to go back to using LIKE. Latest release updated.
  • Searching for numbers in internal note text?

    I have been trying to figure this one out for days now if I enter in the following in the note content of an internal note "7777 test 8888" .

    Now I try to do a basic search on 7777 it comes up with nothing however if I put in 7777 and a space after it pulls up the correct results.

    If I put in the word test it returns the correct results yet if I put in 8888 with a space before or after it does not return any results.

    Can any one explain this or suggest how to fix this?

    Or maybe I should start with can anyone duplicated my issue in there database?

    Thanks-
    Khris
Sign In or Register to comment.