How should i change my SQLAlchemy query?

Question

I am new using python and trying to use SQLAlchemy to connect with my database. I have some tables to get info from news stories. The first thing I do is run the query in the sql console and then build it with SQLAlchemy.

I'm trying to make a search based on tags, but I need all the story's tags, I tested the following query in the sql console and it works fine:

SELECT s.id as id, s.date as date, s.image as image, so.  name as source_name, 
so.logo as source_logo, s.title as title, s.url as url, (
    SELECT GROUP_CONCAT(t.name SEPARATOR ', ') 
    FROM tag as t 
    WHERE NOT (t.name LIKE '%trump%' OR t.name LIKE '%president%') 
AND t.story_id = s.id ORDER BY t.weight) as tags 
FROM story as s, source as so 
WHERE s.id IN (
    SELECT DISTINCT(t.story_id) 
    FROM tag as t  
    WHERE t.name LIKE '%trump%' OR t.name LIKE '%president%'
    ORDER BY t.story_id) 
AND s.date BETWEEN NOW() - INTERVAL 50 DAY AND NOW() AND s.source_id = so.id

Then I'm trying to build that query with SQLAlchemy, and the problem is that the "group_concat" function concats all the tags from every row together, instead of concatenating the tags for each row

story = DbAdapter.story_table.c
source = DbAdapter.source_table.c
tag = DbAdapter.tag_table.c

sel = select([story.id, story.date, story.image, source.name, 
source.logo, story.title, story.url,
(select([func.group_concat(tag.name)])
.where(and_(not_(or_(*params)), story.id == tag.story_id))
.order_by(tag.weight)).alias("tags")])
.where(and_(and_(story.id.in_(select([tag.id.distinct()])
.where(or_(*params))), story.date.between(five_weeks_ago, current_time)), 
story.source_id == source.id))
.order_by(story.id)

And the console prints the query in sql format

SELECT story.id, story.date, story.image, source.name, source.logo, story.title, 
story.url, tags.group_concat_1 
FROM story, source, (
    SELECT group_concat(tag.name) AS group_concat_1 
    FROM tag, story 
    WHERE NOT (tag.name LIKE %s OR tag.name LIKE %s OR tag.name LIKE %s) 
    AND story.id = tag.story_id ORDER BY tag.weight) AS tags 
WHERE story.id IN (SELECT DISTINCT tag.id 
FROM tag 
WHERE tag.name LIKE %s OR tag.name LIKE %s OR tag.name LIKE %s) 
AND story.date BETWEEN %s AND %s AND story.source_id = source.id 
ORDER BY story.id

The only difference I see is that SQLAlchemy creates this new query where it moves the “Select Group_Concat” query from the ATTRIBUTES to the FROM Statement.

Is my SQLAlchemy query correct?

Answer 1

You need to use .label(), not .alias().

.label() aliases a subquery as a scalar expression (i.e. it goes in the SELECT list), while .alias() aliases a subquery as a table expression (i.e. it goes in the FROM list).