Columns with foreign key constraints are not appearing in query result attributes

[mcodd]

Hi! Thank you very much for picking up this module and keeping it up to date - I am reviving a project I was working on 5 or 6 years ago or so and wouldn't have any shot at all at getting it back up and running without your work here!

I'm seeing a strange issue where any columns that have ForeignKey constraints don't get returned as attributes. Apologies if I'm missing something obvious here as there don't appear to be any other issues related to this or documentation that I could find discussing it. There are quite a few major versions of things I'm working through updating here so it may be that I'm doing something deprecated/incorrect at the sqlalchemy/flask layer.

I am using:

• Python 3.9.16
• Flask-Restless-NG 3.2.3
• Flask 3.0.3
• Flask-SQLAlchemy 3.1.1

Here's my definition of a table with the constraint (on the PLAYER_ID column):

class PlayerPosition(db.Model):
    __tablename__ = 'player_position'
    __table_args__ = (
        db.UniqueConstraint('PLAYER_ID', 'POSITION'),
        {'mysql_engine':'InnoDB'}
        )
    id = db.Column(db.Integer, primary_key=True, nullable=False)
    PLAYER_ID = db.Column(db.Integer, db.ForeignKey('player_metadata.id'))
    player = db.relationship("PlayerMetadata", backref='player_positions')
    POSITION = db.Column(db.Enum('P','C','1B','2B','3B','SS','OF','DH'), nullable=False)
    GAMES_AT_POSITION = db.Column(db.Integer, nullable=False)

And here's that related table, just for reference:

class PlayerMetadata(db.Model):
    __tablename__ = 'player_metadata'
    __table_args__ = (
        db.UniqueConstraint('RETRO_ID'),
        {'mysql_engine':'InnoDB'}
        )
    id = db.Column(db.Integer, primary_key=True, nullable=False)
    RETRO_ID = db.Column(db.String(8), nullable=False)
    LASTNAME = db.Column(db.String(32), nullable=False)
    FIRSTNAME = db.Column(db.String(32), nullable=False)

I'm setting up the API like this:

        manager.create_api(PlayerPosition, methods=['GET', 'POST', 'DELETE'], url_prefix = '/api/2015')

When I query the player_position api i see GAMES_AT_POSITION and POSITION fields returned as attributes, but not the PLAYER_ID. Can you explain why this is the case? Here's a curl showing the issue:

$ curl -s -H 'Accept: application/vnd.api+json' http://localhost:8081/api/2015/player_position/100 | jq .
{
  "data": {
    "attributes": {
      "GAMES_AT_POSITION": 1000,
      "POSITION": "DH"
    },
    "id": "100",
    "relationships": {
      "player": {
        "data": {
          "id": "1133",
          "type": "player_metadata"
        }
      }
    },
    "type": "player_position"
  },
  "jsonapi": {
    "version": "1.0"
  }
}

This behavior is consistent in other tables in my project as well. Thank you for taking a look!

[mrevutskyi]

Hi Matt,

that is an intended behavior that follows the JSON API spec

Keys that reference related resources (e.g. author_id) SHOULD NOT appear as attributes. Instead, relationships SHOULD be used

So when a client fetches a player_position it still can access player's ID via player relationship

If for some reason you really need to include PLAYER_ID you should be able to do so via additional_attributes

manager.create_api(PlayerPosition, methods=['GET', 'POST', 'DELETE'], url_prefix = '/api/2015', additional_attributes=['PLAYER_ID'])

https://flask-restless-ng.readthedocs.io/en/latest/fetching.html#specifying-which-fields-appear-in-responses

[mcodd]

Thanks Maksym, That all makes sense and I'm able to work thru the relationship objects to get what I need. It looks like sometimes this gets a little more complicated when it's a relationship where I need to specify the foreignkey explicitly - in those cases i don't get the ID back in the reference, only the FK key/value (the relationship response doesn't include the primary key then, just the explicitly-defined FK). In those cases, it looks like I can just use the include parameter to get the full set of attributes including the primary key value I need.

One quick note, I tried your suggestion regarding additional_attributes (since that would be pretty convenient for me in a couple cases here) and it doesn't seem to work - I don't get any more attributes back in the response. Could you confirm it works for you to specify a relationship as an additional_attibute and get that field back? If it does there must be something specific to my environment...

thanks, matt

[mrevutskyi]

I've checked the code and it seems that I've misled your about additional_attributes, currently it still removes FKs even if they are listed in additional attributes. I think I can change that if using relationships still does not work for you for some reason

[mcodd]

Definitely not a blocker for me to use relationships and the include parameter where needed to get at the data, more of a convenience, and I suppose there's probably also something to be said for just following the JSON API spec here in any case. Feel free to close out this issue and thanks again for your help here!

matt