python - Sql query on a computed field

Question

I have a table where I store the information of businesses, defined in flask as

class ImportData(db.Model):
    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True, nullable=False, index=True)
    business_name = db.Column(db.String(), index=True)
    no_of_rating = db.Column(db.Float, index=True)
    no_of_reviews = db.Column(db.Float, index=True)
    phone = db.Column(db.String(), index=True)
    state = db.Column(db.String())
    city = db.Column(db.String())
    zip = db.Column(db.String())
    address = db.Column(db.String(), index=True)
    category = db.Column(db.String(), index=True)

And another table which holds the relation between the business records and the company as

import_data_company_rel = db.Table('import_data_company_rel',
                                   db.Column('record_id', UUID(as_uuid=True), db.ForeignKey('import_data.id', ondelete="CASCADE"), primary_key=True),
                                   db.Column('company_id', db.Integer, db.ForeignKey('res_company.id'), primary_key=True)
                                   )

The website offers a feature to API validate the phone numbers of business records. This information is stored in a third table which holds the information of which records are API validated by which company.

class ApiRequestRecord(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    company_id = db.Column(db.Integer, db.ForeignKey('res_company.id', ondelete='CASCADE'), nullable=False, index=True)
    create_date = db.Column(db.DateTime, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('res_users.id'))
    data_record_id = db.Column(UUID(as_uuid=True), db.ForeignKey('import_data.id', ondelete='CASCADE'), nullable=False, index=True)
    type = db.Column(db.String(72))  # mobile_check, realtime_mobile_check
    phone = db.Column(db.String)
    valid_number = db.Column(db.Boolean, index=True)  # can be True/False when API validated
    validity_check_date = db.Column(db.DateTime)

I have created hybrid property columns in the ImportData model, which shows if a record is API validated and the result, and the date at which the record was validated, depending upon the current_user.

class ImportData(db.Model):
    # fields definitions
    def get_api_request_detail_result(self, field, type_):
        # q = f"""SELECT arr.valid_number FROM import_data INNER JOIN import_data_company_rel idcr ON idcr.record_id=import_data.id INNER JOIN api_request_record arr ON arr.data_record_id=import_data.id WHERE arr.data_record_id='{str(self.id)}' AND arr.company_id={current_user.company_id} AND arr.type in ('mobile_check', 'realtime_mobile_check') ORDER BY arr.create_date DESC Limit 1;"""
        # result = db.engine.execute(q)
        result = db.session.query(getattr(ApiRequestRecord, field)).select_from(ImportData).join(import_data_company_rel, import_data_company_rel.c.record_id == ImportData.id).join(ApiRequestRecord, ApiRequestRecord.data_record_id == ImportData.id).filter(ApiRequestRecord.data_record_id == self.id, ApiRequestRecord.company_id == current_user.company_id, ApiRequestRecord.type.in_(type_)).order_by(ApiRequestRecord.create_date.desc()).limit(1)

        value = None
        for r in result:
            value = r[0]
        return value

    @hybrid_property
    def valid_number(self):
        return self.get_api_request_detail_result('valid_number', ['mobile_check', 'realtime_mobile_check'])

    @hybrid_property
    def validity_check_date(self):
        return self.get_api_request_detail_result('validity_check_date', ['mobile_check', 'realtime_mobile_check'])

I know it's possible to create a class expression on the computed hybrid columns to query them in sqlalchemy. Here is what I tried for valid_number field. It is not working

@valid_number.expression
def valid_number(cls):
    return (
        select([
            case([(exists().where(and_(
                ApiRequestRecord.data_record_id == cls.id,
                ApiRequestRecord.company_id == current_user.company_id, )).correlate(cls), True)], else_=False,
            ).label("valid_number")
        ]).label("valid_number")
    )

Here is what I'm trying to query, if the valid_number, validity_check_date were regular fields.

1. View the field values, but those fields do not appear in the WHERE condition.

SELECT import_data.id, import_data.phone, import_data.business_name, import_data.valid_number, import_data.validity_check_date
FROM import_data 
INNER JOIN import_data_company_rel idcr 
ON idcr.record_id=import_data.id
WHERE idcr.company_id={current_user.company_id} 
AND import_data.business_name ILIKE '%floor%' 
ORDER BY import_data.validity_check_date DESC 
LIMIT 100;

2. View the field values, and those fields appear in WHERE condition too.

SELECT import_data.id, import_data.phone, import_data.business_name, import_data.valid_number, import_data.validity_check_date
FROM import_data 
INNER JOIN import_data_company_rel idcr 
ON idcr.record_id=import_data.id
WHERE idcr.company_id={current_user.company_id}
AND import_data.business_name ILIKE '%floor%' 
AND valid_number IS NOT null
ORDER BY import_data.validity_check_date DESC;
LIMIT 100;

3. Another example where those fields appear in WHERE condition.

SELECT import_data.id, import_data.phone, import_data.business_name, import_data.valid_number, import_data.validity_check_date
FROM import_data 
INNER JOIN import_data_company_rel idcr 
ON idcr.record_id=import_data.id
WHERE idcr.company_id={current_user.company_id}
AND import_data.business_name ILIKE '%floor%' 
AND import_data.valid_number=true
AND import_data.validity_check_date>'2021-01-01'
ORDER BY import_data.validity_check_date DESC;

How to convert these sql queries when they are not actual fields but computed? And how will be the sqlalchemy hybrid_property expressions of these queries be like? Thanks in advance

question from:https://stackoverflow.com/questions/65888217/sql-query-on-a-computed-field

Answer 1

Waitting for answers