Henri
Another question. Is it possible to use views with PonyORM?
stsouko
Yes
stsouko
But tricky
stsouko
db.execute(f'ALTER TABLE "{schema}"."Reaction" RENAME TO "ReactionRecord"') db.execute(f'CREATE VIEW "{schema}"."Reaction" AS SELECT id, NULL::bytea as structure ' f'FROM "{schema}"."ReactionRecord"')
stsouko
For example
stsouko
After generate mapping
stsouko
Reaction is pony entity
Henri
Thanks! Will try wenn I'm back.
Anonymous
Hi there, I have a question about Pony ORM that's quite long. Is this the best place to ask it?
Alexander
Hi, sure
Anonymous
I am using two tables Member and User. A Member has a composite primary key User, Guild. I also have two repository classes for doing CRUD operation on both tables. Since a Member depends on the user to exist, every time I want to create a Member I have to create a User if it does not exist. However, when I try to do this I get an IntegrityError saying that the foreign key does not exist.
Jim
Show some code
Anonymous
It's quite a large amount of snippets but I will try to put them together.
Alexander
You can put it on pastebin.com and place reference here
Anonymous
https://pastebin.com/pY9YBwfk
Anonymous
I shortened some things to keep it shorter
Alexander
Is DBMember the same class as Member?
> A Member has a composite primary key User, Guild
I don't see it in code
DBMember(
user=member.id,
guild=member.guild.id
)
Should member.id really be assigned to DBMember.user? It looks strange
Anonymous
Even if I return the user I just created in the database and assign that to the DBMember the problem still persists.
Anonymous
I left the last line with the primary key out, but its there
Alexander
What is the DBMember class and how it is different from Member?
Anonymous
Member is a class from a different library, so since I created an entity with the same name, I named it DBMember
Anonymous
And I import it under an alias
Alexander
Even if I return the user I just created in the database and assign that to the DBMember the problem still persists.
Then maybe the problem is with a guild?
Matthew
is User / DBUser the same?
Matthew
You are trying to store a Member in the database, not a DBMember
Matthew
I think
Matthew
from your error message
Anonymous
@metaprogrammer No, the guilds are stored in the database without any problems
Alexander
But do you have a guild with specified id?
Anonymous
yes, it is created before the DBMember is stored
Anonymous
@matthewrobertbell No, those are not the same
Anonymous
Only DBMember is relevant
Alexander
It is better not to directly assign id to attribute, but assign object instead (as it was intended)
db_member = DBMember(
user=member.id,
guild=member.guild.id
)
change to:
user = User[member.id]
guild = Guild[member.guild.id]
db_member = DBMember(
user=user,
guild=guild
)
Then you be sure that user and guild actually exist in the database
Anonymous
The error still persists after this change unfortunately
Alexander
Is it possible that you created database, then changed models, but the database still contains obsolete foreign keys?
Anonymous
No, since the problem even persist if I clear my entire database and run this code
Anonymous
It's an SQLite file and I just clear it, but that does not help
Alexander
You can specify @db_session(sql_debug=True) and check resulted SQL, maybe it highlights some problem
Anonymous
Well it may get more difficult after this
Anonymous
the function that stores the user does not print anything
Alexander
Instead of using sql_debug as option of @db_session, you can set it as:
sql_debug(True)
It may be easier if you want to see output from multiple finctions
Alexander
By the way, it is not necessary to explicitly call commit(), it will be performed automatically upon exit from @db_session
Anonymous
It still does not log anything
Anonymous
What prints tell me is that the database object already exists
Anonymous
But the only place a user can be stored is in this one function
Alexander
Are you using logging module?
Anonymous
Not in that one file
Anonymous
But in other parts of the project yes
Anonymous
it actually does log something, namely that it selects the User because I entered a manual print statement
Anonymous
And if I do print(User[<id>]) it gives me a result that should not exist
Alexander
If you have logger configured, pony uses it instead of writing to stdout.
Pony uses INFO log level for SQL queries
But the root logger of logging module by default has WARNING level, this may be the reason you don't see any messages
Anonymous
I do see messages, just not in one function
Alexander
Hi! At this moment Pony doesn't allow to add conditions to ON clause.
As a workaround, you can make two queries:
- The first query to list all events
- The second query to get all event-ticket pairs for specific user:
all_events = Event.select(lambda e: e.date > date.today())
tickets_for_user = Ticket.select(lambda t: t.person == specific_person and t.event.date > date.today())
And then you can combine query results in memory, I think it should not be slower than a single left join query
Alexander
tickets_by_event = defaultdict(list)
for ticket in tickets_for_user:
tickets_by_event[ticket.event].append(ticket)
combined_results = [(event, tickets_by_event[event]) for event in all_events]
Nikolay
Hi! At this moment Pony doesn't allow to add conditions to ON clause.
As a workaround, you can make two queries:
- The first query to list all events
- The second query to get all event-ticket pairs for specific user:
all_events = Event.select(lambda e: e.date > date.today())
tickets_for_user = Ticket.select(lambda t: t.person == specific_person and t.event.date > date.today())
And then you can combine query results in memory, I think it should not be slower than a single left join query
It can easily be slower because there might a lot of ticket-user pairs for a given user. I skipped that part in the original question to keep it short (perhaps that wasn't such a great idea), but the event list is going to be filtered, e.g. to only show "new" events. And theoretically there could be thousands of event not included in the list and the user could have a ticket for each of them. Left join can utilize an index to get a single ticket per event, but in memory approach can't do that.
In my particular case the filtered event list is going to be very short most of the time, so I settled for N+1 selects.
Nikolay
I guess ideally one day one of the approaches that results in SQL error will result in a left join with extra ON conditions instead. Though a completely new syntax is fine too, I like the ability to build queries step by step with .filter and it would be nice to have that with left joins too.
Nikolay
It can easily be slower because there might a lot of ticket-user pairs for a given user. I skipped that part in the original question to keep it short (perhaps that wasn't such a great idea), but the event list is going to be filtered, e.g. to only show "new" events. And theoretically there could be thousands of event not included in the list and the user could have a ticket for each of them. Left join can utilize an index to get a single ticket per event, but in memory approach can't do that.
In my particular case the filtered event list is going to be very short most of the time, so I settled for N+1 selects.
I know about ponys many-to-many approach, it would probably work here too, but doing it by hand is awkward
Nikolay
Well, I didn't actually try to do it by hand
Alexander
I agree that adding support of arbitrary conditions in left join ON clause is a good idea, I have it in my plans
Nikolay
I imagine it could look like select((p, p.children.filter(some filter)) for p in ...), or perhaps an arbitrary sub-select for child in p.children. But I have no clue about how exactly pony decompiles generators and what is possible there
Alexander
Saying that, I think the query I suggested will utilize index just as fine:
tickets_for_user = Ticket.select(lambda t: t.person == specific_person and t.event.date > date.today())
In this query I added an example filtering condition for event which check date, but you can add any other conditions as well. As a result, the query will take into account only "new" events
Another options is to filter "new" events with the first query, and then use that list of event ids for second query:
new_events = Event.select(lambda e: e.date > date.today())
new_event_ids = [e.id for d in new_events]
tickets_for_user = Ticket.select(lambda t: t.person == specific_person and t.event.id in new_event_ids)
Nikolay
Saying that, I think the query I suggested will utilize index just as fine:
tickets_for_user = Ticket.select(lambda t: t.person == specific_person and t.event.date > date.today())
In this query I added an example filtering condition for event which check date, but you can add any other conditions as well. As a result, the query will take into account only "new" events
Another options is to filter "new" events with the first query, and then use that list of event ids for second query:
new_events = Event.select(lambda e: e.date > date.today())
new_event_ids = [e.id for d in new_events]
tickets_for_user = Ticket.select(lambda t: t.person == specific_person and t.event.id in new_event_ids)
I was talking about event-user index on tickets. The second approach does use it and if iirc pony uses this to fetch many-to-many relations.
Nikolay
I was wondering doesn't that break of lists longer than 1000? Some DBs have a limit of 1000 items in an IN clause
Nikolay
I was talking about event-user index on tickets. The second approach does use it and if iirc pony uses this to fetch many-to-many relations.
Nvm, i'm blind, they both use the index
Nikolay
I guess if one considers joining in memory a non-issue, the first approach is almost exactly the same as a left join. I personally not fond of it because I'd rather not have extra loops in python and let DB do them.
Nikolay
Or rarther "I'd rather not write the loops myself"? I guess ORM still has to loop pretty much for same amount to parse the result
Nikolay
Is it not possible to order by B if A else C with pony? I expected it to generate a case-when-else clause but got decompile errors instead. Perhaps there's a different syntax for this?
Nikolay
I'm not posting the code or trace back because I expect the answer to be "not possible", but tell me if I should.
Jim
Use property i think
Nikolay
Use property i think
If that actually works I'll need to make a property for every single ordering. Which i guess is not that many but I feel like it doesn't belong in the model anyway.
Nikolay
I doubt ii will work though, I don't see how it would be different from putting a ternary op directly into order_by
Jim
https://docs.ponyorm.org/entities.html#hybrid-methods-and-properties ?
Nikolay
"Important that hybrids and properties should contain single line return statement."
Nikolay
Not like i can put a full if-else there, can i?
Jim
Return bla if true else blablaa
Nikolay
So it's still going to use ternary operator
Jim
Yes