111 lines
4.7 KiB
Python
111 lines
4.7 KiB
Python
"""Listing model.
|
|
|
|
Portability notes (see README "MySQL upgrades"):
|
|
- Location stored as lat/lng floats; radius search uses a bounding-box prefilter
|
|
+ haversine refine. On MySQL you may add a generated POINT + SPATIAL INDEX.
|
|
- Hot filter fields (condition/job_type/salary) are denormalized onto indexed
|
|
columns, populated in the service layer from `attributes`. On MySQL these can
|
|
instead be GENERATED ALWAYS columns over the JSON.
|
|
- Free-text search uses the accent-insensitive `title_norm` + body LIKE. On
|
|
MySQL add a FULLTEXT(title, body) index and switch to MATCH ... AGAINST.
|
|
"""
|
|
from datetime import datetime
|
|
from app.utils.time import utcnow
|
|
from sqlalchemy import JSON, Index
|
|
from app.extensions import db
|
|
from app.models.enums import ListingStatus, Lang
|
|
|
|
|
|
class Listing(db.Model):
|
|
__tablename__ = "listings"
|
|
|
|
id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"),
|
|
primary_key=True, autoincrement=True)
|
|
user_id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"),
|
|
db.ForeignKey("users.id"), nullable=False, index=True)
|
|
category_id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"),
|
|
db.ForeignKey("categories.id"), nullable=False, index=True)
|
|
|
|
title = db.Column(db.String(140), nullable=False)
|
|
title_norm = db.Column(db.String(140), nullable=False, index=True) # accent-stripped
|
|
body = db.Column(db.Text, nullable=False)
|
|
lang = db.Column(db.Enum(Lang), nullable=False, default=Lang.en)
|
|
|
|
price_cents = db.Column(db.Integer, nullable=True, index=True)
|
|
|
|
# location
|
|
zip = db.Column(db.String(12), nullable=True, index=True)
|
|
city = db.Column(db.String(80), nullable=True)
|
|
state = db.Column(db.String(2), nullable=True, index=True)
|
|
lat = db.Column(db.Float, nullable=True)
|
|
lng = db.Column(db.Float, nullable=True)
|
|
|
|
# category-specific values
|
|
attributes = db.Column(JSON, nullable=False, default=dict)
|
|
# denormalized hot filter columns (populated from attributes on save)
|
|
attr_condition = db.Column(db.String(40), nullable=True, index=True)
|
|
attr_job_type = db.Column(db.String(40), nullable=True, index=True)
|
|
attr_salary_min = db.Column(db.Integer, nullable=True, index=True)
|
|
attr_salary_max = db.Column(db.Integer, nullable=True, index=True)
|
|
|
|
status = db.Column(db.Enum(ListingStatus), nullable=False,
|
|
default=ListingStatus.active, index=True)
|
|
is_featured = db.Column(db.Boolean, nullable=False, default=False)
|
|
bump_at = db.Column(db.DateTime, nullable=True)
|
|
flag_count = db.Column(db.Integer, nullable=False, default=0)
|
|
view_count = db.Column(db.Integer, nullable=False, default=0)
|
|
expires_at = db.Column(db.DateTime, nullable=False, index=True)
|
|
|
|
created_at = db.Column(db.DateTime, default=utcnow, nullable=False)
|
|
updated_at = db.Column(db.DateTime, default=utcnow,
|
|
onupdate=utcnow, nullable=False)
|
|
|
|
user = db.relationship("User", backref=db.backref("listings", lazy="dynamic"))
|
|
category = db.relationship("Category", back_populates="listings")
|
|
images = db.relationship("ListingImage", back_populates="listing",
|
|
order_by="ListingImage.sort_order",
|
|
cascade="all, delete-orphan", lazy="selectin")
|
|
|
|
__table_args__ = (
|
|
Index("ix_listing_browse", "status", "expires_at"),
|
|
Index("ix_listing_sort", "is_featured", "bump_at"),
|
|
)
|
|
|
|
@property
|
|
def is_live(self):
|
|
return (self.status == ListingStatus.active
|
|
and self.expires_at > utcnow())
|
|
|
|
@property
|
|
def price_display(self):
|
|
if self.price_cents is None:
|
|
return None
|
|
return f"${self.price_cents / 100:,.2f}"
|
|
|
|
@property
|
|
def cover(self):
|
|
return self.images[0] if self.images else None
|
|
|
|
def __repr__(self):
|
|
return f"<Listing {self.id} {self.title[:24]!r}>"
|
|
|
|
|
|
class ListingImage(db.Model):
|
|
__tablename__ = "listing_images"
|
|
|
|
id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"),
|
|
primary_key=True, autoincrement=True)
|
|
listing_id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"),
|
|
db.ForeignKey("listings.id"), nullable=False, index=True)
|
|
path = db.Column(db.String(255), nullable=False)
|
|
thumb_path = db.Column(db.String(255), nullable=True)
|
|
sort_order = db.Column(db.Integer, nullable=False, default=0)
|
|
width = db.Column(db.Integer, nullable=True)
|
|
height = db.Column(db.Integer, nullable=True)
|
|
created_at = db.Column(db.DateTime, default=utcnow, nullable=False)
|
|
|
|
listing = db.relationship("Listing", back_populates="images")
|
|
|
|
def __repr__(self):
|
|
return f"<ListingImage {self.id} of L{self.listing_id}>"
|