By CTE queries I mean this: http://stackoverflow.com/questions/25896706/recursive-cte-query I think you can use them in Pony using raw queries

The graphical part of diagram editor uses JavaScript, the code generation part uses Python

Congrats! It looks awesome!

plain js?

Curently it uses KnockoutJS for templates and Raphael for SVG generation. Right now we rewriting it in ReactJS+Redux+FlowType

I just added the State entity. So StickerMessage is missing `in_state`. Sadly I cannot just drop the StickerMessage table.

I just added the State entity. So StickerMessage is missing `in_state`. Sadly I cannot just drop the StickerMessage table.

Can I fairly easily add that missing field myself?

Sure. What database do you use?

User has a id = PrimaryKey(int)

Sure. What database do you use?

Postgres

This is first class customer service!

This is the schema: https://editor.ponyorm.com/user/luckydonald/Tags

This is first class customer service!

It really is. Thank you very much, btw.

alter table "stickermessage" add column "in_state" int references "user"("id");

alter table "stickermessage" add column "in_state" int references "user"("id");

You are awesome!

Thanks :)

I intend to read your project source. Any recommended reading before that?

I intend to read your project source. Any recommended reading before that?

A bottle of Jack Daniels? 😄

Do you mean sources of PonyORM?

yes sir

A bottle of Jack Daniels? 😄

jajajajaja

A bottle of Jack Daniels? 😄

Maybe documentation?

You may be shocked while reading PonyORM sources, because currently they are not following PEP8 standard

I used my own convention, which was convenient to me

We plan to format it according to PEP8 before release 1.0

Okey, now this is a tough one. Context: One of my telegram bots, @StickerTagBot. This is the inline query search. I have StickerMessages representing a sticker someone sent. I want to filter them by user_id, and search the query_text. Now the seach query should look in the .sticker.emoji field and the .string of any of the Tags (Sticker.tags, which is StickerMessage.sticker.tags[].string). I got that working so far: sticker_messages = orm.select( st for st in StickerMessage if st.user.id == user_id for t in st.sticker.tags if query_text.lower() in t.string.lower() or query_text == st.sticker.emoji ).order_by(orm.desc(StickerMessage.date)).limit(50, offset=offset) Now sticker_messages is a list of StickerMessage rows. How can I make StickerMessage.sticker.file_id distinct here? In other words, modify the query, so I get each StickerMessage.sticker.file_id only once (the newest, StickerMessage.date)? This is my workaround: known_ids = [] # is limited to 50 anyway. for sticker in sticker_messages: file_id = sticker.sticker.file_id if file_id in known_ids: continue # end if known_ids.append(file_id) # do something with file_id

Oh, and offset is a for pagination, it will be increased by 50, and that function called later again.

Pony should add distinct by default if the result contains repeatable data. So, if you write: select(st.sticker.file_id for st in StickerMessage if ...) You should get distinct values

The Tag in question. SELECT * FROM tag WHERE user_id = 10717954 AND string LIKE '%New Sticker%'

StickerMessage table. The sticker was sent 3 times. SELECT * FROM stickermessage WHERE user_id = 10717954 AND sticker_file_id = 'BQADAgADfAkAAgKLowABJ0cw22UFO-YC'

Sorry. My assumption was wrong. That it is in there 3 times because of 3 tags, but actually because it was sent already 3 times.

Inline query, 3 time the same sticker.

If you select st.sticker.file_id instead of st you should see it once, I think

Huh. Can it be that simple? I'll try.

...but because of pagination you can get the same file_id on different pages. I'm not sure it is good to use pagination in this case.

I have to, I can only send 50 entries at once.

NotImplementedError: Ordering by attributes is limited to queries which return simple list of objects. Try use other forms of ordering (by tuple element numbers or by full-blown lambda expr).

Hmm...

.order_by(lambda: orm.desc(st.date))

The IDE can warn you that st variable is not found, but don't worry about that

The IDE can warn you that st variable is not found, but don't worry about that

Indeed, it does :D

pony.orm.dbapiprovider.ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 7: ORDER BY "st"."date" DESC

Uh, this is really hard. I'm sorry...

Ok, then PostgreSQL does not allow to combine ORDER BY x and SELECT DISTINCT y in the same query. Then maybe you can do DISTINCT part in Python: messages = select(st for st in StickerMessage if ...).order_by(...) file_ids = set(m.sticker.file_id for m in messages)

Hm. Postgres SQL has a SELECT DISCTINCT(column1), column2 FROM table

Because with the one above, I'll have to load all id's into memory first, and do cutting it to 50 for pagination later.

Let me try to come up with a raw sql query.

> SELECT DISCTINCT(column1), column2 FROM table what does it do?

> SELECT DISCTINCT(column1), column2 FROM table what does it do?

http://www.postgresqltutorial.com/postgresql-select-distinct/

You can specify one (or possible multible?) of the colums you select to be distinct

http://www.postgresqltutorial.com/postgresql-select-distinct/

Another link, to the official Docs: https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT

Sorry. SELECT DISCTINCT(column1) column1, column2 FROM table SELECT DISCTINCT(column1) * FROM table

I'm not sure it can be useful in your case

I think the correct query is pairs = select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if st.user.id == user_id for t in st.sticker.tags if query_text.lower() in t.string.lower() or query_text == st.sticker.emoji ).order_by(-2, 1).limit(50, offset=offset) file_ids = [ file_id for file_id, date in pairs ]

This is looking promissing, yeah

Yep. Thats it. Wonderfully working! Can I write .order_by(desc(2), 1) instead of .order_by(-2, 1)?

Does desc(2) make a -2?

I think yes

Inline query, 3 time the same sticker.

Awesome. Now it works.

Cool

For completeness, here a comparison of before and after :D

What is the difference between Optional and Nullable?

Megaquestion

One of things that I can't understand

Yay, I'm not alone!

Moreover, behaviour is different depending on the data type of the column!!

The difference is for string attributes, which by default are optional, but not nullable. Otherwise it may be very annoying to search for empty string values, half of which is '' and another half is NULL

And what is the difference for nonstrings ?

For non-strings, all Optional attributes are always nullable

Okey, I was thinking Optional means you can leave it out when createing the object or something

so like blank in django orm?

Yes

Okey, I was thinking Optional means you can leave it out when createing the object or something

Yes, but also you can leave out Required attributes which have `default` option specified

We described it here https://docs.ponyorm.com/api_reference.html?highlight=nullable#optional-string-attributes

Does it explain enough?

I think I've found another bug but want to confirm here: If I have an entity with a volatile attribute, and I perform a select, then an update, then a to_dict() call, I get a KeyError inside a pony library function, A basic example is here: https://gist.github.com/chrisshroba/deb3bbff34261c88e1572a19b06123c3

Thanks, fixed