MySQL WHERE, LIMIT and Pagination

Question

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

Imagine that I want to display documents and all its languages ​​on a page and break 10 readings on each page into my resulting objects. There will be a WHERE statement that determines which languages ​​should be extracted (for example: en, fr, it).

Despite the fact that I want to display only 10 documents per page ( LIMIT 10 ), I need to return more than 10 entries if the document contains more than one language (which is most important).

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

Answer 1

Use sub-query to filter only document 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 also the subtitle file

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

Answer 2

You can add a little counter to each line by counting the number of unique documents that you return and then return only 10. You simply indicate where to start document_id, and then it returns the next next 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; 

Answer 3

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

Create a basic query using the GROUP_CONCAT function to get the data conversion 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 documents with the LIMIT parameter:

 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 learn more about GROUP_CONCAT here: http://dev.mysql.com/doc/refman/5.0/es/group-by-functions.html

Answer 4

Hmmmm ... so if you post your query (SQL statement), it would be easier to spot the error. Your outer LIMIT expression should do the trick. As Rakesh said, you can use subqueries. However, depending on your data, you may (possibly) just want to use simple JOINs (for example, where a.id = b.id ...).

This should be pretty simple in MySQL. In the unlikely event that you are doing something “fancy”, you can always pull data sets into variables that will be parsed by an external language (like Python). In the case when you are literally just trying to limit the screen output (interactive session), check the "pager" command (I like the "pager less").

Finally, exit using the UNION statement. Hope something here is helpful. Good luck