41–50 of 61 posts
Earlier quoted context omitted.
There are extremely few modern cases where pagination at the database layer is a good approach.
How else do you do it? Select the entire table and filter it at the application layer?
I feel the same about the query language of Mongo. The first goal of a query language should be ease of use. With mongo having to type all those extra characters quotes, brakets, square brakets, colons is very annoying. You need to type a lot to get any reasonable output.
db.orders.aggregate [
$group:
_id:
cust_id: "$cust_id"
ord_date: "$ord_date"
total:
$sum: "$price"
,
$match:
total:
$gt: 250
]MongoDB is a NoSQL-type database, so it wouldn't make sense to have a SQL query interface... I think they did a good job with the API for not using SQL. Plus, the API isn't really abusing JSON. It isn't pretty, but it's not abuse.
People seem to be confusing "NoSQL" and "non-relational". Mongo happens to be both, but there's nothing fundamental that puts the two together. You may not get the full capabilities of SQL with non-relational data (JOINs, etc), but there's no reason that non-relational data stores couldn't parse normal SQL and execute the appropriate queries. You can make a relational database that doesn't support the SQL syntax, and…
Earlier quoted context omitted.
There are extremely few modern cases where pagination at the database layer is a good approach.
This is an interesting statement to me. My goal in retrieving data is normally to reduce the result set as fast as possible. If I only need a subset of the data, I would do the bare amount of joining, aggregation, etc. before paginating. I assume the alternative is paginating server-side, which wastes some network bandwidth and processing time on the server.
The vast majority of pagination procedures occurs on the final rendered set. Meaning you've done 100% of the work on the database server to retrieve n% of the product. And when you come back for page 2, you're again doing 100% of the work for n% of the product. Pagination is not a SQL shortcut, and the sole "savings" it provides is network bandwidth between app server and database, which is seldom a limitation.
Earlier quoted context omitted.
There are extremely few modern cases where pagination at the database layer is a good approach.
How about any cases where you don't want to transfer all the rows you're skipping over the wire? As in, nearly all cases?
You picked an ugly mongo query, and there are many. You compared it to a concise SQL query, and there are many that are not. MongoDB's limit(x) and skip(y) are a shitload nicer than most of Microsoft's ideas about pagination. It was only in SQL Server 2012 that they came up with "OFFSET" instead of "google it".... http://stackoverflow.com/questions/2244322/how-to-do-paginat...
There are extremely few modern cases where pagination at the database layer is a good approach.
Earlier quoted context omitted.
not to take away from any of your great points in this post but isn't it the same as: SELECT posts.* FROM posts INNER JOIN post_tags pt ON pt.post_id = posts.id AND pt.tag IN ('foo', 'bar', 'baz') WHERE posts.author IN ('Joe', 'Jane') ORDER BY post_date DESC; My SQL is rusty, I could be missing something but they seem essentially equivalent if you use the SQL helpers such as IN.
No, because that'll select any post that contains any of those three tags, not the posts that contain all three tags. AND vs OR. It's worth noting that I threw the SQL query a bone by denormalizing post_tags into one table. In a properly relational DB, you'd have a tags table, a posts table, and a post_tags join table, so the query gets even hairier (or you have to do two queries). SELECT posts.* FROM posts INNER JOI…
SELECT * FROM post
WHERE id IN
(
select post_id from post_tags
INNER JOIN tags ON post_tag.tag_id = tag.id
WHERE tag.tag in ("foo","bar","baz")
GROUP BY post_id
HAVING COUNT(*) = 3
)
AND author IN ("Joe","Jane")
ORDER BY post_date DESC;
SELECT * FROM post
WHERE id IN
(
select post_id from post_tags
WHERE tag in ("foo","bar","baz")
GROUP BY post_id
HAVING COUNT(*) = 3
)
AND author IN ("Joe","Jane")
ORDER BY post_date DESC;Earlier quoted context omitted.
No, because that'll select any post that contains any of those three tags, not the posts that contain all three tags. AND vs OR. It's worth noting that I threw the SQL query a bone by denormalizing post_tags into one table. In a properly relational DB, you'd have a tags table, a posts table, and a post_tags join table, so the query gets even hairier (or you have to do two queries). SELECT posts.* FROM posts INNER JOI…
SELECT * FROM post WHERE id IN ( select post_id from post_tags INNER JOIN tags ON post_tag.tag_id = tag.id WHERE tag.tag in ("foo","bar","baz") GROUP BY post_id HAVING COUNT(*) = 3 ) AND author IN ("Joe","Jane") ORDER BY post_date DESC; Of course normalizing tags is silly as they are natural keys so it should be as you first stated: SELECT * FROM post WHERE id IN ( select post_id from post_tags WHERE tag in ("foo","b…