Join syntax in request body.

[kdhrubo]

Here is the set of tables to help design the syntax for no FK and implicit joins.

[kdhrubo]

@thadguidry @musibs @rajivmca2004 @souravroy @susanta-mukhopadhyay - pls feel free to share your feedback.

[kdhrubo]

Single Table Join 1

select 
          om.order_id as orderId, 
         om.order_date as orderDate, 
         od.line_number, 
        od.description, 
        od.quantity
 from 
        OrderMaster om 
join OrderDetail od on om.order_id = od.order_id

POST

/OrderMaster?fields=order_id:orderId,order_date:orderDate

[

{
    "table" : "OrderDetail",
    "filter": "order_id=eq=order_id" 
}

]

filter: specified as RSQL
type : not specified use default 'INNER'

[kdhrubo]

Join syntax implemented hence closing this discussion.

[quachtd]

Hi there, we should support custom conditions in the "ON" block. Such as the sample below, it's valuable for situations of left join:
select d.*, s.sales_person from dealers d
left join said_salesperson s
on d.said = s.said
and s.sales_person = 'I will get return value with or without this value match'
where d.said = 'myid1'

Payload:
[
{
"table": "said_salesperson",
"fields": [
"sales_person"
],
"type": "LEFT",
"on": [
"said==said",
"sales_person=='my optional value not work here'"
]
}
]

[thadguidry]

@quachtd on and withTable are 2 attributes of many already supported that you can add to the request body. See our docs: https://db2rest.com/docs/join/how-to-join-multiple-tables

[kdhrubo]

The string is not supported in ON. We can potentially extend it. Will check

…

On Thu, Jan 23, 2025, 8:06 PM Thad Guidry ***@***.***> wrote: @quachtd <https://github.com/quachtd> on and withTable are 2 attributes of many already supported that you can add to the request body. See our docs: https://db2rest.com/docs/join/how-to-join-multiple-tables — Reply to this email directly, view it on GitHub <#262 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAISPRGW747AEUFLAPVD3CD2MGN3JAVCNFSM6AAAAABVYBTP36VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCOJTG4YTSNQ> . You are receiving this because you modified the open/close state.Message ID: ***@***.***>

[thadguidry]

Confused by your reply, it's an Array he presented as the value of on? So what part do you mean by String?

[
"said==said",
"sales_person=='my optional value not work here'"
]

[quachtd]

The second one - "sales_person" I want to it to be searched with a literal such as "sales_person"=='my name1' or "my name2". Yahoo Mail: Search, Organize, Conquer On Thu, Jan 23, 2025 at 6:21 PM, Thad ***@***.***> wrote: Confused by your reply, it's an Array he presented as the value of on? So what part do you mean by String? [ "said==said", "sales_person=='my optional value not work here'" ] — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>