No ORM, just me.
Was somewhat similar to something I had seen done at an old job, but they used SQL Server and that type of query worked fine there.
There were a couple business cases that had to be satisfied, which is why I went the way I did:
The first was "allow products to be grouped together, and those groups be placed in a hierarchy. All of the products in child groupings are valid for the parent of the grouping. Products should be able to be added and removed from this group at any point."
The next was "allow a user to add a product, or a group of products to a contract and set pricing information, product is the most definitive and overrides any groupings the product may be in, and the lowest level of the grouping hierarchy should be used if the product is not directly on the contract."
The last was "adding and removing products from a group should immediately take effect to make those products valid or invalid on any contracts that grouping is a part of."
Now I am not going to say I love this design, I actually am not a fan of it at all. I just couldn't think of any other design pattern that would meet those business requirements. I was hoping to create a view to make working with the final result the rules specified above easy when you want to know what pricing is valid for a specific product on a contract.
So that is the "why" at least.
On Thu, Dec 17, 2015 at 12:00 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Adam Brusselback <adambrusselback@xxxxxxxxx> writes:
> The view I am having trouble with is able to push down it's where clause
> when the id's are directly specified like so:
> SELECT *
> FROM contract_product cp
> WHERE cp.contract_id = '16d6df05-d8a0-4ec9-ae39-f4d8e13da597'
> AND cp.product_id = '00c117d7-6451-4842-b17b-baa44baa375f';
> But the where clause or join conditions are not pushed down in these cases
> (which is how I need to use the view):
> SELECT *
> FROM contract_product cp
> WHERE EXISTS (
> SELECT 1
> WHERE cp.contract_id = '16d6df05-d8a0-4ec9-ae39-f4d8e13da597'
> AND cp.product_id = '00c117d7-6451-4842-b17b-baa44baa375f'
> );
This plea for help would be more convincing if you could explain *why*
you needed to do that. As is, it sure looks like "Doctor, it hurts when
I do this". What about that construction isn't just silly?
(And if you say "it's produced by an ORM I have no control over", I'm
going to say "your ORM was evidently written by blithering idiots, and
you should not have any faith in it".)
Having said that, the reason nothing good happens is that
convert_EXISTS_sublink_to_join() punts on subqueries that have an empty
FROM clause, as well as some other corner cases that I did not care to
analyze carefully at the time. Just looking at this example, it seems
like if the SELECT list is trivial then we could simply replace the EXISTS
clause in toto with the contents of the lower WHERE clause, thereby
undoing the silliness of the query author. I don't think this could be
handled directly in convert_EXISTS_sublink_to_join(), because it's defined
to return a JoinExpr which would not apply in such a case. But possibly
it could be dealt with in make_subplan() without too much overhead. I'm
not feeling motivated to work on this myself, absent a more convincing
explanation of why we should expend any effort to support this query
pattern. But if anyone else is, have at it.
regards, tom lane
