1

The problem is in the header of topic. So here are the details.

I am using Flask-SQLAlchemy and PostgreSQL and my db schema is like this:

class Settings(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(), unique=True)

    lang = db.relationship('Lang', order_by='Lang.id')

    def __init__(self, key, lang):
        self.key = key
        self.lang = lang

    def __repr__(self):
        return '<Settings %r, %r>' % (self.key, self.lang)

class Lang(db.Model):
    __tablename__ = 'settings_lang'
    id = db.Column(db.Integer, primary_key=True)
    lang = db.Column(db.String(2))
    value = db.Column(db.Text())

    parent_id = db.Column(db.Integer, db.ForeignKey('settings.id'))

    def __init__(self, lang, value):
        self.lang = lang
        self.value = value

    def __repr__(self):
        return '<Lang %r, %r>' % (self.lang, self.value)

I use query like this:

for data in db.session.query(Settings).join(Lang).filter(Lang.lang.contains('en')).all():
    print data

And expect it to return only settings with 'en' language. But instead I get all the languages.

What can be the problem and how to deal with it?

UPD: SQL output

SELECT settings.id AS settings_id, settings.key AS settings_key 
FROM settings JOIN settings_lang ON settings.id = settings_lang.parent_id 
WHERE settings_lang.lang LIKE '%%' || %(lang_1)s || '%%'
{'lang_1': 'en'}

SELECT settings_lang.id AS settings_lang_id, settings_lang.lang AS settings_lang_lang, 
       settings_lang.value AS settings_lang_value,
       settings_lang.parent_id AS  settings_lang_parent_id 
FROM settings_lang 
WHERE %(param_1)s = settings_lang.parent_id ORDER BY settings_lang.id
{'param_1': 1}

Example row from settings_lang:

        id    parent_id    lang    value
        1     1            "en"    "Research"
        2     1            "ru"    "НПП"
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
A. Martyn
  • 85
  • 1
  • 9
  • also see [this question](http://stackoverflow.com/questions/6350411/how-to-retrieve-executed-sql-code-from-sqlalchemy) on how to print raw sql queries executed by alchemy – alex vasi Mar 07 '13 at 14:30
  • Thank you, it may be helpful. Maybe the query is constructed somehow wrong – A. Martyn Mar 07 '13 at 14:48
  • First query look ok and it loads only Settings objects, except it's better to write `filter(Lang.lang='en')`. The second one just loads `Lang` by id following the relationships. So I don't believe you that query loads all settings. Please, look again at your database and your program output. – alex vasi Mar 07 '13 at 16:03
  • The problem is that Settings does not contain the language. It contains id and key, but Lang contains id, parent_id, value and lang. So when we retrieve records by id, it returns both languages, because parent_id is the same for both of them – A. Martyn Mar 07 '13 at 16:12
  • @A.Martyn Can you format your row pasting, as well as showing what headers map to what values? It's hard to tell with the way you pasted it. – cwgem Mar 07 '13 at 17:22
  • @A.Martyn Try this `db.session.query(Settings).join(Settings.lang).filter(Lang.lang=='en').all()` Also your `ru` seems to be pointing to an English string and your `en` seems to be pointing to a Russian string? – cwgem Mar 07 '13 at 17:33
  • @cwgem I tried this query, but still same result. As for the ru/en strings - yes, it was a mistake. Changed it to the true values – A. Martyn Mar 07 '13 at 17:40
  • @cwgem also, I tried to follow the guide here [link]http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying-with-joins[/link] and the joined juery also returns all addresses – A. Martyn Mar 07 '13 at 17:44
  • @A.Martyn Run this query *directly* on the DB server: `SELECT settings.id AS settings_id, settings.key AS settings_key FROM settings JOIN settings_lang ON settings.id = settings_lang.parent_id WHERE settings_lang.lang = 'en';` I want to make sure this isn't a bug. – cwgem Mar 07 '13 at 17:44
  • @cwgem query returns record with id=1 and key='site_title' – A. Martyn Mar 07 '13 at 17:52
  • @A.Martyn I'm fairly lost on this unfortunately... I would recommend taking this to [sqlalchemy support](http://www.sqlalchemy.org/support.html), most likely IRC where you can go back and forth with people, giving them a link to this page so they can see where you're at so far. – cwgem Mar 07 '13 at 17:59
  • 1
    Seems that I just accidentally solved my problem. The following query gives me what I want: `db.session.query(Settings.key, Lang.value).join(Settings.lang).filter(Lang.lang=='en')` – A. Martyn Mar 07 '13 at 18:12

0 Answers0