HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

SQL/Gdn_Model: Get previous and next entry WHERE...

Maybe someone with SQL knowledge can help me out here:
I'm trying to fetch the previous and the next entry for an ID from the database.

I can't just go by the ID, because I need a where clause, e.g. "previous post by this user in that thread"

What is the "best" (most efficient) way to do this and how would I do it using Gdn_Model?

«1

Comments

  • R_JR_J Ex-Fanboy Munich Admin

    Sounds like this is no job for SQL, but if I had to, I would do it that way:

    SELECT ID
    FROM TABLE
    WHERE ID > KnownID
    ORDER BY ID ASC
    LIMIT 1

    SELECT ID
    FROM TABLE
    WHERE ID < KnownID
    ORDER BY ID DESC
    LIMIT 1

    But that is really not efficient and ugly as hell...

  • x00x00 MVP
    edited September 2014

    first take a look a look at models how you extend an use Gdn_Model

    LIMIT 1 isn't necessarily inefficient, query engine should optimize that. @businessdad is the person to ask regarding SQL matters.

    $Result = $this->SQL
       ->Select('t.*')
       ->From('Table t')
       ->Where('t.UserID', $UserID)
       ->Where('t.ID >', $CurrentID)
       ->Limit(1)
       ->Get()
       ->Result();
    

    grep is your friend.

  • R_JR_J Ex-Fanboy Munich Admin
    edited September 2014

    Oh yes, you've asked for the Garden syntax, sorry. x00 already gave that information.

    Maybe you can avoid 2 database calls by using a union

    $SQL = Gdn::SQL();
    
    $SqlNext = $SQL
        ->Select('ID')
        ->From('Table')
        ->Where('ID >', $KnownID)
        ->OrderBy('ID', 'asc')
        ->Limit(1)
        ->GetSelect();
    
    $SqlPrevious = $SQL
        ->Select('ID')
        ->From('Table')
        ->Where('ID <', $KnownID)
        ->OrderBy('ID', 'desc')
        ->Limit(1)
        ->GetSelect();
    
    $Result = Gdn::Database()->Query($SqlNext.' union all '.$SqlPrevious);
    
  • I don't there will be much difference between two database call and union. Also best use abstraction of the model.

    grep is your friend.

  • R_JR_J Ex-Fanboy Munich Admin

    I like views. Don't know if that is more efficient because it could be cached, but if you need more than on Previous/Next value, it would be great to get it with a simple query.

    public function Setup() {
        $SQL = Gdn::SQL();
    
        $ViewSql = $SQL
            ->Select('t.ID')
            ->Select('p.ID', 'max', 'PreviousID')
            ->Select('n.ID', 'min', 'NextID')
            ->From('Table t')
            ->Join('Table p', 'p.ID < t.ID', 'left outer')
            ->Join('Table n', 't.ID < n.ID', 'left outer')
            ->GroupBy('t.ID')
            ->GetSelect();
    
        $SQL->View('V_PreviousAndNext', $ViewSql);
    }
    
    ...
    
    $Result = $SQL
        ->Select('ID, PreviousID, NextID')
        ->From('V_PreviousAndNext')
        ->WhereIn('ID', $IDs)
        ->Get();
    

    I haven't tested if it works and I guess it could only work if Table is sorted by ID.

  • R_JR_J Ex-Fanboy Munich Admin

    @x00 said:
    I don't there will be much difference between two database call and union. Also best use abstraction of the model.

    I thought that every connection to the db is bad and should be avoided thus I thought about combining them.
    What do you mean by "abstraction of the model"? Do you mean not using SQL directly? I totally agree on that, but for subselects and unions, there is no possibility in Garden to avoid that, as far as I know.

  • x00x00 MVP
    edited September 2014

    he want to make a model, not just make calls.

    Every call isn't necessarily bad, not alt all.

    A single query may actually less efficient that two. In this case I don't think you will get much difference.

    GetSelect has some uses, but you are forfeiting some useful abstraction which you need to plug back in.

    grep is your friend.

  • UNION basically is two queries put together.

    grep is your friend.

  • x00x00 MVP
    edited September 2014

    Out in the weird and wonderful world of no-SQL, you are actively encourage to make short calls where necessarily.

    It is your responsibility to come up with an efficient data model, and define relationships between data. It can be very fast indeed done well.

    SQL is a little bit different becuase it is relational, however it doesn't treat similar queries exactly the same way, it has an optimizer that figure out the more efficient way to retrieve that data.

    That is not to say the optimizer is always right. It is going to have tendencies (often deliberately so) and those tendencies are known to SQL engine experts .

    grep is your friend.

  • businessdadbusinessdad Stealth contributor MVP
    edited September 2014

    @R_J said:
    Sounds like this is no job for SQL

    I second that. From the description, it looks like a "navigator" of some sort (something like next comment, previous comment), which is usually best avoided, due to the excessive workload it involves. Essentially, it adds two extra queries every time a comment is retrieved, and that's very inefficient.

    Regarding the actual implementation, the solution described by @R_J and @x00, i.e. running two separate queries, is the correct one. I cannot think of a more efficient way to implement it.

    Update
    A union won't make a much of a difference, as it's simply made by two queries which are executed separately. I would recommend not to go with JOINs, because they could be even slower.

  • x00x00 MVP
    edited September 2014

    Essentially, it adds two extra queries every time a comment is retrieved, and that's very inefficient.

    I didn't realise he was doing it for every comment, I assumed he was just doing it for the session user.

    I agree there shouldn’t be a loop of queries. If you have to do then you would pre query and create lookup. The query would be complex though, it would make more sense to stash the CommentIDs per UserID and DiscussionID, store on save.

    grep is your friend.

  • Thank you all, that was very helpful to me.

    Basically I'm in the process of building a plugin that adds a little tooltip to mentions to show the last comment of the mentioned user in that thread.
    This will be ajax-loaded, only if a user hovers over the mention, so it will only be a single query + framework.

    Also, I might want to build something with a prev/next functionality in the future, which is why I asked the question in a more general way :)

  • R_JR_J Ex-Fanboy Munich Admin
    edited September 2014

    I would do the query on Comment Save. If you extend the Comment table by one column and fill it whenever someone writes and saves a comment, you'll have to do the "PreviousCommentIDQuery" only once.
    After that you can build the link for your AJAX call with this information.

    You would have to build a fallback for comments that have been started before plugin activation, though.

  • x00x00 MVP
    edited September 2014

    I would do the logic on Comment Save. If you extend the Comment table by one column and fill it whenever someone writes and saves a comment, you'll have to do the "PreviousCommentIDQuery" only once. After that you can build the link for your AJAX call with this information.

    That what is not he is asking, he is asking for the last comment for the mentioned user in the discussion if it exists.

    If you were going to store anywhere it would be another table LastComment table with dicussionid/userid key.

    @Bleistivt I would be careful of hover triggered ajax, it may go mental. You may want limit requests sent out.

    grep is your friend.

  • You may also want to precahe mention data, and send that to the client, getting rid of the need for ajax.

    grep is your friend.

  • businessdadbusinessdad Stealth contributor MVP

    @Bleistivt said:
    This will be ajax-loaded, only if a user hovers over the mention, so it will only be a single query + framework.

    I agree with @x00. Be careful with Ajax, you might find yourself killing your site involuntarily with dozens of requests. Some sort of caching, possibly external, would be highly recommended.

    Also, I might want to build something with a prev/next functionality in the future, which is why I asked the question in a more general way :)

    This is the sort of navigator which I'm famous for removing from all the projects :)

  • R_JR_J Ex-Fanboy Munich Admin

    Yes, you are right, I got it wrong! So every mention-tooltip shall show the latest comment (max(CommentID)) in the discussion and not the last comment (GDN_Comment.CommentID < $CommentID) before this one?
    Or shall it show not the latest but the next one (GDN_Comment.CommentID > $CommentID)?

    Only latest is dynamic. All other are static and can be loaded when the discussion view is called, but that could seriously blow up the page content.
    If you load it dynamically, I'd be interested in ways to cache that many data - simply out of curiosity.

  • Most of this would happen on the client side, only if the comment is not on the same page and wasn't already loaded into the dom, it will make a request, otherwise, the script just pulls it from the page or a cache variable. But yes, I will have to throttle requests, thanks for the suggestion.

    The idea is to eliminate the need to scroll up or go back a page when asking yourself "what is the user responding to?"

    Pre-caching / sending it to the client is a good idea, but since threads and comments are very dynamic (comments may be edited or deleted) this would involve synchronizing the table on edit/delete actions

    @businessdad said:
    This is the sort of navigator which I'm famous for removing from all the projects :)

    You see this a lot in gallery scripts, etc. and I think phpBB has this for threads too (which is useless) so I thought there might be a trick to make it efficient.

  • vrijvlindervrijvlinder Papillon-Sauvage MVP

    @peregrine wrote the ToolTip plugin , maybe that can give you an idea how it retrieves part of the discussion , and apply it to comments .

  • @vrijvlinder said:
    peregrine wrote the ToolTip plugin , maybe that can give you an idea how it retrieves part of the discussion , and apply it to comments .

    actually its pretty much unrelated and two different things, and Bleistivt is light years ahead.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Sign In or Register to comment.