How to use one schema for two tables?

Hey guys, new elixir/phoenix user here. I’m making a food recipe site to learn the language and framework and things have been going alright until I got to this point. I’ve managed to get it working how I want but not in a clean way.

So my current flow is user wants to create a recipe. So they make a recipe “draft” that isn’t publicly published and can keep around for a while. Then when they submit that it turns into a regular recipe. So create -> recipe draft -> submit -> recipe.

I though I could simply use the same schema but I didn’t know how to use a different table with it. I learned I could use Repo.put_meta(source: “table_name”) when I was inserting, but couldn’t find anything as simple when retrieving. So I made a separate identical schema with a different table source, but it was unmanageable converting between the two (trying to add a RecipeDraft to the Recipe table and vice versa).

So I went back to the single schema and figured out a way to get the data from the table I wanted in a pretty verbose way. Like this:

def get_recipe!(id, source \\ "recipes"), do:!(from r in source, where: == ^id, select: %Recipe{id:, name:, description: r.description,
      ingredients: r.ingredients, directions: r.directions, notes: r.notes, nutrition_facts: r.nutrition_facts,
      tags: r.tags, cook_time: r.cook_time, prep_time: r.prep_time, servings: r.servings, images: r.images, user_id: r.user_id,
      inserted_at: r.inserted_at, updated_at: r.updated_at})
    |> Repo.preload(:user)
    |> Repo.preload(:reviews)

I’m using postgreSQL (newish to that too) so I can’t do select: r because it gives me an error that I can’t select all fields without a schema.

So my actual question is… is there an easy way to share one schema with two tables? Or at least an easier/cleaner way to pull a schema from a different table other than the source of the schema?


Hello and welcome,

You might use only one Recipe table, with a status field draft/published…

You might use a published query, to list published recipes.

And publishing a recipe would only be a matter of toggling the status field :slight_smile:

I did think about doing that but I was imagining that keeping two separate tables would help keep my queries a little cleaner. With two tables I could just reference the source table and use the same queries instead of having to filter based on the draft status (which really isn’t much).

I will probably go with this method as the my current queries are quite ugly. I think filtering on the status would be cleaner at this point. Thanks!

You might want to look at this part of the ecto docs to learn how to use a single schema on multiple tables: