Provide join statement as is to .from_()

[antoni4040]

Hello. I have queries and I want to change their join order for a project, thus I only want to change the FROM part of the query, but I can't use the .from_(sql) with something that contains the JOIN keyword in it. I imagine this is by design, is there some way to do it without compilcated tree traversals? Thank you.

[barakalon]

Hi there.

I don't understand exactly what you're trying to do.

Can you provide more information? Maybe a "before" query and an ideal "after" query?

[antoni4040]

I want to pass to .from_() something like this:
On_Time_On_Time_Performance_2016_1 JOIN L_QUARTERS ON On_Time_On_Time_Performance_2016_1.Quarter=L_QUARTERS.Code

(experimenting with join ordering for a project)
This is generated as text from some other program, and the easiest solution is probably to mess with the final .sql() as a string, but it would be nice if it could be passed to .from_() directly.

[barakalon]

This is a little tricky, as sqlglot treats from and joins as different sub expressions.

Here's what I would do:

import sqlglot

# An existing sqlglot expression
expression = ...

# Whatever the "other program" gives you
reordered = "x JOIN y ON x.a = y.a JOIN z ON y.b = z.b"

# Parsing the reordered from...join clause into an intermediate sqlglot expression
intermediate = sqlglot.parse_one(f"SELECT * FROM {s}")

from_ = intermediate.args.get("from")
joins = intermediate.args.get("joins") or []

expression.set("from", from_)
expression.set("joins", joins)

[antoni4040]

Thank you for the answer, although I ended up messing with the final sql text.
Still, this library has reduced my code in half and runs better than other python parsers...