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

[nothingatall]

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]

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

 

[nothingatall]

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]

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

 

[Jeremy]

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

 

[nothingatall]

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

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 ...?

 

[nothingatall]

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

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.

 

[Jeremy]

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

 

[nothingatall]

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

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.

 

[AndyB]

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.

 

[Jeremy]

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

 

[nothingatall]

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.

 

[Jeremy]

This will help you along the way:
http://xenforo.com/community/resources/understanding-the-xenforo-class-proxy-system.2080/

It needs to extend and catch all edits / new posts. If you need help, you should post in Development Discussions.

 

[AndyB]

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.

 

[Jeremy]

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

 

[nothingatall]

This will help you along the way:
http://xenforo.com/community/resources/understanding-the-xenforo-class-proxy-system.2080/

It needs to extend and catch all edits / new posts. If you need help, you should post in Development Discussions.

Thank you. I will definitely head into that forum when I begin this process.

 

[AndyB]

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]

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]

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.