MySQL - Like + JOIN

Question

I want to search for a user in my database, and for each, make a JOIN ON another table. Here are my tables:

USERS: id, firstname, lastname, [...] - Containing user's data

FRIENDS: fan_id, idol_id, [...] - Which user follows which user

Here is my search query, classic:

SELECT `id`, `username`, `firstname`, `lastname`, `lang`, `twitter`, `facebook`, `picture`, `regdate`
FROM `users`
WHERE `username` LIKE ?
OR `email` LIKE ?
OR `firstname` LIKE ?
OR `lastname` LIKE ?
OR `twitter` LIKE ?
OR `facebook` LIKE ?
ORDER BY `username`;

So now, I want to a get friends table, containing the relationship between the searched user and the user which is asking this.

Here is what I thought about:

SELECT `id`, `username`, `firstname`, `lastname`, `lang`, `twitter`, `facebook`, `picture`, `regdate`,
GROUP_CONCAT(
    CONCAT_WS(':', `fan_id`, `idol_id`) SEPARATOR ';'
) AS relations
FROM `friends`, `users`
WHERE (`username` LIKE ?
OR `email` LIKE ?
OR `firstname` LIKE ?
OR `lastname` LIKE ?
OR `twitter` LIKE ?
OR `facebook` LIKE ?)
AND (
(`fan_id` = 100 AND `idol_id` = `id`)
OR
(`fan_id` = `id` AND `idol_id` = 100)
)
ORDER BY `username`;

Actually, I want to get in relations if the searched user appears as fan_id AND the searching user as idol_id or the contrary. But when I search for a user with the first query, I have 2 rows, 2 users (ids = 80 & 125). With the second query, I have only 1 row (id = 80) but the relations shows 100:80;100:125 which says that I have a half of the query working...

For information, I also want to have a result if there isn't (in relations), so I tried IFNULL(..., 0) but nothing more.

Thank you for your help.

Answer 1

This is based off of a guess of what I think you are aiming for:

select users.id,users.firstname,
case 
when CONCAT_WS(';',t1.fans,t2.idols)='' then null
else CONCAT_WS(';',t1.fans,t2.idols)
end as relations
from users left join 
(
select CONCAT_WS(':',fan_id,idol_id) as fans,idol_id
from friends
where fan_id=80
) as t1
on users.id=t1.idol_id
left join
(
select CONCAT_WS(':',fan_id,idol_id) as idols,fan_id
from friends
where idol_id=80
) as t2
on users.id=t2.fan_id
where id=100

Sqlfiddle is the result for a user with id 100 who is searching for a user with id 80.

where fan_id=80 and where idol_id=80 needs to be set to the correct id for the user being searched for and where id=100 can be replaced with the wheres for finding the user doing the searching. Or you can remove all three and get a list of everyone's relations to eachother.

---EDIT---

Sqlfiddle

This query gives less detail but searches through friends only once and users only once; specifically it no longer holds the ids in the idol and fan columns, rather these columns are either 1 or null. 1 means that the user is that of the other user, so user 125 searches for user 100 and has fan - null | idol - 1 which means that they are not a fan of user 100 but they are an idol of user 100. The relations column as suggested in my previous comment is either 2 for both a fan and idol, 1 for just one of those, or 0 for neither.

select users.id,users.firstname,sum(t1.fan) as fan,sum(t1.idol) as idol,
case 
when t1.fan is null and t1.idol is null then null
else count(*) 
end as relations
from users
left join
(
select fan_id,idol_id,
case when fan_id=100 then 1 end as idol,
case when idol_id=100 then 1 end as fan
from friends
where fan_id=100 or idol_id=100
) as t1
on users.id=t1.fan_id or users.id=t1.idol_id
where firstname like '%user1%'
group by id

Four places where you have to change the id of the user being searched for, all the 100s. And the where clause at the bottom can be populated by whatever you want.

Answer 2

It's much easier to use the standard JOIN syntax than it is to use the obsolete omega-join model that you're using.

In a multiple-table query you should probably qualify your column names.

Start your join with the so-called lead table. In your case you want a row per user, so start the join with that.

Your long cascade of OR criteria in your WHERE will cause your query to perform very poorly. You may want to consider some sort of FULLTEXT (binary mode) search instead.

Backticks inhibit readability and aren't necessary except when a table or column has the same name as a reserved word.

You have a GROUP_CONCAT summarizing function, but no corresponding GROUP BY clause, in the query. You need both. (There are allowable linguistic shortcuts in which GROUP BY isn't necessary, but you should avoid those when you're debugging things.)

I am trying to figure out your join criteria, but I don't get it. I don't understand what's special about id = 100. But, is it possible that you want, in the second part of your join, to look for idol_id = 100 rather than fan_id = 100?

             (f.fan_id = u.id AND  f.idol_id = 100)

What you have doesn't seem logical to this outsider to your project.

I suggest you try this:

SELECT u.id, u.username, 
       u.firstname, u.lastname, 
       u.lang, u.twitter, u.facebook, 
       u.picture, u.regdate,
       GROUP_CONCAT(CONCAT_WS(':', f.fan_id, f.idol_id) SEPARATOR ';') AS relations
  FROM users u
  JOIN friends f ON  (                  
             (f.fan_id = 100 AND f.idol_id = u.id)
                OR
             (f.fan_id = u.id AND  f.idol_id = 100)
            )
 WHERE u.username LIKE ?
    OR u.email LIKE ?
    OR u.firstname LIKE ?
    OR u.lastname LIKE ?
    OR u.twitter LIKE ?
    OR u.facebook LIKE ?
 GROUP BY u.id, u.username, 
       u.firstname, u.lastname, 
       u.lang, u.twitter, u.facebook, 
       u.picture, u.regdate
 ORDER BY u.username