What would you say is the most efficient way to do this query in MySQL? Say you have two tables:
blogs: id, title, post (E.g. 1, 'Why I like MySQL', 'MySQL is…')
categories: id, blog, name (E.g. 1, 1, 'Databases', 2, 1, 'Open Source')
How could I query a “page” of blogs with an offset and get a fixed number of blogs (e.g. 10 per page) but also all their categories? I understand MySQL doesn’t support arrays so was wondering if this could be done with one query rather than selecting the categories with a query per blog.
I don’t know what you mean with query a page of blogs with an offset
Blogs will need to have a foreign key if you want to know the category. That is, you need to add the field “category_Id” and your table would be:
Blogs: id, title, post, category_id (e.g. 1, ‘Why I like MySQL’, ‘MySQL is…’, 1)
That last 1 indicates that this blog belongs to the category 1, with the name of ‘Databases’
[quote=“nvrmnd1, post:3, topic:216526, full:true”]something like this
[/quote]
no, because if each blog has 2 categories, you’ll end up listing only 5 blogs
he wants 10 blogs, regardless of number of categories each blog has
SELECT b.id
, b.title
, b.post
, GROUP_CONCAT(c.name) AS categories
FROM blogs AS b
LEFT OUTER
JOIN categories AS c
ON c.blog = b.id
GROUP
BY b.id
ORDER
BY b.id DESC LIMIT 10
Just a further question on this. Is there a suggested way to separate chunks of data if you’re returning strings? For ints, for example, a comma is good enough but that’d likely break in text (e.g. if I had a comma in one of my blog categories). Creating a small random string to go between them would do it but just wondered if there was a built-in way.
You could also break this into two queries. One to fetch the paginated blog posts and another to fetch all categories for the retrieved blog posts. One more query isn’t going to make any significant performance impact and might even be faster. This will also make it easier to model the domain with data other than just the category name.
SELECT x,y,z FROM blogs LIMIT 10;
-- Create array of blog ids in result set to use in where clause for categories.
SELECT x,y,z FROM categories WHERE blog_id IN ({blog_id_list})
-- Map categories to each blog at the application level.
Than for instance when using PHP it would a simple of matter of just populating each blog with its categories. Something like.
foreach($categories as $category) {
$blogs[$category['blog_id']]['categories'][] = $category;
}
[quote=“DrQuincy, post:6, topic:216526, full:true”]
Just a further question on this. Is there a suggested way to separate chunks of data if you’re returning strings? [/quote]
yes, use a special delimiter of your choosing
see da manual for the DELIMITER syntax of the GROUP_CONCAT function
Yes, I thought of this way too but was interested in how it could be done in a single query. In my case I think the performance difference will be negligible and will probably use this method.
[quote=“DrQuincy, post:10, topic:216526, full:true”]
Do you mean SEPARATOR? [/quote]
yeah, SEPARATOR… sorry, mind not always working at 100% efficiency