How to merge two sql queries into one with variable limit

Question

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 ?

I have tried to first display results from favourites query, then I would take id's returned from there and use them when retrieving results from second query in WHERE NOT IN, but I think that is not optimal, so I am searching for one query to do what I need, if possible... — black-room-boy
– black-room-boy, Commented Jun 9, 2015 at 13:58
look at dev.mysql.com/doc/refman/5.0/en/union.html for joining two queries together. And also it looks like your query for the highest rating misses something - it will return not the highest rating, but just first 9 rows encountered... — Olha
– Olha, Commented Jun 9, 2015 at 14:18

Drew · Accepted Answer · 2015-06-09 14:57:04Z

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

dirluca · Accepted Answer · 2015-06-09 14:13:22Z

1

I'd go for a join:

select P.id, P.title, P.bought 
from product as P
left join user_favourites as UF on(P.id=UF.product_id)
where UF.user_id=$user_id OR  UF.user_id IS NULL
order by user_id DESC
limit 9;;;

This assuming that in product table you have 1 row per product and bought is an integer, not 1 row per buyer as your group by seem to suggest

Here's a fiddle

answered Jun 9, 2015 at 14:13

dirluca

4233 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to merge two sql queries into one with variable limit

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related