SELECT query include possible favorites table entry...

safra

Hi,

I m struggling getting the following to work. I have 3 tables:

users: ID, name, etc
comments: ID, user, comment, date
favorites: ID, user, favorite (where 'favorite' is the user id of the person that has been added as a favorite)

A user can do a request for all comments within a date range:

SELECT comments.ID as comment, users.name as name FROM comments, users WHERE users.ID=comments.user AND date<'...' AND date>'...'

But now I would like to get favorites info, is the user that sent the comment on the favorites list of the user that makes the request? In this case an entry exists in the favorites table.

I tried things like adding left outer join on comments.user=favorites.favorite to above query but that resulted in weird "Unkonwn column errors".

Anyone can hint me in the right direction?

Thanks!

traq

USER is a reserved word in MySQL. If you want to use it as a column name, you need to delimit it with backticks (these are NOT 'single quotes').

Dunno if that's your whole problem, but it's a start. Post the queries you've tried, and the complete error messages.

bradgrafelman

traq;11012141 wrote:
USER is a reserved word in MySQL.

No, it isn't.

traq

bradgrafelman;11012143 wrote:
No, it isn't.

whoops...

I was thinking of the CREATE|DROP|RENAME USER commands. Sorry for the confusion!

bradgrafelman

Easy mistake to make... however the last part of your post still applies:

traq;11012141 wrote:
Post the queries you've tried, and the complete error messages.

It's hard to tell exactly what you tried without seeing the raw SQL query... otherwise we're making assumptions (and we all know what happens when one assumes...).

safra

First I tried what I wrote, something like:

SELECT comments.ID as comment, users.name as name, favorites.favorite FROM comments, users LEFT OUTER JOIN on comments.user=favorites.favorite WHERE users.ID=comments.user AND date<'...' AND date>'...'

That resulted in an "Unkonwn column error" referring to, I belief, comments.user. Code ran well without the LEFT OUTER JOIN so comments.user should be fine by itself.

Afterwards I tried this:

SELECT t1.ID as comment, t2.name as name, t3.favorite FROM comments as t1 LEFT JOIN users as t2 ON t1.user=t2.ID LEFT OUTER JOIN favorites as t3 ON  t3.favorite=t2.ID WHERE t1.date<'...' AND t1.date>'...' AND t3.user='this user ID'

I remember using LEFT OUTER JOIN before in a situation like this. The problem is, this query will only find those comments for users who exist as a favorite. I just want to have the favorite info as extra info. It should find all comments but it should return, for example, null for
t3.favorite if that person is not added as a favorite.

bradgrafelman

safra;11012183 wrote:
First I tried what I wrote, something like:

SELECT comments.ID as comment, users.name as name, favorites.favorite FROM comments, users LEFT OUTER JOIN on comments.user=favorites.favorite WHERE users.ID=comments.user AND date<'...' AND date>'...'

Problem with that query is here:

LEFT OUTER JOIN on

Left outer join... what? You didn't tell MySQL which table is being joined.

johanafm

The join is not where it goes wrong.

safra;11012183 wrote:

FROM comments as t1
LEFT JOIN users as t2 ON t1.user=t2.ID
LEFT OUTER JOIN favorites as t3 ON  t3.favorite=t2.ID

OUTER and INNER joins specify which information to retain for rows having no matches in the other table (LEFT, RIGHT and FULL outer joins respectively). However, you should note that the difference between INNER JOIN and LEFT JOIN is exactly that of the first being an inner join while the second is an outer. INNER JOIN is shorthand notation for LEFT INNER JOIN, while LEFT JOIN is shorthand notation for LEFT OUTER JOIN. Thus you have not modified your query at all by adding OUTER to your left join. Also note that you should allready have the expected behaviour (include everything from the left hand side i.e. the comments table joined with users, wether or not there are matching rows in the favourites table, while not including favourites information if there is no comments info).

On the other than, you might as well switch the first join from LEFT JOIN to INNER JOIN to remove all comments for which there is no user. Granted, it's a case which should NEVER exist - After all, you have specified FOREIGN KEY (user) REFERENCES users ON DELETE CASCADE on the comments table havn't you?

safra;11012183 wrote:

WHERE t1.date<'...' AND t1.date>'...' AND t3.user='this user ID'

You are however specifying the the favourite has to match 'this user ID' (hint, when posting rather write that as @this_user_id, since that would signify a variable in SQL rather than a string literal which makes it more straightforward to understand), which you don't seem to be looking for. You seem to be looking for comments for @this_user_id, which would mean

t1.user = @this_user_id

Also, to make it clear what table is referred when using aliases, for your own sake do use meaningful names (or at least meaningful initials)

FROM comments AS c
-- (left) inner join instead of left (outer) join
INNER JOIN users AS u
-- left (outer) join instead of left outer join
LEFT JOIN favorites AS fav
-- which will now make it clear that you are matching user id against
-- the favourites table... which is clearly wrong
WHERE fav.id = @this_user_id

safra

