How to get products from order, order entries, and orders status from query

Question

I prepared a flexible search query. Here, I developed a condition like:

1. Order status is completed in orders

2. And order entries which are present in order

3. Getting products which are in orderentries

For this I wrote a query

select {p.pk} from {
  order as o 
  join OrderStatus as os on {os.pk}={o.status}
  join orderentry as oe on{oe.order}={o.pk}
  join product as p on {oe.product}={p.pk}
}
where {os.code}='COMPLETED' 
AND {o.date}>'2020-08-16 00:00:00.000' AND{o.date}<'2020-09-30 00:00:00.000' 
group by{p.pk} order by count({oe.pk}) desc limit 10

here in this query what I want is I want to get all product information like

select * from Product}

How to modify this query get all products?

Answer 1

You can do this using a subselect. The first query you posted above will be the subselect. You simply need to add another select to fetch the product information for all the PKs returned in the subselect.

select * from {Product as prod} where {prod.pk} in 
({{ 
  select 
    top 10 {p.pk} 
  from 
    {
      Order as o join 
      OrderStatus as os on {os.pk} = {o.status} join 
      OrderEntry as oe on {oe.order} = {o.pk} join 
      Product as p on {oe.product} = {p.pk}
    }
  where 
    {os.code} = 'COMPLETED' and 
    {o.date} > '2020-08-16 00:00:00.000' and 
    {o.date} < '2020-09-30 00:00:00.000'
  group by {p.pk} 
  order by count({oe.pk}) desc
}})