Join Clause

[aderosso]

Is there a way to overwrite the join clause for a SQL data source? I'm looking for something similar to how tableClause, whereClause, etc works.

[Isomorphic]

tableClause is where we put the joins.

[aderosso]

I tried that, but adding an incluleFrom field adds additional joins.

[Isomorphic]

Could you clarify what you're trying to do? Are you trying to get rid of existing joins, add additional joins, something else?

Are you using DataSource.useAnsiJoins? This effects which clause is used for joins. See also operationBinding.includeAnsiJoinsInTableClause.

[aderosso]

So I have 2 tables, A and B. Table A is the master set of data and B may or may not have a related record. I need to add a condition to the join. The ds.xml is based off of table B since those are the values it would be editing, Here's the query that works:

SELECT ... FROM A
LEFT JOIN B ON A.id = B.id AND B.company_id = ?

I got it working by using a customSQL, but then I lose paging which isn't ideal. If I could just override the table/join clause it would be great. Which I'm able to do as long as I don't includeFrom table A as that adds duplicated joins.

[Blama]

Hi aderosso,

if there is no built-in way (don't think so) you can try this:
1) Analyze your query
2) No includeFrom fields in a.ds.xml, but instead:

<field name="b_extradata" customSelectExpression="SELECT extradata FROM b WHERE a.id = b.id AND b.company_id = $criteria.fakeField" />
<field name="fakeField" customSelectExpression="null" />

Then run this query and see if your DB optimizes the correlated subselect away into a outer join. If the plans are identical, you should be able to pull this of somehow with no performance penalty.

Best regards
Blama

[aderosso]

Thanks Blama, I was able to get it working with customSQL. I was just wondering though if it's intended that the <tableClause> doesn't suppress other system generated join statements? Or would be nice to have a <joinClause> to be able to add.

[Blama]

Hi aderosso,

yes, but customSQL lost you your pagination.
These two queries return the same for me:

--EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select p.id                                                                                                as product_id
     , p.name                                                                                              as product_name
     , (select pc.name
        from t_productcategory pc
        where pc.id = p.productcategory_id
          and p.name like '%S%')                                                                           as productcategory_name
from t_product p;

--EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
    select p.id    as product_id
     , p.name  as product_name
     , pc.name as productcategory_name
from t_product p
         left outer join t_productcategory pc on pc.id = p.productcategory_id and p.name like '%S%';

And they should both work with pagination, IMHO.

Also, there is already OperationBinding.ansiJoinClause. I did not use it so far, though.

Best regards
Blama

[aderosso]

Ahhh, that's what I was looking for. Thank you!