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

Bleistivt

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?

R_J

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

x00

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();

R_J

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);

x00

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

R_J

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_J

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

x00

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.

x00

UNION basically is two queries put together.

x00

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 .

businessdad

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

x00

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.

Bleistivt

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_J

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.

x00

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.

x00

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

businessdad

@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_J

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.

Bleistivt

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.

vrijvlinder

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

peregrine

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