How to populate one table from database using a form which is based on other table? All in one template


I am not sure if my question is understandable so I will try to explain it here a bit more.

I have a user cpm on my front page. My goal is to have a list of products under it, from which a user can choose products that he consumed. The moment he chooses it, it automatically subtracts it from his cpm.

I don’t really know how to:

  1. Create a list based on ‘food table’ from which a user can choose products which will be saved in a database and which will populate other user’s table .
  2. Do it all on this page without redirecting.
  3. How to make it all work with database.

I already have a food table from which I should probably extract products and populate other table in database which holds those products chosen by user which is probably linked to user’s table:

 schema "foods" do
    field :calories, :integer
    field :name, :string


  @doc false
  def changeset(food, attrs) do
    |> cast(attrs, [:name, :calories])
    |> validate_required([:name, :calories])

Moreover, I guess it should also hold a specific hour at which a user did choose a product so to reset his cpm after 24 hours.

I am learning by myself and I don’t know how to link it all together. I will be grateful for your guides.

Best wishes, and sorry for a long post.

Okay, there are a few things to this.

You don’t need to store CPM in the database as it could be calculated dynamically. To calculate it, you have to associate a user with a food and a date at which the user consumed the food. You need three tables in the database - users, foods and users_foods. The third table users_foods is the association which is resolving a many to many relationship of users and foods. A type of food can be consumed by many users at a specific date and a user can consume many types of food at a specific date.

After you complete your data design, you can create a food - user association whenever you click on a food. For the CPM value you just subtract the sum of all the associated foods for today from the maximum allowed value.

I hope this helps.

1 Like

Thanks for the reply!

I am already calculating CPM dynamically. About my tables, I already have a table for users:

schema "users" do
    field :name, :string
    field :username, :string
    field :password, :string, virtual: true
    field :password_hash, :string


for their personal data:

 schema "physical_profile" do
    field :age, :integer
    field :height, :integer
    field :weight, :integer
    field :sex, :string
    field :physical_activity, :string
    belongs_to :user, Calorie.Accounts.User


for foods:

 schema "foods" do
    field :calories, :integer
    field :name, :string


Now I understand that I need to create one more table users_foods, which will link users and foods and it will have a many to many relationship.

Do you have any suggestions how to create a list of foods from which a user can choose what he has eaten? Should it be a form with a changeset coming from foods? Can I on it’s bases populate newly created users_foods table?