• 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

#1
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?
 

AndyB

Well-known member
#2
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
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?
 

AndyB

Well-known member
#10
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 ...?
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.
 
#12
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.
 

AndyB

Well-known member
#14
That combination will actually be quite slow Andy, since that's not how the search system works.
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:

Jeremy

Well-known member
#15
Using the %LIKE% query will be slow, and duplicates the search effort. Searching using elastic search that querying based on ID is entirely different.
 

AndyB

Well-known member
#17
Using the %LIKE% query will be slow, and duplicates the search effort. Searching using elastic search that querying based on ID is entirely different.
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.
 

Jeremy

Well-known member
#18
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%.
 

AndyB

Well-known member
#19
However, using Elastic Search and then using %LIKE% will be slow. Plain and Simple.
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.

The Enhanced Search also doesn't use a combination of Elastic Search and %LIKE%.
No but Xenforo does use a combination on ES and MySQL as I'm suggesting to do.