Does `join` supports the `or` operator?

[64J0]

From this example I understood that it works like an and:

for a in as do
join b in bs on (a.id=5)
join c in cs on ((a.id, b.id) = (c.aid, c.bid))

Does it support the or too? For example, to translate this query:

select d.*
from d 

left join e 
on d.id = e.id

left join s 
on d.id = s.id

join g 
on e.group = g.group or s.group = g.group;

Originally posted by @64J0 in https://github.com/fsprojects/SQLProvider/issues/141#issuecomment-2117967478

I tested with this code, but it didn't work:

for d in ds do
join e in (!!) es on (d.id = e.id)
join s in (!!) ss on (d.id = s.id)
join g in gs on (e.group = g.group || s.group = g.group)

The error is:

Invalid join relation in 'join'. Expected 'expr <op> expr', where <op> is =, =?, ?= or ?=?.

[Thorium]

The error you pasted is FS3125, which is F# error, not SQLProvider error. As far as I know, the main limitation and issue is dotnet/fsharp QueryBuilder, not SQLProvider. I've been trying to get them extend the QueryBuilder in the past, but it seems not to be their target.

If the QueryBuilder would support it, the addition to SQLProvider side could be quite easy: The type LinkData would need to carry some kind of "what" operations (and or or, and equal, not-equal, greater-than, etc.) as currently it does AND equals only, like this:

  ~~  (String.Join(" AND ", (List.zip data.ForeignKey data.PrimaryKey) |> List.map(fun (foreignKey,primaryKey) ->
      sprintf "%s = %s"
          (fieldNotation (if data.RelDirection = RelationshipDirection.Parents then fromAlias else destAlias) foreignKey)
          (fieldNotation (if data.RelDirection = RelationshipDirection.Parents then destAlias else fromAlias) primaryKey)
      ))))

There are 2 workarounds here:

• Depending on your case, maybe you can do just something like this:

for d in ds do
join e in (!!) es on (d.id = e.id)
join s in (!!) ss on (d.id = s.id)
for g in gs do 
where (e.group = g.group || s.group = g.group)

• Or if your F# goes too complex, it's probably better to do a view and then just query that from SQLProvider like any other table.

[64J0]

Got it, thanks for the fast reply.

I'll move on with some of the workarounds then.