query.sql

SELECT track_1.id AS track_1_id, base_collection.id AS base_collection_id, base_collection.name AS base_collection_name, base_collection.main_base_id AS base_collection_main_base_id 
FROM track AS track_1 
JOIN track_base_collection_association AS track_base_collection_association_1 ON track_1.id = track_base_collection_association_1.track_id 
JOIN base_collection ON base_collection.id = track_base_collection_association_1.base_collection_id 
WHERE track_1.id IN ($1::INTEGER)


SELECT base_collection.id, base_collection.name, base_collection.main_base_id 
FROM base_collection 
WHERE base_collection.id = $1::INTEGER

SELECT bases.id AS bases_id, bases.name AS bases_name 
FROM bases 
WHERE bases.id IN ($1::INTEGER)

SELECT base_collection_1.id AS base_collection_1_id, track.id AS track_id, track.title AS track_title, track.artist AS track_artist, track.label AS track_label, track.open_name AS track_open_name, track.file_path AS track_file_path, track.album AS track_album, track.genre AS track_genre, track.created_at AS track_created_at 
FROM base_collection AS base_collection_1 JOIN track_base_collection_association AS track_base_collection_association_1 ON base_collection_1.id = track_base_collection_association_1.base_collection_id JOIN track ON track.id = track_base_collection_association_1.track_id 
WHERE base_collection_1.id IN ($1::INTEGER)

SELECT base_collection.id, base_collection.name, base_collection.main_base_id, bases_1.id AS id_1, bases_1.name AS name_1 
FROM base_collection LEFT OUTER JOIN bases AS bases_1 ON bases_1.id = base_collection.main_base_id 
WHERE base_collection.id = $1::INTEGER

SELECT base_collection_1.id AS base_collection_1_id, track.id AS track_id, track.title AS track_title, track.artist AS track_artist, track.label AS track_label, track.open_name AS track_open_name, track.file_path AS track_file_path, track.album AS track_album, track.genre AS track_genre, track.created_at AS track_created_at 
FROM base_collection AS base_collection_1 JOIN track_base_collection_association AS track_base_collection_association_1 ON base_collection_1.id = track_base_collection_association_1.base_collection_id JOIN track ON track.id = track_base_collection_association_1.track_id 
WHERE base_collection_1.id IN ($1::INTEGER)


SELECT authors.name, top_3_articles.title, top_3_articles.views
FROM authors
JOIN LATERAL (
    SELECT articles.title AS title, articles.views AS views
    FROM articles
    WHERE articles.author_id = authors.id
    ORDER BY articles.views DESC
    LIMIT 3
) AS top_3_articles ON true;

subquery = sa.select(
    (articles_table.c.title, articles_table.c.views),
).where(
    articles_table.c.author_id == authors_table.c.id,
).order_by(
    articles_table.c.views.desc(),
).limit(3).lateral('top_3_articles')

query = sa.select(
    (authors_table.c.name, subquery.c.title, subquery.c.views),
).select_from(
    authors_table.join(subquery, sa.true()),
)