how to get the top selling products and store it in solr, and retrive and show in category page

[former_member670738]

for retriving the data from db i used a cron job to retrive the data and insert the data into a new topselling product table. this table contains only onne column wich holds the collection of products. and my proble start form herare i write a query to retrive the top selling products from db

crating table to store topselling products data:

<collectiontypes><br>   <collectiontype code="topsellingproductscodes" elementtype="Product" autocreate="true" type="set"/><br></collectiontypes>

<itemtype code="TopSoldProducts">
   <deployment table="TopSoldProducts" typecode="23677"/>
   <attributes>
      <attribute qualifier="products" type="topsellingproductscodes">
         <persistence type="property" />
      </attribute>
   </attributes>
</itemtype>

1.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}>'2017-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.

it only returning the product pk's like this(,'ApparelStyleVariantProductModel (8796267708417@12)','ApparelStyleVariantProductModel (8796267282433@14)','ApparelProductModel (8796267020289@16)',).. but i want all product information so i can easily store the data into my newly created table

2.how to store the data into solr (i don't know how to index data into solr).

3. how to retrive the indexed data from solr using controller and showing ito the existing categgory page.

is there any wrong in my approach please correct me.

please help me out Experts.

thank you