4

Flask-sqlalchemy Models for my book and actions tables are

class Book(db.Model):

    __tablename__ = 'book'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    activity = db.relationship('Actions')

class Actions(db.Model):

    __tablename__ = 'actions'

    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('book.id'), nullable=False)
    action = db.Column(db.Enum(['requested', 'issued', 'opened', 'closed']), nullable=True)
    created_on = db.Column(db.DateTime, default=_get_date_time)

Basically it's a one-to-many relationship between a book and actions. And I am using flask-restless for the API.

I need to get

All books that have not been closed

I have tried below search queries

q={
"filters": [
    {
      "name": "activity",
      "op": "any",
      "val": {
        "name": "action",
        "op": "not_equal_to",
        "val": "closed"
      }
    }
  ]
}

and

q={
  "filters": [
    {
      "name": "activity",
      "op": "any",
      "val": {
        "name": "action",
        "op": "not_in",
        "val": ["closed"]
      }
    }
  ]
}

But I am getting wrong result

{
"num_results": 30,
"objects": [
{
  "activity": [
    {
      "action": "opened",
      "created_on": "2015-06-05T17:05:07",
      "id": 31
    },
    {
      "action": "closed",
      "created_on": "2015-06-05T17:05:44",
      "id": 32
    }
  ],
  "id": 1,
  "name": "K&R"
  ....
},
...
]
"page": 1,
"total_pages": 3
}

Am I making some mistake here or this kind of thing is not possible in restless?

Please help me out.

Hussain
  • 5,057
  • 6
  • 45
  • 71

1 Answers1

2

Try this: 

q = { "filters":[   
         {
          "name":"activity__action", 
          "op":"not_equal_to", 
          "val":"closed"
         }
      ]
}
Meph-
  • 657
  • 1
  • 8
  • 20
  • It gives an error - `Can't compare a collection to an object or collection; use contains() to test for membership.` with response = `{ "message": "Unable to construct query" }` – Hussain Jun 19 '15 at 07:48
  • and with "op":"any", val:"opened" ? (last paragraph of https://flask-restless.readthedocs.org/en/0.7.0/searchformat.html ) – Meph- Jun 19 '15 at 08:51
  • But for that it executes something like `SELECT * FROM book WHERE EXISTS (SELECT 1 FROM actions WHERE book.id = actions.book_id AND actions.action = %s) ORDER BY book.id ASC` – Hussain Jun 19 '15 at 09:18
  • And I guess I want something like `SELECT * FROM book WHERE NOT EXISTS (SELECT 1 FROM actions WHERE book.id = actions.book_id AND actions.action = 'closed') ORDER BY book.id ASC` – Hussain Jun 19 '15 at 09:24
  • @Hussain did you manage to figure this out the solution? – Asive Dlaba Jun 23 '20 at 10:12