0

ExpenseList table have two foreign keys, i want to fetch list of expenses from this category with corresponding category. Currently it is returning category_id only when i am applying following query:

query = ExpenseList.query.filter_by(user_id=6)
return make_response(jsonify([i.serialize for i in query.all()])),200

Its returning response like this:

[
{
    "category": 3,
    "created_on": "Sun, 05 Nov 2017 09:40:19 GMT",
    "money_spent": "50",
    "name": "DVD"
},
{
    "category": 3,
    "created_on": "Sun, 05 Nov 2017 09:40:39 GMT",
    "money_spent": "100",
    "name": "Movie"
}
]

Model schema is 

class ExpenseList(db.Model):
    __tablename__ = 'expense_list'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(500), unique=True, nullable=False)
    money_spent = db.Column(db.String(500), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category_list.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    is_recurring = db.Column(db.Boolean,nullable=False, default=False)
    created_on = db.Column(db.DateTime, nullable=False)

    @property
    def serialize(self):
        return {
            'name' : self.name,
            'money_spent' : self.money_spent,
            'category' : self.category_id,
            'created_on' : self.created_on
         }


class CategoryList(db.Model):
    __tablename__ = 'category_list'

    id = db.Column(db.Integer,primary_key = True, autoincrement=True)
    name = db.Column(db.String(500), unique=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_on = db.Column(db.DateTime, nullable=False)

How can i get category name instead of category_id in response object, I also tried backrefs, lazy etc but don't know where i am going wrong.

univerio
  • 19,548
  • 3
  • 66
  • 68
Magnotta
  • 933
  • 11
  • 34
  • possible duplicate; https://stackoverflow.com/questions/6044309/sqlalchemy-how-to-join-several-tables-by-one-query – Paul Maxwell Nov 05 '17 at 06:56
  • i read that question but did n't find perfect answer – Magnotta Nov 05 '17 at 07:32
  • `query(ExpenseList).join(CategoryList).f‌​ilter_by(user_id=6)` does this help? I'm not able to trial anything so this is probably all I can offer. also try [SQLAlchemy filter query by related object](https://stackoverflow.com/questions/2010454/sqlalchemy-filter-query-by-related-object) – Paul Maxwell Nov 05 '17 at 07:38
  • yeah this works,but This actually doesn't do a join at all, it returns row objects in a tuple. In this case, it'd return [(, ,),...] – Magnotta Nov 05 '17 at 08:11

1 Answers1

0

Here is a join You want

query(ExpenseList.name.label('expense_name'),
    ExpenseList.money_spent,
    CategoryList.name.label('category_name'),
    ExpenseList.created_on)\
.join(CategoryList, ExpenseList.category_id == CategoryList.id)\
.filter_by(ExpenseList.user_id=6)

and a worrying part in Your CategoryList model is:

name = db.Column(db.String(500), unique=True, autoincrement=True)
Giedrius.S
  • 70
  • 7