CODEWITHSUNDEEP
pythonflasksqlalchemy
codi6
codi6

Reputation: 566

Cross referencing two tables in python?

I have a table called article. It has a column article.id and article.snippet.

I have another table called article_vote. It has a column article_id (which is a foreign key for article.id) and vote_choice_id (which is a number from 1 to 6).

I want to plug a snippet in and have the program count all the vote_choice_id’s for that snippet when the vote_choice_id = 1, vote_choice_id = 2, vote_choice_id = 3, etc. That means cross-referencing these two tables, which confuses me.

Here's my current code:

##############################################
class Article(db.Model):
    # object mirrors table 'article'
    id = db.Column('id', db.Integer, primary_key=True)
    title = db.Column(db.String(400))
    url = db.Column(db.String(400))
    image_url = db.Column(db.String(400))
    snippet = db.Column(db.String(1000))
    date_upload = db.Column(db.DateTime(), default=datetime.utcnow)

    def __init__(self, title, url, image_url, snippet):
        self.title = title
        self.url = url
        self.image_url = image_url
        self.snippet = snippet


##############################################
class ArticleVote(db.Model):
    # object mirrors table 'article_vote'
    id = db.Column('id', db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.fb_id'))  # Sloppy
    article_id = db.Column(db.Integer, db.ForeignKey('article.id'))
    vote_choice_id = db.Column(db.Integer)
    comment = db.Column(db.String(400))

    def __init__(self, user_id, article_id, vote_choice_id, comment):
        self.user_id = user_id
        self.article_id = article_id
        self.vote_choice_id = vote_choice_id
        self.comment = comment

And then here's what I wrote after I tried to follow an example that I couldn't quite understand:

##############################################
class PublicationVoteSummary():
# This object keeps the count of votes for a Publication

    def __init__(self, article_id):
        self.snippet = snippet
        self.choice_count = {}  # count by textual choice
        self.choice_id = {}

        pub_choice_list = VoteChoice.getVoteChoiceList()

        for item in pub_choice_list:
            self.choice_id[str(item.id)] = item.choice
        for item in pub_choice_list:
            self.choice_count[item.choice] = 0
        self.total_votes = 0

    def addVote(self, choice):
        self.choice_count[choice] = self.choice_count[choice] + 1
        self.total_votes = self.total_votes + 1

    def getVoteCount(self, choice):
        return self.choice_count[choice]

##############################################
def retrieve_publication_summary(article_id):
    pub_obj = PublicationSummary(article_id)
    publication_list = db.session.query(ArticleVote, Snippet).filter(ArticleVote.article_id == article_id).filter(
        ArticleVote.vote_choice_id == 1).all()
   for item in vote_choice_id:
        # avs_obj.appendComment(item.comment) # 09/27 - added to show comments in results page
        avs_obj.appendDetail((item[1].name, text, item[0].comment))  
    return pub_obj

I also tried this:

SELECT 1.(count)
FROM   article_vote
       INNER JOIN vote_choice_id
           ON Article.id = Article_vote.article_id
WHERE  Article_vote.vote_choice_id = 1

Upvotes: 0

Views: 590

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55600

This query will output the snippet and count of votes for each vote_choice_id for that snippet (as long as there at least one vote_choice_id for a given snippet).

import sqlalchemy as sa

q = (db.session.query(Article.snippet, ArticleVote.vote_choice_id, sa.func.count(ArticleVote.id))
              .join(ArticleVote, Article.id == ArticleVote.article_id)
              .group_by(Article.snippet, ArticleVote.vote_choice_id)
              .order_by(Article.snippet, ArticleVote.vote_choice_id.desc()))

for snippet, vote_choice_id, count in q:
    print(snippet, vote_choice_id, count)

The code generates this SQL:

SELECT article.snippet AS article_snippet, article_vote.vote_choice_id AS article_vote_vote_choice_id, count(article_vote.id) AS count_1 
FROM article 
JOIN article_vote ON article.id = article_vote.article_id 
GROUP BY article.snippet, article_vote.vote_choice_id 
ORDER BY article.snippet, article_vote.vote_choice_id DESC

And produces output like

New York Times 6 38
New York Times 5 46
New York Times 4 36
New York Times 3 44
New York Times 2 34
...

To get the count for a given snippet and vote_choice_id can be obtained by adding filters to the query (and removing vote_choice_id from the output, and the GROUP BY and ORDER BY clauses):

q = (db.session.query(Article.snippet, sa.func.count(ArticleVote.id))
               .join(ArticleVote, Article.id == ArticleVote.article_id)
               .filter(Article.snippet == 'New York Times')
               .filter(ArticleVote.vote_choice_id == 2)
               .group_by(Article.snippet))
for snippet, count in q:
    print(snippet, count)

Generating this SQL:

SELECT article.snippet AS article_snippet, count(article_vote.id) AS count_1 
FROM article 
JOIN article_vote ON article.id = article_vote.article_id 
WHERE article.snippet = ? AND article_vote.vote_choice_id = ?

and this output:

New York Times 34

Upvotes: 1

Related Questions