SELECT u.firstName, u.lastName, u.email, b.intro AS copy, b.createdAt,
b.title, b.id AS blogId, b.categoryId, 'blog' AS type,
CAST(GROUP_CONCAT(DISTINCT r.roleid SEPARATOR ',') AS CHAR) AS view_roles
FROM blogs b
LEFT JOIN users u ON b.userid = u.id
LEFT JOIN roles r ON b.categoryid = r.categoryid AND r.type = 'view'
UNION
SELECT u.firstName, u.lastName, u.email,
c.comment AS copy, c.createdAt,
b.title, c.blogid, b.categoryId,
'comment' AS type,
CAST(GROUP_CONCAT(DISTINCT r.roleid SEPARATOR ',') AS CHAR) AS view_roles
FROM comments c
LEFT JOIN users u ON c.userid = u.id
LEFT JOIN blogs b ON c.blogid = b.id
LEFT JOIN roles r ON b.categoryid = r.categoryid AND r.type = 'view'
ORDER BY createdAt DESC
LIMIT 10
;
So I’m wanting to combine the blogs table and comments table into one, then limit to the 10 most recent which is fine, but it breaks down when I add the LEFT JOIN for the roles table (both queries). I’m needing the associated roles in a comma delimited list under the column ‘view_roles’. When I don’t include the join for the roles, I get an expected amount of rows returned. When it IS included it’s limiting the rows to 2.
Schema is as follows:
Users:
Blogs:
Comments:
Roles:
I feel like either I’m making a simple mistake OR the query has gotten too complex.
Adding GROUP BY createdAt just doesn’t seem correct. Still getting less than expected results.
The ‘difficult’ part of this query is I’m needing a column that is comma separated list of role id’s That I can compare on the application server (Coldfusion) on whether to show or not.
Just had a realization that if I limit to 10 in the database query that a user might not see anything (if they’re role list doesn’t apply).
Here the problem presented to me. They want a ‘facebook’ like list of the most recent blog posts and comments for blog posts (with newest posts first). Blogs are inside categories which have view permissions. If the person can’t see the category, they shouldn’t see the blog post in the list.
I’m starting to think that I need to be involving CF in on creating this query.
I feel like this SHOULD be pretty easy, it’s the whole converting columns to comma list that hangs me up.
yeah, it isn’t – you want to GROUP BY the category, to get all the roles for the category
SELECT u.firstName
, u.lastName
, u.email
, b.intro AS copy
, b.createdAt
, b.title
, b.id AS blogId
, b.categoryId
, 'blog' AS type
, r.view_roles
FROM blogs b
INNER
JOIN users u
ON u.id = b.userid
INNER
JOIN ( SELECT categoryid
, CAST(GROUP_CONCAT(roleid) AS CHAR) AS view_roles
FROM roles
WHERE type = 'view'
GROUP
BY categoryid ) AS r
ON r.categoryid = b.categoryid
UNION ALL
SELECT u.firstName
, u.lastName
, u.email
, c.comment AS copy
, c.createdAt
, b.title
, c.blogid
, b.categoryId
, 'comment' AS type
, r.view_roles
FROM comments c
INNER
JOIN users u
ON u.id = c.userid
INNER
JOIN blogs b
ON b.id = c.blogid
INNER
JOIN ( SELECT categoryid
, CAST(GROUP_CONCAT(roleid) AS CHAR) AS view_roles
FROM roles
WHERE type = 'view'
GROUP
BY categoryid ) AS r
ON r.categoryid = b.categoryid
ORDER
BY createdAt DESC LIMIT 10
in the FROM clause, subselects are often called derived tables or inline views (for reasons that are prettty evident in both cases)
subselects are also allowed in the SELECT clause, where they are called scalar subqueries, because here they are allowed to return only one row consisting of one column, i.e. a scalar value
yes
UNION is actually the same as UNION DISTINCT (the DISTINCT keyword is the default) and indicates that the entire result set will be scanned for duplicate rows – entire rows, all columns
UNION ALL skips this scan, and accepts all result rows, including duplicate rows, if any
in the case of your query, there won’t be any dupes, because one subselect generates rows with ‘blog’ and the other generates rows with ‘comment’
Off Topic:
thanks, mike… too bad the sitepoint coding style munged the sql code in my book… well, except for the leading comma convention, which, after some discussion, they begrudgingly accepted