I have two tables:
user_favourites -> id, user_id, product_id
product -> id, title, bought
I need to display 9 results -> user favourites plus other products if user has less than 9 favourites.
So on my page there should be 9 products displayed. If user has selected 9 favourite product then I will display those 9 favourites, if he has selected less than 9 ( lest say 5 ), then I have to display 5 of his favourites plus 4 highest rated products from the system.
To get user favourites I have this query :
select product_id from user_favourites where user_id = $userId
To get highest rated products I have this query:
select id, title, count(bought) from product group by id limit 9
So since I want to display first favourite products + most popular ones if user has not selected 9, can I somehow merge these two queries into one to get the desired result ? Please not one problem here, I need to remove duplicates. If product with id of 999 is chosen by user but he is also among the most popular ones, I need to display it only once. Also I need to get max of 9 results.
What is the most elegant way of doing this using php and mysql ?
to expand slightly on dirluca's fine work
create table product
(
id int not null auto_increment primary key, -- as per op question and assumption
title varchar(255) not null,
bought int not null -- bought count assumption, denormalized but who cares for now
);
create table user_favourites
(
id int not null auto_increment primary key, -- as per op question and assumption
user_id int not null,
product_id int not null,
unique index (user_id,product_id)
-- FK RI left for developer
);
insert into product (title,bought) values ('He Bought 666',10),('hgdh',9),('dfhghd',800),('66dfhdf6',2),('He Bought this popular thing',900),('dfgh666',11);
insert into product (title,bought) values ('Rolling Stones',20),('hgdh',29),('4dfhghd',100),('366dfhdf6',2),('3dfghdgh666',0),('The Smiths',16);
insert into product (title,bought) values ('pork',123),('and',11),('beans',16),('tea',2),('fish',-9999),('kittens',13);
insert into user_favourites (user_id,product_id) values (1,1),(1,5);
select P.id, P.title, P.bought,
( CASE
WHEN uf.user_id IS NULL THEN 0 ELSE -1 END
) AS ordering
from product as P
left join user_favourites as UF on(P.id=UF.product_id)
where UF.user_id=1 OR UF.user_id IS NULL
order by ordering,bought desc
limit 9;
-- ignore the ordering column, naturally, when plopping in gui
id title bought ordering
5 He Bought this popular thing 900 -1
1 He Bought 666 10 -1
3 dfhghd 800 0
13 pork 123 0
9 4dfhghd 100 0
8 hgdh 29 0
7 Rolling Stones 20 0
12 The Smiths 16 0
15 beans 16 0