sql - Building dymanic query with many laterail joins -

i building dynamic query based on user-submitted conditions on user-specified fields.

i have 2 tables

visitors(id, name, email...) 

and

trackings(id, visitor_id, field, string_value, integer_value, boolean_value, date_value) 

the conditions comes in form of array of sql fragments built earlier. default filters attributes hardcoded on visitors table, , custom filters, values user-submitted , stored in eav schema (trackings)

example:

default: {"name ilike 'jack'", "(last_seen < (current_date - (7 || ' days')::interval))"}  custom: {"field = 'number_of_orders' > 10", "is_pro_user = true"} 

a visitor can have many trackings, each 1 recording custom, user-submitted data field visitor. each visitor has default data lies on table itself, such email, name or last_seen etc.

now, users should ask queries such as:

• give me visitors no custom field named number_of_orders has been recored (is unknown)
• give me visitors default attribute name set jack , custom attribute total_purchase_value greater 1000

my attempt @ solving using stored procedure dynamically concatenated series of conditions using , (for default data on visitor table), , or statements (for custom data in trackings table) inside where-caluse

create or replace function find_matching_visitors(app_id text, default_filters text[], custom_filters text[]) returns table (   id varchar ) $body$ declare     default_filterstring text;     custom_filterstring text;     default_filter_length integer;     custom_filter_length integer;     sql varchar; begin     default_filter_length := coalesce(array_length(default_filters, 1), 0);     custom_filter_length := coalesce(array_length(custom_filters, 1), 0);      default_filterstring := array_to_string(default_filters, ' , ');     custom_filterstring := array_to_string(custom_filters, ' or ');      if custom_filterstring = '' or custom_filterstring null         custom_filterstring := '1=1';     end if;      if default_filterstring = '' or default_filterstring null         default_filterstring := '1=1';     end if;      sql := format('                 select v.id visitors v                 left join trackings t on v.id = t.visitor_id                 v.app_id = app_id , (%s) , (%s)                 group v.id                 having case when %s > 0 count(v.id) = %s else true end             ', custom_filterstring, default_filterstring, custom_filter_length, custom_filter_length);     return query execute sql;  end; $body$ language 'plpgsql'; 

this works great, afaik there not way express unknown filter custom attribute. in require using left outer join, or not exists subquery access outer scope.

i looking @ alternative ways accomplish same above, support kind of query. thinking below, using series of lateral joins each condition, seems won't perform there more 1-2 conditions/joins.

select v.id, v.name visitors v inner join lateral ( <-- custom fields     select * trackings t     field = 'admin' ) t1 on t1.visitor_id = v.id inner join lateral (     select * trackings t     field = 'users_created'  ) t2 on t2.visitor_id = v.id inner join lateral (     select * trackings t     field = 'teams_created' , integer_value > 0 ) t3 on t3.visitor_id = v.id v.app_id = 'asnyw1-rgcl0i' , (v.type = 'lead' or v.type = 'user') , name ilike 'mads' , email not null // <-- default fields 

any suggestions?