@, I noticed that as well and fixed it, you probably saw the initial post meanwhile I was changing it!

@, To be honest LEFT RIGHT INNER OUTER JOIN etc, always confuses me... I tried changing LEFT to INNER etc. but it still doesn't work.

At the end you wrote "the favourite has to match 'this user ID' ". 'this user ID' refers to the logged in user who does the request, the favorite is column 'favorite' not column 'user'. This because we have to search the database to see if the user who submitted the comment is on the favorites list of this user. The end of your query looks like

WHERE fav.id = @this_user_id

Is your query the one that should work? fav.id is not the logged in user nor the favorite. but when I change it to fav.user=@this_user_id (the logged in user), no records are returned... when removing fav.user=@this_user_id all records are returned with null for the favorites columns because there are no comments from users on the favorites list of the logged in user, this for testing purposes!

johanafm

safra;11012295 wrote:
@, To be honest LEFT RIGHT INNER OUTER JOIN etc, always confuses me... I tried changing LEFT to INNER etc. but it still doesn't work.

because that should change nothing (due to overall design of the DB schema), but is semantically correct from a functionality viewpoint. What the change from LEFT JOIN users to INNER JOIN users does is it will remove any comment which has no matching user - Clearly there should be no such comments!

safra;11012295 wrote:
At the end you wrote "the favourite has to match 'this user ID' ". 'this user ID' refers to the logged in user who does the request

Yes, and I understand this. However, from a syntax point of view, this is NOT what it says, and that is why I suggested that when you post here, instead of using a string literal to show what you mean, express it using a variable.
SQL variables are referenced using @.

SET @varname = 'value';

SELECT @varname;

So if you instead of

AND t3.user='this user ID'

wrote

AND t3.user=@this_user_ID

everyone would instantly see that you're talking about a variable, i.e. a value which may/will change, rather than having to guess that it is.

safra;11012295 wrote:
, the favorite is column 'favorite' not column 'user'. This because we have to search the database to see if the user who submitted the comment is on the favorites list of this user. The end of your query looks like
WHERE fav.id = @this_user_id
Ah, now I see what you want to do here. No, this part was NOT following my lines of reasoning when it came to the user_id, it was to show what I believe is a proper way of using aliases. "fav" as an alias is far clearer than "t3". In other words, the above has the exact same functionality as your code, and it is broken. I believed what you wanted to do was find comments posted by the current user, and link in info about their favorites for these posts. Just to explain what I was talking about above.

The reason things won't work for non-friend comments, is that the above
- finds such a comment
- joins it to the user who posted it
- left joins it to the favourites table (where there is no such matching friend, or in other words: t3.favourite IS NULL)
- adds a constraint WHERE fav.favourite = @this_user_id (@this_user_id clearly isn't null for the current user)

Solution: change the where clause to match
AND (fav.favourite = @this_user_id OR fav.favourite IS NULL)
Don't forget the parentheses! Also, include fav.favourite in the selected fields. When it matches the current user, the person making the comments and the current user are friends. When it is null, they are not.

safra

Thanks a lot for helping!

The 'this user ID' was purely to be descriptive, $user can be anything...

The last line, I belief, will not get the desired data as the favorite column is still matched against $user which is the ID of the logged in user.

What I want is simply get all comments within 2 dates. If the person who submitted the comment is on the favorites list of the logged in user ($user) display a favorite icon.

AND (fav.user=$user OR fav.favourite IS NULL)

doesn't work either, no results...

johanafm

The entire query being....

safra

The queries I tried are exactly like in your post. I only changed @this_user_ID into $user the php var holding the id of the logged in user instead of the id of the favorite (this is unknown and what we have to search for, if it exists at all in the favorites table for the logged in user)

FROM comments as c
INNER JOIN users as u ON c.user=u.ID
LEFT JOIN favorites as fav ON  fav.favorite=u.ID
// $user is the logged in user id
WHERE fav.user=$user

And

FROM comments as c
INNER JOIN users as u ON c.user=u.ID
LEFT JOIN favorites as fav ON  fav.favorite=u.ID
// $user is the logged in user id
WHERE fav.user=$user OR fav.favourite IS NULL

johanafm

SELECT u.name AS comment_writer, c.text, fav.favourite
FROM comments as c
INNER JOIN users as u ON c.user=u.id
LEFT JOIN favourites as fav ON fav.user=u.id AND fav.favourite = $user

safra

$user and the comment writer are still swapped. But I belief the following works:

SELECT u.name AS comment_writer, c.text, fav.favourite
FROM comments as c
INNER JOIN users as u ON c.user=u.id
LEFT JOIN favourites as fav ON fav.user=$user AND fav.favourite = c.user

Many thanks!

safra

$user and the comment writer are still swapped. But I belief the following works:

SELECT u.name AS comment_writer, c.text, fav.favourite
FROM comments as c
INNER JOIN users as u ON c.user=u.id
LEFT JOIN favourites as fav ON fav.user=$user AND fav.favourite = c.user

Many thanks!