Web page should show one product image for specific product category from PostgreSql database. This image should changed automatically to other image after every 25 seconds. Returned product may be random or in some sequence. Some product may be missing and some repeated but most of the products in criteria should returned. Total available image count may change slightly between sample retrieval
Currently code below is used which is executed after every 25 seconds. This requires two queries to database: one for count which may be slwo and second for single image retrieval. In both cases where clauses are duplicated, in real application where clause is very big and changing it requires changes in two places.
How to improve this so that single query returns sample ? Column types cannot changed, natural primary keys are used. Additional columns, triggers, indexes, sequences can added if this helps.
ASP.NET/Mono MVC3 , npgsql are used.
$count = select count(*)
from products
where prodtype=$sometype and productid in (select productid from images);
$random = next random integer between 0 .. $count-1;
-- $productsample is result: desired sample product
$productsample = select product
from products
where prodtype=$sometype and productid in (select productid from images)
offset $random
limit 1;
create table products ( productid char(20) primary key,
prodtype char(10) references producttype
);
create table images(
id serial primary key,
productid char(20) references products,
mainimage bool
);