def get_businesses_by_category(self, category_id, limit=20, offset=0): """Return paginated businesses in a category""" sql = """ SELECT b.*, c.name as category_name, AVG(r.rating) as avg_rating FROM businesses b JOIN categories c ON b.category_id = c.id LEFT JOIN reviews r ON b.id = r.business_id WHERE b.category_id = ? AND b.is_active = 1 GROUP BY b.id ORDER BY b.name ASC LIMIT ? OFFSET ? """ return db.query(sql, [category_id, limit, offset])
def get_business_profile(self, business_id): """Fetch full business details + reviews""" biz = db.query_one("SELECT * FROM businesses WHERE id = ?", [business_id]) if not biz: return None reviews = db.query("SELECT * FROM reviews WHERE business_id = ? ORDER BY created_at DESC", [business_id]) biz['reviews'] = reviews return biz business directory script
def search_businesses(self, keyword, location=None): """Search by name, description, or address""" sql = """ SELECT *, MATCH(name, description) AGAINST(? IN NATURAL LANGUAGE MODE) as relevance FROM businesses WHERE is_active = 1 AND (name LIKE ? OR description LIKE ? OR address LIKE ?) """ like_term = f"%{keyword}%" params = [keyword, like_term, like_term, like_term] if location: sql += " AND address LIKE ?" params.append(f"%{location}%") sql += " ORDER BY relevance DESC" return db.query(sql, params) """ return db