problem with mysql query

Question

i have a table called users this what the table look like

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) 

and finally i have a table called friends, this what the table look like

CREATE TABLE `friends` (
  `friendship_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id1` bigint(20) unsigned NOT NULL,
  `user_id2` bigint(20) unsigned NOT NULL,
  `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`friendship_id`),
  KEY `user_id1` (`user_id1`),
  KEY `user_id2` (`user_id2`),
  CONSTRAINT `friends_ibfk_1` FOREIGN KEY (`user_id1`) REFERENCES `users` (`id`),
  CONSTRAINT `friends_ibfk_2` FOREIGN KEY (`user_id2`) REFERENCES `users` (`id`)
)

so basically if userA is following userB , then a row will be added to the friends table, with the attribute user_id1 is userA and user_id2 is userB.

im trying to write a mysql query for a searchbox. the user will enter a string and the query will crawl a list of users that include that string but the people that the user is following need to be displayed first.

so if we have 3 users

• jack
• jason
• john

if the user Chris (who's following jason) enters in the searchbox the string 'ja', the query will crawl the list of users with the following order jason,jack. since jason is followed by chris.

from my understanding , i think it might a group by problem, i tried different queries but i couldnt get the needed results

do you guys have any idea ?

thanks a lot

Answer 1

You have to do a trick for sorting, so friendships get a 0 and non-friendships get a 1 in a temporary field and then we sort ascending for this field and as second we sort by username

SELECT x.username
FROM users x LEFT JOIN friends y ON x.id=y.user_id2 AND y.user_id1=$LOGGED_IN_USER
WHERE LOWER(x.username) LIKE 'ja%'
ORDER BY CASE WHEN y.user_id2 IS NULL THEN 1 ELSE 0 END,x.username

@thanks to scwagner for pointing me to extend JOIN-clause