cross join in sqlalchemy 1.3

I can't understand how to connect two tables with cross join. Earler, at sqlalchemy version 1.1.4 I do it this way:

A = Table1.sa 
B = Table2.sa
my_query = A.query().join(B, literal(True))

But after updating to version 1.3 it's raise exception:

sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Try adding an explicit ON clause to help resolve the ambiguity.

I'm understand that it's mean that I have to do something like that:

my_query = A.query().join(B, A.fireign_key_id = B.id)

But table A haven't foreign key to table B.

How to connect two tables with cross join and without foreign key? Thanks.

Cross join doesn't use join conditions so there can't be any ON between the Table1 and Table2. In Cross join each row from T1 joins with all rows from T2. Are you sure about using Cross join?

Is explicit CROSS JOIN possible with SQLAlchemy?, SQLAlchemy doesn't have explicit way to specify CROSS JOIN. IMHO all major relation databases do CROSS JOIN when you has tables in FROM clause, but no join criteria between them. But after updating to version 1.3 it's raise exception: sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Try adding an explicit ON clause to help resolve the ambiguity.

Thank you for all answers. Exception was raise, because the query couldn't determine which table is left and into query(...) use the fields of table B. After I explicitly indicated the left table, this exception disappeared.

my_query = A.query(...).join(B, A.id != None)

Configuring how Relationship Joins — SQLAlchemy 1.3 , Handling Multiple Join Paths�. One of the most common situations to deal with is when there are more than one foreign key path between two� The custom criteria we use in a relationship.primaryjoin is generally only significant when SQLAlchemy is rendering SQL in order to load or represent this relationship. . That is, it’s used in the SQL statement that’s emitted in order to perform a per-attribute lazy load, or when a join is constructed at query time, such as via Query.join(), or via the eager “joined” or “subquery

The simplest way to do a cross join is to select from both tables without providing any filter to perform an ANSI join:

my_query = session.query(A, B)

This'll produce a cartesian product (cross join) of your A and B entities, no workarounds required.

SQL Expression Language Tutorial — SQLAlchemy 1.3 , Those who are familiar with SQL joins know that this is a Cartesian product; each row from the users table is produced against each row from� The Query.join() method currently supports several usage patterns and arguments that are considered to be legacy as of SQLAlchemy 1.3. A deprecation path will follow in the 1.4 series for the following features:

Selectables, Tables, FROM objects — SQLAlchemy 1.3 , join() or FromClause.outerjoin() methods on the resulting Join object. function sqlalchemy.sql.expression. select� Source code for examples.join_conditions.threeway. """Illustrate a "three way join" - where a primary table joins to a remote table via an association table, but then the primary table also needs to refer to some columns in the remote table directly.

What's New in SQLAlchemy 0.9? — SQLAlchemy 1.3 Documentation, now is 2.6, which features a wide degree of cross-compatibility with Python 3. If we wanted to reverse the order of the left and right elements of the JOIN, the� In order to provide cross- compatibility under Python 2, the SQLAlchemy cx_Oracle dialect will add Unicode-conversion to string data under Python 2 as well. Historically, this made use of converters that were supplied by cx_Oracle but were found to be non-performant; SQLAlchemy’s own converters are used for the string to Unicode conversion

What's New in SQLAlchemy 1.4? — SQLAlchemy 1.4 Documentation, This document describes changes between SQLAlchemy version 1.3 and the construction of the Query itself, when methods filter() or join() would be called, etc ., object, such that these two objects now have cross-compatible internal state. SQL Cross Join. When each row of first table is combined with each row from the second table, known as Cartesian join or cross join. In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table. We can specify a CROSS JOIN in two ways: Using the JOIN syntax.

Comments
  • Please add definitions for models A and B, or Table1.sa etc.
  • JOIN foo ON true is effectively a cross join, though not written explicitly as one.
  • The cross join does not itself apply any predicate to filter rows from the joined table. The results of a cross join can be filtered by using a WHERE clause which may then produce the equivalent of an inner join. en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join Ok, we also can use ON instead of WHERE and result will be the same, but for what?
  • Please note the word "effectively". The result of the aforementioned inner join is exactly equal to the relation produced by a cross join, and the result can be further selected from using a WHERE clause just as well. On the other hand the simple approach would be to just list the FROM-items in pre SQL-92 style.