SQLAlchemy exists() used with first() ?

[Justvuur]

Hi there,

When creating another column property in a model that makes use of the exists(), I noticed that the exists does a "select *".

For example, the form exists below:

class Contact(ResourceMixin, db.Model):  
 __tablename__ = 'contacts'

form_contacts = db.relationship(FormContact, backref='contact', passive_deletes=True)

form_exists = column_property(
        exists().where(and_( FormContact .form_contact_id == id,
                            FormContact.partnership_id == partnership_id
                            )).label('form_contact_exist'), deferred=True
    )

prints out to be something like:

exists(select * from form_contacts where form_contacts.form_contact_id == id and  form_contacts. partnership_id == partnership_id)

Does the exists "stop" the query once one row is returned or does it execute the entire select all query?

If the latter, is there a way to limit the select all to one row?

Regards,

Justin

[Jonathan Vanasco]

I think you misunderstand `exists()` in SQLAlchemy and SQL.  `exists()` is a convenience function to create a SQL `EXISTS` clause, which is an operator used for filtering subqueries.

The 'from_exists' is just a subquery.  It is supposed to be used within a query which would then limit the query, not executed itself.

See

   https://docs.sqlalchemy.org/en/14/core/selectable.html?#sqlalchemy.sql.expression.Exists 

   https://docs.sqlalchemy.org/en/14/core/selectable.html?#sqlalchemy.sql.expression.exists

   https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.exists

   https://www.w3schools.com/sql/sql_exists.asp