0

i have a challenge whose consist in filter a query not with a value that is not present in a table but a value that is retrieved by a function.

let's consider a table that contains all sales on database

id, description, category, price, col1 , ..... col n

i have function that retrieve me a table of similar sales from one (based on rules and business logic) . This function performs a query again on all records in the sales table and match validation in some fields.

similar_sales (sale_id integer) - > returns a integer[]

now i need to list all similar sales for each one present in sales table.

select s.id, similar_sales (s.id)
from sales s

but the similar_sales can be null and i am interested only return sales which contains at least one.

select id, similar
from (
    select s.id, similar_sales (s.id) as similar
    from sales s 
) q
where #similar > 1 (Pseudocode)
limit x

i can't do the limit in subquery because i don't know what sales have similar or not. I just wanted do a subquery for a set of small rows and not all entire table to get query performance gains (pagination strategy)

Lucho82
  • 150
  • 2
  • 10
  • Not sure what your question is, [how to count values in postgres array](https://stackoverflow.com/a/11189209/1048572)? – Bergi Dec 17 '21 at 23:30
  • Alternatively, make your function return `NULL` instead of an empty array (which it might already do if you use an aggregate), and just write `where similar is not null`. – Bergi Dec 17 '21 at 23:31

1 Answers1

0

you can try this :

select id, similar
from sales s
cross join lateral similar_sales (s.id) as similar
where not isempty(similar)
limit x
Edouard
  • 6,577
  • 1
  • 9
  • 20