CODEWITHSUNDEEP
mysql
stef
stef

Reputation: 27789

MySQL WHERE, LIMIT and pagination

I have tables: documents, languages and document_languages. Documents exist in one or more languages and this relationship is mapped in document_languages.

Imagine now I want to display the documents and all of its languages on a page, and paginate my result set to show 10 records on each page. There will be a WHERE statement, specifying which languages should be retrieved (ex: en, fr, it).

Even though I only want to display 10 documents on the page (LIMIT 10), I have to return more than 10 records if a document has more than one language (which most do).

How can you combine the WHERE statement with the LIMIT in a single query to get the records I need?

Upvotes: 6

Views: 296

Answers (4)

user2460464
user2460464

Reputation: 485

Hmmmm... so, if you post your query (SQL statement), it might be easier to spot the error. Your outermost LIMIT statement should "do the trick." As Rakesh said, you can use subqueries. However, depending on your data, you may (probably) just want to use simple JOINs (e.g. where a.id = b.id...).

This should be fairly straightforward in MySQL. In the unlikely case that you're doing something "fancy," you can always pull the datasets into variables to be parsed by an external language (e.g., Python). In the case that you're literally just trying to limit screen output (interactive session), check-out the "pager" command (I like "pager less;").

Lastly, check-out using the UNION statement. I hope that something, here, is useful. Good luck!

Upvotes: 0

randiel
randiel

Reputation: 290

I created these tables:

create table documents (iddocument int, name varchar(30));
create table languages (idlang char(2), lang_name varchar(30));
create table document_languages (iddocument int, idlang char(2));

Make a basic query using GROUP_CONCAT function to obtain the traspose of languages results: 

select d.iddocument, group_concat(dl.idlang) 
from documents d, document_languages dl 
where d.iddocument = dl.iddocument 
group by d.iddocument;

And finally set the number of the documents with LIMIT option:

select d.iddocument, group_concat(dl.idlang) 
from documents d, document_languages dl 
where d.iddocument = dl.iddocument 
group by d.iddocument limit 10;

You can check more info about GROUP_CONCAT here: http://dev.mysql.com/doc/refman/5.0/es/group-by-functions.html

Upvotes: 0

Ohlin
Ohlin

Reputation: 4178

You can add a little counter to each row counting how many unique documents you're returning and then return just 10. You just specify what document_id to start with and then it returns the next coming 10.

SELECT document_id,
    if (@storedDocumentId <> document_id,(@docNum:=@docNum+1),@docNum), 
    @storedDocumentId:=document_id
FROM document, document_languages,(SELECT @docNum:=0) AS document_count
where @docNum<10 
    and document_id>=1234 
    and document.id=document_languages.document_id
order by document_id;

Upvotes: 0

Rakesh Soni
Rakesh Soni

Reputation: 10907

Use sub query to filter only documents records

select * from 
(select * from documents limit 0,10) as doc, 
languages lan, 
document_languages dl
where doc.docid = dl.docid
and lan.langid = dl.langid

Check sub query doc as well

http://dev.mysql.com/doc/refman/5.0/en/from-clause-subqueries.html http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Upvotes: 1

Related Questions