Flask-Restplus how to combine data from 2 table into single data response?

Question

I am using Flask-Restplus and SQLAlchemy to develop my API. I want to return a response with information from two SQAlchemy objects, a user and a device, with a 1:1 relationship between them.

I have a query that looks like this:

details = db.session.query(User, Device).filter(User.id == Device.id) \
                    .filter(User.email== data['email'])\
                    .all()

For now, the result of the query above can be printed in console is like this:

[(<User 'None'>, <Device 1>)]

I want my API endpoint to return the following JSON:

{
  "data": [
    [
      {
        "id": 20,
        "name": null,
        "token": "Some String here"
      }
    ]
  ]
}

Here is my DTO:

class UserDto:
    # this is for input
    user = api.model('user', {
        'email': fields.String(required=False, description='phone number'),
        'name': fields.String(required=False, description='username'),
        'device_id': fields.String(required=False,description='user_device_id'),
   })

   # this is for output
    details = api.model('details', {
        'id': fields.Integer(required=False, description='the id'),
        'name': fields.String(required=False, description='name'),
        'token': fields.String(required=False, description='token')
    })

Models for User and Device:

class User(db.Model):
    __tablename__ = "users_info"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.Integer, unique=True, nullable=True)
    email = db.Column(db.String)
    device = db.relationship('Device', backref='user')
    # .. more fields ..


class Device(db.Model):
    __tablename__ = "user_device"

    user_device_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    id = db.Column(db.Integer, db.ForeignKey(User.id))
    token = db.Column(db.String, nullable=True)
    # .. more fields ..

I order to achieve JSON result above, I want the id and name is from the User object, and token is from the Device object.

Controller:

api = UserDto.api
_user = UserDto.user
_details = UserDto.details

@api.route('/')
class User(Resource):
    @api.response(201, 'successful')
    @api.expect(_user, validate=True)
    @api.marshal_list_with(_details, envelope='data')
    def post(self):
        data = request.json
        return user(data=data)

Actual Response:

{
  "data": [
    [
      {
        "id": 20,
        "name": null,
        "token": null
      },
      {
        "id": 20,
        "name": null,
        "token": "some string here"
      }
    ]
  ]
}

As you can see here, the same record appears 2 twice (once with token being null and once with token with the string I want).

How can I achieve the response that I want above?

Answer 1

You get two output entries because you have two objects (rows) in your query. You don't need to add the Device object to your query, because it is already available as the user.device. Adjust your Restplus model to use user.device.token attribute.

So you need to alter your query to only load the user:

return User.query.filter_by(email == data['email']).all()

If the User.email field is supposed to be unique (an email address should really reference just one user in your database), consider using .first() instead of .all(), then wrapping the return value of .first() in a list, or more logical, changing your return value into a single JSON object without a list. Also consider using the first_or_404() method to automatically produce a 404 Not Found response in case there is no user with this email.

You then need to configure your details API model:

details = api.model('details', {
    'id': fields.Integer(required=False, description='the id'),
    'name': fields.String(required=False, description='name'),
    'token': fields.String(
        attribute='device.token',
        required=False,
        description='token'
    )
})

Note the attribute field for token, we are telling Flask-Restplus here to take the token attribute of the device attribute of each object.

This does require you to alter your SQLAlchemy model, because you don't have a one-on-one relationship defined right now. You have a one to many, so user.device is actually a list. Add uselist=False to your device relationship:

class User(db.Model):
    __tablename__ = "users_info"

    # ...
    device = db.relationship('Device', backref='user', uselist=False)

That restricts you to a scalar value, so a single device object per user.

Or, if you did mean for it to be a one-to-many relationship, then you'll have to replace the token field with a tokens list:

details = api.model('details', {
    'id': fields.Integer(required=False, description='the id'),
    'name': fields.String(required=False, description='name'),
    'token': fields.List(
        fields.String(attribute='token', required=False),
        description='array of tokens for all user devices',
        attribute='device'
    )
})

You may want to rename device to devices in that case, to better reflect that you have multiple.

Note that in all cases, you'll want to think about loading strategies. If you are always going to access the device associated with the user, adding lazy="joined" to the device relationship is going to help performance. Alternatively, add .options(db.joinedload('device')) to your User query, before the .first() or .all() call:

return User.query.filter_by(email == data['email']).options(db.joinedload('device')).all()