selecting 1 image from each product if there were n different products, if not, select more than 1 image from each products til n is reached

Question

In the project's design schema, one product may have many images.

Now I want to select n images from products with the situation:

• If n products were defined, select 1 image from each.
• Else select more images from each product limit n

Do we also need a PHP side action to reach the goal?

The schema is as expected:

product (id, title, ...)
image (id, product_id, filename, ...)

I cannot even think of a query like this, that's why I haven't unfortunately tried anything.

The query should look like this:

SELECT * FROM image ..with..those..hard..conditions.. LIMIT n

Answer 1

If I understand it well -- you need n images. If possible from different products. Otherwise, several images from the same product is acceptable as fallback.

From now, the only solution I could think of, is to build a temporary table, with numbered rows such as there will be one "image" of each product at the "top" -- and filed with the rest of the images.

Once that table is build, your query is just a SELECT ... LIMIT n.

This will perform horribly -- and if you choose that solution of something inspired -- you should consolidate the image table off-line or on schedule basis.

see http://sqlfiddle.com/#!2/81274/2

--
-- test values
--
create table P (id int, title char(20));
insert into P values
  (1, "product 1"),
  (2, "product 2"),
  (3, "product 3");

create table I (pid int, filename char(40));
insert into I values
   (1, "image p1-1"),
   (1, "image p1-2"),
   (3, "image p3-1"),
   (3, "image p3-2"),
   (3, "image p3-3");

--
-- "ordered" image table
--
create table T(n int primary key auto_increment not null,
                         filename char(20));


--
-- consolidate images (once in a while)
--
delete from T;
insert into T(filename)
    select filename from I group by pid;
insert into T(filename)
    select filename from I order by rand();

--
-- do the actual query
--
select * from T limit n;

EDIT Here is a completely different idea. Not using a consolidation table/view -- so this might be seen as better:

http://sqlfiddle.com/#!2/57ea9/4

select distinct(filename) from
    (select 1 as p, filename from I group by pid
     union (select 2 as p, filename from I order by rand() limit 3)) as T
order by p limit 3

The key point here is I don't have to really "number" the rows. Only to keep track of which rows are coming from the first SELECT. That is the purpose of p. I set both LIMIT clause to the same value for simplicity. I don't think you have to "optimize" that part since the benefit would be very small -- and ORDER BY RAND() is so terrible that you don't have to think about "performances" here ;)

Please note I don't have fully tested this solution. Let me know if there is some corner cases (well.. any case) that don't work.

Answer 2

http://sqlfiddle.com/#!2/32198/2/0

create view T as select (select filename from I where pid = id order by filename limit 1) as singleImage from P having singleImage is not null;

select * from (
  select singleImage from T
  union all (select filename from I where filename not in
    (select singleImage from T) order by rand() limit 5)
  ) as MoreThanN limit 5;

If your N is rather small, you may benefit from my technique for selecting random rows from large tables: although it is intended for selecting a single row, it could be adapted to select a few random rows relatively easily.

Here's the SQL with Sylvain Leroux's examples:

-- Test values
create table P (id int, title char(20));
insert into P values
  (1, "product 1"),
  (2, "product 2"),
  (3, "product 3");

create table I (pid int, filename char(40));
insert into I values
   (1, "image p1-1"),
   (1, "image p1-2"),
   (3, "image p3-1"),
   (3, "image p3-2"),
   (3, "image p3-3"),
   (3, "image p3-4"),
   (3, "image p3-5");

-- View to avoid repeating the query
create view T as select (select filename from I where pid = id order by filename limit 1) as singleImage from P having singleImage is not null;

-- Query
select * from (
  select singleImage from T
  union all (select filename from I where filename not in
    (select singleImage from T) order by rand() limit 5)
  ) as MoreThanN limit 5;