group_concat() / left join troubles

I have two issues, a solution (either partial or whole) to either
would be tremendously appreciated. Let me first share my query and
then ask my questions.

SELECT SQL_CALC_FOUND_ROWS entries. * ,
GROUP_CONCAT( tags.tag ) AS tag_list
FROM entries
LEFT JOIN userdata ON userdata.entry_id = entries.entry_id
LEFT JOIN tags ON tags.entry_id = entries.entry_id
WHERE userdata.user = '$user'
AND tags.tag = $tag'
GROUP BY entries.entry_id
ORDER BY entries.date
DESC
LIMIT 0 , 30

I have to be honest... JOINS are bit a like magic to me.
Question 1: The AND tags.tag = $tag' part mucks up the GROUP_CONCAT
tag_list. Instead of returning a list of tags for the entry (as it
does without AND tags.tag = $tag'), tag_list only returns one tag
($tag itself). Without the AND tags.tag = $tag' part, the query
returns comma-separated tags as desired. What gives?

Question 2: I'm having trouble figuring out COUNT(), which is what I
believe I need to get the count for entries.type (entries.type is
either 'image', 'link', 'video', 'text'). This isn't nearly as
important as question one, but it would be nice to know.

Thanks in advance for entertaining my noob-ness.

On Sat, 17 May 2008 00:30:09 +0200, matth <matthud@gmail.com> wrote:

> I have two issues, a solution (either partial or whole) to either
> would be tremendously appreciated. Let me first share my query and
> then ask my questions.
>
> SELECT SQL_CALC_FOUND_ROWS entries. * ,
> GROUP_CONCAT( tags.tag ) AS tag_list
> FROM entries
> LEFT JOIN userdata ON userdata.entry_id = entries.entry_id

As you require a specific user, why the LEFT JOIN instead of a regular one?
JOIN userdata
ON userdata.entry_id = entries.entry_id
AND userdata.user = '$user'

> LEFT JOIN tags ON tags.entry_id = entries.entry_id
> WHERE userdata.user = '$user'
> AND tags.tag = $tag'

Surely: AND tags.tag = '$tag' ?

> GROUP BY entries.entry_id
> ORDER BY entries.date
> DESC
> LIMIT 0 , 30
>
> I have to be honest... JOINS are bit a like magic to me.
> Question 1: The AND tags.tag = $tag' part mucks up the GROUP_CONCAT
> tag_list. Instead of returning a list of tags for the entry (as it
> does without AND tags.tag = $tag'), tag_list only returns one tag
> ($tag itself). Without the AND tags.tag = $tag' part, the query
> returns comma-separated tags as desired. What gives?

You are telling the query you only want one specic tag, and then wonder
why it only gives you that one? What did you think you would accomplish by
adding that last bit? Maybe some explanation of what you are trying to do
is in order. If I had to guess, I'd say you want this (untested, I have no
sample data or desired outcome, or even the table structure):

SELECT SQL_CALC_FOUND_ROWS entries.* ,
GROUP_CONCAT( tags.tag ) AS tag_list
FROM userdata
JOIN entries
ON userdata.entry_id = entries.entry_id
JOIN tags AS required_tag
ON required_tag.entry_id = entries.entry_id
AND required_tag.tag = '$tag'
LEFT JOIN tags
ON tags.entry_id = entries.entry_id
WHERE userdata.user = '$user'
GROUP BY entries.entry_id
ORDER BY entries.date
DESC LIMIT 0 , 30

Make sure that running a seperate SELECT COUNT(*) isn't faster then
SQL_CALC_FOUND_ROWS though, I would be surprised at all.

> Question 2: I'm having trouble figuring out COUNT(), which is what I
> believe I need to get the count for entries.type (entries.type is
> either 'image', 'link', 'video', 'text'). This isn't nearly as
> important as question one, but it would be nice to know.

As I suspect an entry_id is a unique primary key (cotrrect me if I'm
wrong) you can do a COUNT(entries.type), which would always return 1,
unless type can be NULL, in which case is will be 0.

Take a step back from your problem, and think about wether or not doing
some seperate queries instead of trying to fit it all in one isn't a
better solution.
--
Rik Wasmus
....spamrun finished