Hello,
I wanted to use soft delete for my project. I found a nice library called paranoid, it was working only with ecto 2, I managed to upgrade it, however the behavior of preloads are the default ones from Ecto. I was reading the source code from Ecto preload, however it has few to no commentaries and it is kind of hard to understand. The basic idea I thought of implementing is appending a query that checks for is_nil(deleted_at), however queries can be nested so this proves a challenge.
Currently the only way to achieve this is to make a query inside the preload, witch is not the perfect case, especially when preload is used in a query itself.
Oh hey, that paranoid library looks similar to how I handle ‘deletes’ in my database. I use a removed_at timestamp column (and an optional removed_data column to record who/how/what deleted it). Anytime I access any of my tables I just add an is_nil(table.removed_at) to it (and indexes only index the nil versions as well). I also don’t edit a row except to set a removed_at, I always remove a prior and add a new one for a mutation (my work likes to keep perfect records of past rows) in a transaction. I have a set of helpers on my MyServer.Repo that simplifies it even more (bulk mutations or deletes, etc…). It’s simple enough to do that I never thought of turning it into a standalone library.
Correct, you will absolutely require a preload query for it, either joined or externally. You can override your preload call on your *.Repo to add it automatically in ‘some’ cases though, but really you’ll need to use a query.
Preload is one of those things in Ecto I don’t really use, it’s not functional enough for patterns like this, so I either always just join manually or do another Repo call and merge them in-code (which also allows me to do postprocessing all in one step too). Plus it’s how I’m used to doing it, preload is still a very foreign concept to me and only seems half-built in Ecto in that I haven’t seen a way to force query changes like this without doing it manually everywhere… ^.^;
To be fair preloads are simple and they work well enough. When I started developing the project I was thinking to use custom queries for soft delete and relations mapping, but shortly I found that solution not readable and a lot of code added on top of it. Relations are a very nice and clean way to join multiple tables together with you care only about the actual content and not queries. This has a huge advantage when such nested things come into play. For example you have tables: roles, roles_mapping, acl, acl_mapping, such tables are basic when it comes for roles and you want to return a user acls (a user can have 1 or more roles and a role has 1 or multiple acls). With query this is a 5 line query with 4 joins that is not very nice looking when on other hand with relations and preloads this 1 line of code that you are sure it is correct (from point of view of query) and it is very readable and easy to debug.
For the simple cases, though I stated that it doesn’t work well for these patterns. ^.^
For my general tables (not all my design, 90% of them are pre-existing tables that go back decades in age) almost every-single-join requires matching multiple fields (not just one easy ‘id’ field or so), while also doing other checks such as making sure a certain field is null, or a certain field has a specific value ('A' as a string is common for the ‘active’ rows, like I said, old database…), or the way-too-common horrors of an incrementing field per row ‘type’ that you have to take only the highest value of, ignoring the older ones as they are the ‘older data’, and even more fun when all of these are mixed a dozen times. Preload’s current design fails very very horribly on these ‘Enterprise Style’ Oracle databases.
So if you know how to get preloads to work on such setups then I’d love to know, and these styles are common across multiple jobs I’ve had in the industry. I’m not sure if I’ve ever seen a database with such ‘simple’ joins in Production anywhere that wasn’t just someone’s hobby project or other trivial thing that’s less than a trillion rows across hundreds to thousands of tables.
To be fair I am just a beginner, not much experience in elixir and in enterprise projects also. It would be very nice to develop preloads to support such cases, maybe I’ll give it a try, but it is rather hard without any support from somebody who already has more experience.
In general a way to pass a detailed query to the join definitions with a way to pass in data from the parent that is more than just a single field (note, that parent may be across multiple tables, a lot of joins I do have to join across multiple tables to get the row of a single thing) would be the big start.
Not sure I’m allowed to, plus none really exist as singular queries (I do a TON of query building, to make sense of the original SQL, which had a bad habit to be about 2-10 pages long when printed out, and yes they gave them to me on paper… Joining over 10 tables is routine just to get a little drop of information…). Though I can detail any specific cases or styles of course.
If you have the time please do it. I think such information will be useful for multiple people improving ecto, even if I am unable to enhance the preload.
