Predicate short-coming

[aclark]

I'm trying to execute a fetch using a predicate that specifies conditions on joined tables fields.

This is parsing down to this SQL:

SELECT "order_id", "notes", "order_date", "po_number", "received", "supplier_id" FROM "public"."orders"
WHERE (("po_number") ~* ($1)) OR (("supplier"."supplier_name") ~* ($2))

Of course, I get an error when this gets to the database since the "supplier" table wasn't listed in the FROM clause. I've been experiencing some issues with relationship names, but that's for another bug report.

[tuukka.norri@…]

(In [188]) Fixed bugs in FROM clause creation for simple JOINs using a WHERE clause (references #65) - Compound predicates should now get handled correctly. - Column references in SELECT queries will now have schema and table names to prevent ambiguities. - Also wrote a test.

[tsnorri]

Predicates should now get examined for constant value NSExpressions which have BXPropertyDescriptions as values. Corresponding entities should then get added to FROM clause. A view is probably needed for anything more complicated but let me know if this works for you.

[aclark]

Unfortunately, no. The bug still occurs with these changes. My predicate is built up from a UI design where user-selected fields and operators can be chosen and values then can be entered for each field.

For instance, a predicate can look like this:

(NOT po_number MATCHES[c] "test") OR supplier.supplier_name MATCHES[c] "ferg"

[tsnorri]

Replying to aclark:

If you didn't, try to create the predicate somewhat in this manner:

BXEntityDescription* order = [context entityForTable: @"order" error: &error];
BXEntityDescription* supplier = [context entityForTable: @"supplier" error: &error];
BXPropertyDescription* supplierName = [[supplier attributesByName] objectForKey: @"supplier_name"];
BXPropertyDescription* poNumber = [[order attributesByName] objectForKey: @"po_number"];
        
NSPredicate* predicate = [NSPredicate predicateWithFormat: 
        @"(NOT %@ MATCHES[c] \"test\") OR %@ MATCHES[c] \"ferg\"", poNumber, supplierName];

The poNumber needn't actually be a property since the fetch target is the same entity. The query gets more verbose this way, though. I should probably write some documentation on joins like this.

[aclark]

Ok, tried what you suggested and there's still a problem with this.

The query that gets passed to PostgreSQL isn't what I'm looking for:

LOG:  execute <unnamed>: SELECT "public"."orders"."order_id", "public"."orders"."notes", "public"."orders"."order_date", "public"."orders"."po_number", "public"."orders"."received", "public"."orders"."supplier_id" FROM "public"."supplier", "public"."orders" WHERE ((NOT (("public"."orders"."po_number") ~* ($1)))) OR (("public"."supplier"."supplier_name") ~* ($2))
DETAIL:  parameters: $1 = 'test', $2 = 'ferg'

Notice there's no join parameters there. Oddly the query that's passed to check on locks though looks like this:

LOG:  execute <unnamed>: SELECT l.* FROM baseten.lock_public_orders l , "public"."supplier" NATURAL INNER JOIN "public"."orders" WHERE (((NOT (("public"."orders"."po_number") ~* ($1)))) OR (("public"."supplier"."supplier_name") ~* ($2))) AND baseten_lock_cleared = false
DETAIL:  parameters: $1 = 'test', $2 = 'ferg'

This is closer and would work for me in this case but ideally I'd like to see my queries be passed where the foreign keys are passed in automatically.

LOG:  statement: SELECT * from orders join supplier on (orders.supplier_id = supplier.supplier_id) where po_number !~* 'test' or supplier.supplier_name ~* 'ferg';

Is this a priority for you? If it's not I'll look into adding this functionality myself but I don't want to have duplication of effort.

[markonen]

Please work on it if you can. We have plenty of other stuff to work on with BaseTen :)

[tsnorri]

Replying to aclark:

Notice there's no join parameters there.

Right, I should have thought of that. Try this:

    BXPropertyDescription* supplierName = [[supplier attributesByName] objectForKey: @"supplier_name"];
    BXPropertyDescription* supplierId = [[supplier attributesByName] objectForKey: @"supplier_id"];
    BXPropertyDescription* poNumber = [[order attributesByName] objectForKey: @"po_number"];
    BXPropertyDescription* orderSupplierId = [[order attributesByName] objectForKey: @"supplier_id"];
        
    NSPredicate* predicate = [NSPredicate predicateWithFormat: 
        @"%@ == %@ AND ((NOT %@ MATCHES[c] \"test\") OR %@ MATCHES[c] \"ferg\")", 
                supplierId, orderSupplierId, poNumber, supplierName];

The query should be something like this:

SELECT 
        "public"."order"."id", 
        "public"."order"."po_number", 
        "public"."order"."supplier_id" 
FROM 
        "public"."supplier", 
        "public"."order" 
WHERE 
        (("public"."supplier"."supplier_id") = ("public"."order"."supplier_id")) AND 
        (((NOT (("public"."order"."po_number") ~* ($1)))) OR (("public"."supplier"."supplier_name") ~* ($2)))

Oddly the query that's passed to check on locks though looks like this:

It gets constructed in a not-so-general manner, hence the INNER JOIN clause.

Is this a priority for you? If it's not I'll look into adding this functionality myself but I don't want to have duplication of effort.

Earlier I had thought that it would be too difficult to make queries with JOIN clauses but it would be really nice to have such a feature.

[aclark]

I think it's an important feature. It certainly would make predicates more powerful and it's definitely something I'm going to work on over the next couple of days to get working.