0

I will give you a simple example of what I want

let say that I have an articles table with the following ids (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

now the visitor want to sort this articles by article title then with the author name so the query will look like this

SELECT `articles`.`id`, `articles`.`title`, `articles`.`body`, `authors`.`id`, `authors`.`name` from `articles`
left join `authors` on `articles`.`author_id` = `authors`.`name`
ORDER BY `articles`.`title` ASC, `authors`.`name` ASC;

which may result the following order (5, 9, 8, 6, 4, 1, 3, 10, 2, 7)

then the visitor visits the detail page of article number 4, now from this page I want to navigate throw his results which is (5, 9, 8, 6, 4, 1, 3, 10, 2, 7)

so I want to catch the article number "6" as previous article and article number "1" as next article

how can I perform this operation using Mysql while in article details page I have just the article ID and the search criteria and search order.

Note: the solution should consider the performance because I have a table which may reach 1,000,000 row

Karim
  • 390
  • 4
  • 17
  • in mysql, not easily. in other dbs that have windowing functions, very easy. – Marc B May 06 '15 at 18:22
  • possible duplicate of [Select a row along with its next and previous rows](http://stackoverflow.com/questions/28966667/select-a-row-along-with-its-next-and-previous-rows) – AdamMc331 May 06 '15 at 18:26
  • If you preserve the position in the results the current article was in the original query, you can repeat the query with a "LIMIT pos+1, 1" to get to the next one. – Uueerdo May 06 '15 at 18:41

2 Answers2

0

In SQL, you would want to use something like ROW_NUMBER() OVER. It seems this isn't available in MySql; see this question.

Community
  • 1
  • 1
levelonehuman
  • 1,465
  • 14
  • 23
0

This is rather simple.

The previous article will be the one with the highest (so to speak) title, that is still less than the title of article 4. You can find that like this:

SELECT MAX(title) AS maxTitle
FROM articles
WHERE title < (SELECT title FROM articles WHERE id = 4);

If you want to get the id of that article, you can join it back to the original table:

SELECT a.id
FROM articles a
JOIN(
  SELECT MAX(title) AS maxTitle
  FROM articles
  WHERE title < (SELECT title FROM articles WHERE id = 4)) tmp ON tmp.maxTitle = a.title;

To get the next article, you just flip all of your conditions:

SELECT a.id AS nextArticle
FROM articles a
JOIN(
  SELECT MIN(title) AS maxTitle
  FROM articles
  WHERE title > (SELECT title FROM articles WHERE id = 4)) tmp ON tmp.maxTitle = a.title;

Here is an SQL Fiddle example that has rows ordered in the same way you do, and does return values 6 and 1 as you expect.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • this solution works well on this simple case, but what if the user has the ability to sort by another field which is in another table? let say the author name from table authors this is actually my case – Karim May 06 '15 at 19:14
  • You could use the same query, just replace title with author in the query @Karim – AdamMc331 May 06 '15 at 19:15
  • sorry I did not explain my case well, I mean that visitor may search by combination (articles.title asc, authors.name asc) – Karim May 06 '15 at 19:18
  • @Karim I understand now. I will have to play around with this, but one thing you can do is add an index to order the columns `1, 2, 3, 4` and then say if your current article is *index* 4, you can pull for the article at index 3. I will try to build an example. – AdamMc331 May 07 '15 at 14:31
  • I think that this task could be done with a very complex SQL statement that will affect the performance, I see that the most appropriate way to perform this task is to send the index of the article to details page url, so I can navigate easy from there. – Karim May 14 '15 at 07:32