How to convert a query with left joins with multiple "on conditions" to a view in postgres?

Question

Consider the query below(very small example for brevity)

select * from users u
left join orders o on (u.oid = o.id and o.state='CA') -- 'CA' aka state code will be passed dynamically
where u.id in (1,2,3)

How to convert this query to a view? In my java code currently the userIds and state is dynamic. Meaning for each request I am getting a different set of userIds and state and then I am forming the above query to get the results.

But for performance/other reasons, I want to convert this query to a materialized view or at the very least a normal view.

The main problem is that the state is being passed in the "on" condition. If this wasn't dynamic, then query would be like so

select * from users u
left join orders o on (u.oid = o.id) -- if state code filter wasn't present/required at all in business requirements
where u.id in (1,2,3)

And then this could've been easily converted to a view and I could've run the query as

select * from my_custom_view where user_id in (1,2,3)

But since, state is being passed dynamically inside the "on" condition, I am not sure how to convert this to a view(both for organization and later on for performance purposes through materialized view etc).

Answer 1

Essentially you want to query and filter a view (since it probably preprocesses data) and this view has an outer join.

If you were not using a view your "Plain Query" would look like:

select u.*, o.id as xid, o.state, o.price
from users u
left join orders o on u.oid = o.id and state = 'CA'
where u.id in (1, 2, 3);

Removing the extra predicate state = 'CA' the view is created as:

create view v as
select u.id as user_id, u.oid as order_id, o.state as state from users u
join orders o on (u.oid = o.id) -- removed LEFT JOIN
where u.id in (10)
union all -- added LEFT JOIN separately
select u.id as user_id, u.oid as order_id, null from users u
where u.id in (10)

To query this view and get the same result you need to account for the outer join. Instead of adding the predicate state = 'CA' to it you need to add the predicate state = 'CA' or state is null to it.

This time, the "Query Through a View" will take the form:

select * from v where state = 'CA' or state is null;

You can see that both options -- plain query and query through a view -- produce the same result according to the left join.

See running example at DB fiddle.