Failing to use `union` and also preload or select associated data

Correction. This should have been: ... order_by: fragment("updated_at DESC"), ...

I got most of the way there with.

def latest_edited_or_published_by_author(%User{} = user, amount \\ 8) do
  story_query =
    from s in Story,
      join: a in assoc(s, :authors),
      where: a.id == ^user.id,
      group_by: s.id,
      select: %{
        id: s.id,
        title: s.title,
        draft?: false,
        authors: fragment(
          "array_agg(jsonb_build_object('id', ?, 'username', ?, 'verified', ?))",
          a.id,
          a.username,
          a.verified
        )
      }

  story_draft_query =
    from sd in StoryDraft,
      join: a in assoc(sd, :authors),
      where: a.id == ^user.id,
      group_by: sd.id,
      select: %{
        id: sd.id,
        title: sd.title,
        draft?: true,
        authors: fragment(
          "array_agg(jsonb_build_object('id', ?, 'username', ?, 'verified', ?))",
          a.id,
          a.username,
          a.verified
        )
      }

  union_query =
    from q in story_query,
      union: ^story_draft_query,
      order_by: fragment("updated_at DESC"),
      limit: ^amount

  Repo.all(union_query)

end

This successfully puts author data in a list of maps, like is intended. The maps use strings as keys, not atoms, which is not ideal. But I believe I might be able to fix that somehow with type/2 from Ecto. Any ideas are very welcome, of course .

Or this .

I definitely prefer returning structs over maps.

Can you explain your decision to separate stories from drafts at the database level (assuming you’ve done so)? Since you’re obviously needing to mix the two together in some circumstances, that seems to me to be a good indication that things would be much easier if they were unified — I would likely use a nil “published at” timestamp to indicate “draft” state.

I initially wanted to store stories and story drafts in one database table. Using a draft_content field (as I have seen suggested in resources) did not make sense to me for my use case, compared to the alternatives.

Having a published_at field in the table seemed a nice option. But the user needs to be able to draft in parallel with having a story published. So, imagine first drafting a story, than publishing it, than drafting more, while other users can still read the published version of your story. Also, I wanted to make use of an Ecto one to one relationship between story and story draft. Using seperate tables in that case seemed a good fit.

Because the benefits of having a consistent data structure are greater than the disadvantage of sending more data from back to frontend? Minimizing data footprint was my main reason for not returning structs, but I have this uneasy suspicion that I am lacking the insight to make a better founded decision .

Oh, I see. To me that sounds like versioned content, and I would lean into that. There are a number of ways to model that, but something like stories would have a “published at” field and a “current version ID”, story versions table would have a “story ID” field

Very much the former. I would need to prove that my application saw noticeable benefit before I worried about the latter.

I’m all for the story/draft separation and do it myself.

One of the reasons is certainly as @coen.bakker mentioned which is to be able to edit a draft of a currently published story. @jswanner: amen to versioned content! It does add complexity that you might not need, though, and I’d argue it goes beyond that. A published story has attributes/associations that a draft does not. Comments and likes are the main ones. If we separate these entities then we can avoid all the conditionals that creep into various functions. With one entity that might be published we always have to ask if it is. With two entities this goes away and I feel that is more powerful than it may seem.

On the other topic, I’ve been following along with these posts and didn’t have quite the wherewithal to respond this weekend, but I absolutely agree with @jswanner that you should just return structs unless you have a really good reason not to. Personal, I would not say that minimizing data over the wire is probably not a good reason—while I really sympathize with the sentiment, I think it’s almost certainly negligible in this scenario.

This definitely ties into other discussions going on about the difficulty of learning Phoenix, but if you really want to have have smaller slices of certain tables, then create new structs in different contexts that have a subset of the fields you need. You can also create table views at the db level but that is taking it even further.

Can you clarify the exact conditions you want to meet a little further please?
For example the structure seems to be:

Create new “Story” using many-to-many with users stored as a list?
Create new “StoryDraft” that stores the story_id and can be updated by any of the users? Presumably also stores the users as a list.
The above have independent updated_at values as they are in different tables

When you return the results, you are searching through the list of users, and only returning the latest entries for for both tables, regardless of which user last modified them.

Is this correct?

If so the most difficult part about this for me seems to be dealing with the list of authors which I assume are “users” with user id values. I don’t really touch joins or fragments that often, but I would try something along these lines

def recently_updated(user, limit \\ %{}) do
  query =
    from s in Story,
    join: sd in StoryDraft,
    on: fragment("? = ANY(?)", ^user.id, sd.users),
    where: s.id == ss.id,
    order_by: [desc: s.updated_at],
    limit: ^limit,
    distinct: true,
    preload: [:users]

  Repo.all(query)
end

Again, this will probably not work as is, and was written assuming you store the “authors” in a list as “users”

Also I changed the order_by to just normal elixir instead of a fragment and added distinct to stop you getting duplicate results if a story and draft are both within the limit. Not sure if its needed or not.

    order_by: [desc: s.updated_at],

If its a field in the database and a straight forward asc or desc you don’t need to use a fragement and can just write it like this

I added a preload of users under the assumption you have a field name “users” storing the user ID’s as a list. I don’t normally do this, so not sure if it works. Normally :user would preload a single user.

Hopefully something helps.