1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.2 A way to show all quoted messages for a user

Discussion in 'Styling and Customization Questions' started by nothingatall, Sep 10, 2013.

  1. nothingatall

    nothingatall Member

    Been looking through the database to see if this information is stored there somewhere, but so far, I'm not having any luck finding it:

    I'm looking for a way to pull all the times a user has been quoted into one page. The alerts system is fantastic, but since it expires after a time-limit, I've had users want to have a record (similar to the the "likes I've received" option) that would let them be able to see all the times they've been quoted ... so I'm looking for a way to pull that information out of the alert feed and give them just the times they've been quoted, without it expiring.

    Any ideas?
     
  2. AndyB

    AndyB Well-Known Member

    You can use phpmyadmin to see how many times a member has been quoted.

    1) Bring up the xf_post table
    2) Click the Search tab
    3) use the LIKE %...% option on the message field
    4) Example of a search:

    [quote="nothingatall
     
  3. nothingatall

    nothingatall Member

    Hmmm, yeah ... that would work. Of course there's 18 million posts on the site, so that would be intensive. Maybe CRON an update to run that and spit the information into another table with USER_ID_of_quoater / user_id_of_quotee / post id? And then use that table instead? Wouldn't be updated as much, but could be less server intensive.

    Anyone have other ideas on that?
     
  4. AndyB

    AndyB Well-Known Member

    Curious many seconds it took to retrieve the data using the LIKE %...% option?
     
  5. Jeremy

    Jeremy XenForo Moderator Staff Member

    LIKE isn't the fastest query around. That's why ES and Sphinx exists for large boards.
     
  6. nothingatall

    nothingatall Member

    Yeah, using ES. Is there a way to leverage that to some degree and create the page I'm talking about? Using ES that is ...?
     
  7. nothingatall

    nothingatall Member

    I haven't actually run the query on my live site, just know in the past LIKE has been pretty slow -- especially if it's a page hit with any frequency.
     
  8. Jeremy

    Jeremy XenForo Moderator Staff Member

    The easiest would be to either build a search URL or get a custom add-on written that tracks quotes.
     
  9. nothingatall

    nothingatall Member

    That was kinda what I figured, that something written that would grab the ID when it's made would be the best bet.

    Crap. Ok. Thanks.
     
  10. AndyB

    AndyB Well-Known Member

    Yes you can use ES to return all posts with the word "nothingatall" and then run the MySQL query using the LIKE %...% on those results. Using this ES and MySQL combination should result in returning the final list very quickly.
     
  11. Jeremy

    Jeremy XenForo Moderator Staff Member

    That combination will actually be quite slow Andy, since that's not how the search system works.
     
  12. nothingatall

    nothingatall Member

    Yeah, my thoughts were that would be pretty brutal.

    I think that the best bet is to add some kind of relationship to a new table on post. user_id1 "quoted" user_id2 "in" post_id ... and then just pull that information separately.

    Now I just gotta figure out how to do that.
     
  13. Jeremy

    Jeremy XenForo Moderator Staff Member

  14. AndyB

    AndyB Well-Known Member

    I actually do this on my forum and it works great. It requires writing a script that is independent of Xenforo. The PHP script first queries ElasticSearch and the post_id's that match (I limit to 200 and sort by descending) are used to query MySQL.
     
    Last edited: Sep 11, 2013
  15. Jeremy

    Jeremy XenForo Moderator Staff Member

    Using the %LIKE% query will be slow, and duplicates the search effort. Searching using elastic search that querying based on ID is entirely different.
     
  16. nothingatall

    nothingatall Member

  17. AndyB

    AndyB Well-Known Member

    My working script only takes .03 seconds to return 200 post_id's. Those post_id's are of the most recent messages that I was quoted in. My xf_post table is 1.3 million rows.

    My understanding is the Xenforo also uses the same technique for all it's searches, that is it uses ES to to the initial query and then uses MySQL to query those results.
     
  18. Jeremy

    Jeremy XenForo Moderator Staff Member

    XenForo MySQL queries doesn't use %LIKE%, either. Fetching post IDs and querying based on post IDs using Elastic Search will be quick, yes. However, using Elastic Search and then using %LIKE% will be slow. Plain and Simple. The Enhanced Search also doesn't use a combination of Elastic Search and %LIKE%.
     
  19. AndyB

    AndyB Well-Known Member

    Depends on what you consider slow. On my forum it takes .03 seconds (1.3 million posts). I estimate it will take just a fraction longer on the OP's 18 million post forum.

    No but Xenforo does use a combination on ES and MySQL as I'm suggesting to do.
     

Share This Page