To that table

Maybe that?

Maybe you can see more info, which validate method it is exactly

And the same for parse_value

Maybe it is JSON, for example

Thanks I'll check that

I have no json

The most uncommon I have its varbinary

Hi, I’m trying to use an Entity like the class below, and looks like te orm tries to use a PrimaryKey id that I have not defined. I’m using composite_key instead of PrimaryKey due to some attributes of that key are optional. What can I do to fix this issue? class Player(db.Entity): _table_ = 'player' player_id = orm.Required(str) team_id = orm.Optional(str) league_id = orm.Optional(str) sport_id = orm.Required(str) # other attrs orm.composite_key(player_id, team_id, league_id, sport_id) Thrown exception pony.orm.dbapiprovider.OperationalError: (1054, "Unknown column 'player.id' in 'field list'")

composite_key is not primary key. If you don't specify some primary key, Pony will create id int autoincrement. If you want to have primary key as composite, you can orm.PrimaryKey(attr1, attr2, attr3) Also notice that using Optional values in primary key is not the best choice.

if I use a PrimaryKey with a Optional attr it fails with the message Optional attribute cannot be part of primary key

Yes ofc

so.. there's no way with pony to have a denormalized table with a unique key with multiple attributes that could be optional?

What does "Optional" means here? Is maybe nullable possible?

Relational model assumes that all primary key elements are required

As a workaround you can have automatically generated id primary key and secondary key with optional elements

yep, Required + nullable could work?

In most sane SQL databases primary key columns cannot be nullable

https://stackoverflow.com/questions/386040/whats-wrong-with-nullable-columns-in-composite-primary-keys

ok, I will check the suggestions. Thanks guys.

Is there a way to use native database enum type (https://dev.mysql.com/doc/refman/8.0/en/enum.html) with Pony?

Is there a way to use native database enum type (https://dev.mysql.com/doc/refman/8.0/en/enum.html) with Pony?

Hi, at this moment Pony does not support native database enum type yet

I have a doubt,I have this model and this table on the database(postgres)

This is the dable

Why pony says the name field is required?

https://docs.ponyorm.org/api_reference.html#optional-string-attributes

Makes sense, thank you

https://docs.ponyorm.org/api_reference.html#optional-string-attributes

Wouldn't that break unique=true?

In Pony unique optional strings are nullable by default

Also, the editor, when clicking "Edit" on a previously created snapshot turns all white again. #bug

What is the best way to store order in a SQL database? Like I have [{"id": "B", "something": "foo"}, {"id": "A", "something": "batz"}] And store those as | ID | something | deleted | ———————————————————————————— | C | foo | true | | A | batz | false | | B | foo | false |

Probably the simplest way is to have position column with int value

And then make that one unique, together with something identifying that array? xyz = [{"id": "B", "something": "foo"}, {"id": "A", "something": "batz"}] uvw = [{"id": "D", "something": "yeh"}, {"id": "E", "something": "hi"}] With a table like | ID | Array | something | deleted | position | ——————————————————————————————————————————————— | C |  xyz | foo | true | NULL | | A | xyz | batz | false | 2 | | B | xyz | foo | false | 1 | | D | uvw | yeh | false | 1 | | E | uvw | hi | false | 2 |

I'm not sure making it unique in the database is a good idea, as it makes reordering harder. Also, you want to ignore deleted records

I would set the deleted ones to NULL, so they shouldn't be included in the unique constraint. Like here I would make (Array, position) unique.

I think you can. But If you want to do operations like "move an item from position 7 to position 3" it may be harder to do with enforced uniqueness

Yeah, I'd have to make sure to change each of those before the @orm.db_session is over, right?

When Pony performs an update of position to some value it should not be another row with the same combination of (Array, position) You can perform operations like (move item from pos 7 to pos 3) in three steps: 1) assign all items with positions 3-7 temporary negative numbers -3 ... -7 2) perform flush() 3) assign correct new values This way you will never have two rows with the same positions when performing an update

When Pony performs an update of position to some value it should not be another row with the same combination of (Array, position) You can perform operations like (move item from pos 7 to pos 3) in three steps: 1) assign all items with positions 3-7 temporary negative numbers -3 ... -7 2) perform flush() 3) assign correct new values This way you will never have two rows with the same positions when performing an update

Wow, that is a genius way!

I hope I will only be storing them, - so I am given an array, - query the fitting ones from the database, - set the id's of those negative (`.position *= -1`), - apply the new postition (positive) as I iterate through the xyz array and find them in my query result by the same`ID`, - and mark every one in the database which is not in my current list as delete=True and position=None.

So position has to start with 1, instead of 0 as there is no -0.

I think it can work this way

How do i filter in List of JSON structure using filter method

Here is my entity defination

metadata is the single JSON record

I may have multiple rows like this

Now I need search if any of the name matches test

@metaprogrammer sorry to bother request your support

Or anyone please help me out

So, metadata is array on top-level?

and you want to find if any of array items has specific value for key name

What database do you use?

MySQL

So, metadata is array on top-level?

Yes

and you want to find if any of array items has specific value for key name

Yes

You can try something like this (not tested): name_value = 'test' select(s for s in Service if raw_sql(""" json_contains( json_extract(s.metadata, '$$[*].name'), $name_value, '$$' ) """))

try to experiment with json_contains and json_extract https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Note that I use $$ instead of $ inside JSON path expressions, as $ has special meaning for Pony, and single $ should be replaced with $$ in queries

How do I use In filter

With lambda

To add more I traverse from from one to many relationship

name_value = 'test' query = query.filter(lambda s: raw_sql(""" json_contains( json_extract(s.metadata, '$$[*].name'), $name_value, '$$' ) """))

If you use relationship only for querying this JSON column, you need to add some fake condition to force join

name_value = 'test' Will something like this work query = query.filter(lambda s: raw_sql(""" json_contains( json_extract(s.media.semo.metadata, '$$[*].name'), $name_value, '$$' ) """))

But it is not entirely clear what you mean by traversing 1:m relationship here

name_value = 'test' Will something like this work query = query.filter(lambda s: raw_sql(""" json_contains( json_extract(s.media.semo.metadata, '$$[*].name'), $name_value, '$$' ) """))

no, inside raw_sql you write SQL code, and the first argument of json_extract should be in form table_alias.column_name

so semo table has some alias inside a query, and you need to use this alias

You can write some simple query like query = query.filter(lambda s: s.media.semo.metadata is not None) and look at its SQL to see semo table alias

I need to go offline, can answer additional questions tomorrow

I start with Job entity based on status like success,

From here I need to job.media.service_media.metadata

And metadata is an array

Of JSON structure

and service_media is set

Yes

From here I need to job.media.service_media.metadata

From this relationship I need to search if any of the key name has value

Is order of items in metadata important?

(not for this query, but in general)