In this chapter, we will talk approximately the operators which build on relationships.
__eq__()
The above operator is a many-to-one “equals” comparison. The line of code for this operator is as proven below −
s = session.query(Customer).filter(Invoice.invno.__eq__(12))
The equal SQL question for the above line of code is −
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.invno = ?
__ne__()
This operator is a many-to-one “now not equals” evaluation. The line of code for this operator is as shown underneath −
s = session.query(Customer).filter(Invoice.custid.__ne__(2))
The equivalent SQL question for the above line of code is given below −
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers, invoices
WHERE invoices.custid != ?
contains()
This operator is used for one-to-many collections and given underneath is the code for carries() −
s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))
The equal SQL query for the above line of code is −
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE (invoices.invno LIKE '%' + ? || '%')
any()
any() operator is used for collections as proven underneath −
s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))
The equal SQL query for the above line of code is shown under −
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE EXISTS (
SELECT 1
FROM invoices
WHERE customers.id = invoices.custid
AND invoices.invno = ?)
has()
This operator is used for scalar references as follows −
s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))
The equal SQL question for the above line of code is −
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE EXISTS (
SELECT 1
FROM customers
WHERE customers.id = invoices.custid
AND customers.name = ?)