57 lines
1.5 KiB
Python
57 lines
1.5 KiB
Python
|
#!/usr/bin/env python3
|
||
|
|
import psycopg2
|
||
|
|
from psycopg2.extras import RealDictCursor
|
||
|
|
|
||
|
|
# Get connection from .env
|
||
|
|
with open('.env') as f:
|
||
|
|
for line in f:
|
||
|
|
if line.startswith('DATABASE_URL'):
|
||
|
|
db_url = line.split('=', 1)[1].strip()
|
||
|
|
break
|
||
|
|
|
||
|
|
# Parse URL: postgresql://user:pass@host:port/dbname
|
||
|
|
parts = db_url.replace('postgresql://', '').split('@')
|
||
|
|
user_pass = parts[0].split(':')
|
||
|
|
host_port_db = parts[1].split('/')
|
||
|
|
host_port = host_port_db[0].split(':')
|
||
|
|
|
||
|
|
conn = psycopg2.connect(
|
||
|
|
host='localhost',
|
||
|
|
port=5433,
|
||
|
|
database=host_port_db[1],
|
||
|
|
user=user_pass[0],
|
||
|
|
password=user_pass[1]
|
||
|
|
)
|
||
|
|
|
||
|
|
cursor = conn.cursor(cursor_factory=RealDictCursor)
|
||
|
|
|
||
|
|
# Test the exact query from views.py
|
||
|
|
query = """
|
||
|
|
SELECT s.*,
|
||
|
|
c.name as customer_name,
|
||
|
|
CONCAT(COALESCE(cont.first_name, ''), ' ', COALESCE(cont.last_name, '')) as kontakt_navn
|
||
|
|
FROM sag_sager s
|
||
|
|
LEFT JOIN customers c ON s.customer_id = c.id
|
||
|
|
LEFT JOIN LATERAL (
|
||
|
|
SELECT cc.contact_id
|
||
|
|
FROM contact_companies cc
|
||
|
|
WHERE cc.customer_id = c.id
|
||
|
|
ORDER BY cc.is_primary DESC NULLS LAST, cc.id ASC
|
||
|
|
LIMIT 1
|
||
|
|
) cc_first ON true
|
||
|
|
LEFT JOIN contacts cont ON cc_first.contact_id = cont.id
|
||
|
|
WHERE s.deleted_at IS NULL
|
||
|
|
ORDER BY s.created_at DESC
|
||
|
|
LIMIT 5
|
||
|
|
"""
|
||
|
|
|
||
|
|
cursor.execute(query)
|
||
|
|
rows = cursor.fetchall()
|
||
|
|
|
||
|
|
print("Query results:")
|
||
|
|
print("-" * 80)
|
||
|
|
for row in rows:
|
||
|
|
print(f"ID: {row['id']}, Titel: {row['titel'][:30]}, Customer: {row.get('customer_name')}, Kontakt: {row.get('kontakt_navn')}")
|
||
|
|
|
||
|
|
conn.close()
|