Best-practise question - follow and friends posts in ecto

Questions / Help
,
1

Hi everyone.
My name is Audun and i’m coming from a team that uses the .NET stack heavily in our development and products.
I got my eyes on Elixir and Phoenix half a year ago and have been playing around with it.
I love it, i love the design and i love the thought behind it.
As my background is OOP for many years i do not find Elixir that dificult to get started with, but i do belive i use quite a longer time to actually understand some of the design patterns and remembering them. But even so, i want to get Elixir into my organization as the primary tool for the different projects we have as long as it fits the need.

I do however have a question. One of my pet project that i have to learn is kinda “twitter” clone. One of the misstakes i did when starting was to find Ecto difficult to understand, and to keep moving forward i started using Postgresql functions since i’m more familiar with that. Now, i have a lot of functions and its quite messy to add new stuff to existing functions and to know whats actually on the server. I want to try the Ecto way. So here is the “thought” out design.
In my twitter clone i have a table called Tweets.

CREATE TABLE public.tweets
id integer NOT NULL
slug character varying(500)
user_id integer
geom geography
CONSTRAINT tweets_user_id_fkey FOREIGN KEY(user_id) REFERENCES public.users(id) MATCH SIMPLE

The user table looks like:

CREATE TABLE public.users
id integer NOT NULL
username character varying(120)
password_hash character varying(250)

A user can be friends with another user:

CREATE TABLE public.friendships
id integer NOT NULL
user_a_id integer
user_b_id integer
accepted boolean
CONSTRAINTS friendships_user_a_id_fkey FOREIGN KEY(user_a_id) REFERENCES public.users(id) MATCH SIMPLE
CONSTRAINTS friendships_user_b_id_fkey FOREIGN KEY(user_b_id) REFERENCES public.users(id) MATCH SIMPLE

A user can also follow a topic

CREATE TABLE public.follows
id integer NOT NULL
user_id integer
topic_id integer
CONSTRAINT follows_topic_id_fkey FOREIGN KEY(topic_id) REFERENCES public.topics(id) MATCH SIMPLE
CONSTRAINT follows_user_id_fkey FOREIGN KEY(user_id) REFERENCES public.users(id) MATCH SIMPLE

So a user can have a friend and a user can follow a topic. When fetching tweets i need to do the following:

a) Find the users friends and fetch tweets.
b) Find the topic the user subscribes to and get tweets
c) Find the tweets the user have added
d) Make sure no duplicates are fetched in the return of A and B.
e) Calculate the distance from where the user are compared to the tweet (see geom on tweet)

I know that this might be overly complicated, but i do thing its as simple as i can get it.
But if i was to use ecto is there a better “design” in the db for this, and if not can this be done in Ecto?

Thanks for reading, and just as i note i have simplified the tables above to only contain the needed fields. Hope this is easy to read and makes sense. Be kind as this is my first post in this forum, and i’m a newbie with Elixir in general.

Thanks

1 Like
2

Ecto.Query might be your friend for custom queries and LINQ style db interaction .NET folks should be familiar with.

Ecto assoc would be for fk and table links four you.

3

Hey!

With phoenix generate migrations is very easy, here a usefull link

Also show how to generate tables with Ecto ^^:

Ecto has a linq like sintax to query on database as show in cheatsheet, more examples in

https://hexdocs.pm/ecto/Ecto.Query.html

To make the relationship work, the Ecto docs has amazing references and explanation for it

https://hexdocs.pm/ecto/Ecto.Schema.html

For validations Ecto uses changesets, you can see it on the docs too

Is pretty easy work with ecto, if you have any question always check the docs before everything, almost all the time the answer is there ^^

4

Does the tweets belongs to topics? If so You might have missed a topic_id field inside tweets.

Do You need help with the migrations? Or with the Schema? Or with the queries?

For me, designing the schema has always been the toughest part.

1 Like
5

Hi and thanks for all the replies.
Yea, i missed the topic_id but its a many to many relation. Typed the create script manually so missed that.

I should have added what i’ve tried and what i struggle with. Designing the schema and doing queries (linq way) with ecto is find, but when the queries become more advanced with multiple joins that relates to each other and preloading data i’m a little stuck.

Since my Postgresql function consist of 3 selectes for each of the cases above with a UNION to combine them i could have done a Task.Wait for all the tree queries and removed duplicates in Elixir, but i have not tested this approace as i just read about the Task functionallity of Elixir.
Pseudo code ( my heads path to the result in look alike ecto… I also do not match the query below with the scema above)

from u in User,
join: friends in assoc(u, :friends), // A user can have many friends
join: follows in assoc(u, :follows), // A user can follow many topics
join: tweets in Tweet,
where: u == ^user.id, (friends.user_a_id == ^user.id or friends.user_b_id == ^user.id), user.follows.Any(follows.id),
preload: [friends: [user_x_id: :tweets], follows: [topic: :tweets], u: [:tweets, :follows] ]

This should result in me getting all tweets from my friends, the topic i follow and tweets i have added to the system.
I guess breaking them up into tree pieces would be a simple solution, but is this possible to do in one?

Sorry if its a little messy.

1 Like
6

I tried to reproduce your example, I came up with a user schema like this…

  schema "users" do
    field :name, :string
    field :password, :string, virtual: true
    field :password_digest, :string
    
    many_to_many :friends, User,
      join_through: "friendships",
      join_keys: [friend_a_id: :id, friend_b_id: :id]
    
    many_to_many :reverse_friends, User,
      join_through: "friendships",
      join_keys: [friend_b_id: :id, friend_a_id: :id]
    
    many_to_many :topics, Topic,
      join_through: "follows"
    
    has_many :tweets, Tweet
    
    timestamps()
end

One note is that friendship can be tricky. To get all the friends I am with, I need to query for both direct and reverse friends. Maybe someone can show the way to make this friends association more elegant.

So You can query for direct tweets

q = from t in Tweet, where: t.user_id == ^the_user_id_you_want

Here it is not complete, but You can see the idea

q = from t in Tweet, where: t.user_id in [^friend_1_id, ^friend_2_id…]

I saw here https://hexdocs.pm/ecto/Ecto.Query.html#select/3 that You can use or_where, but I did not yet made the one query you are looking for :slight_smile:

So, in theory (not tested), You shoud be able to do

q = from t in Tweet, where: t.user_id == ^the_user_id_you_want,
or_where: t.user_id in [^friend_1_id, ^friend_2_id…],
or_where: t.topic_id in [^topic_that_user_follows_id, …]

Then, use a distinct: true, an order by date… and whatever You want to do

Please show your schema, so we can compare our way of doing this.

7

To elaborate on my previous post, I can select a user (with id = 3)

iex> q = from u in User, select: u, preload: [:friends, :reverse_friends, :topics, :tweets], where: u.id == ^3
iex> u = q |> Repo.one

And now I can collect friends id and topics id with

iex> friends_ids = (u.friends |> Enum.map(& &1.id)) ++ (u.reverse_friends |> Enum.map(& &1.id))
iex> topics_ids = u.topics |> Enum.map(& &1.id)

I now have enough information to inject into the tweets query

iex> q = from t in Tweet, where: t.user_id in ^[u.id|friends_ids], or_where: t.topic_id in ^topics_ids
iex> q |> Repo.all
11:53:29.138 [debug] QUERY OK source="tweets" db=1.8ms
SELECT t0."id", t0."slug", t0."user_id", t0."topic_id", t0."inserted_at", t0."updated_at" FROM "tweets" AS t0 WHERE ((t0."user_id" = ANY($1))) OR (t0."topic_id" = ANY($2)) [[3, 4], [7, 8, 9]]
8

Hi and thanks for your examples @kokolegorille it helped a lot.
My current scheme looks like this:

schema "users" do
field :name, :string, size: 100
field :profile_picture, :string
field :email, :string, size: 120
field :phone, :string, size: 20
field :bio, :string
field :username, :string, size: 40
field :password_hash, :string, size: 100
field :password, :string, virtual: true

has_many :tweets, Pb.Tweets
has_many :likes, Pb.Like
has_many :devices, Pb.Device
many_to_many :follows, Pb.Topic, join_through: "follows"

Since i’ve done most of the relations in the Postgresql function (including insert) i do not have the topics etc relations as your example above.

What concerns me with this echo approach is speed. The above will generate multiple round trips, but for all i know it can be just as fast.

I’ll take the examples and experiment to see if i can get it to fit my need and educate my ecto knowledge. I’ll reply with my findings :-). Thanks so much @kokolegorille

9

The relation You put into your schema does not depend on the way You generate the tables.

While it is true I generated a friendships relation like this…

defmodule Twit.Repo.Migrations.CreateFriendshipsTable do
  use Ecto.Migration

  def change do
    create table(:friendships, primary_key: false) do
      add :friend_a_id, references(:users)
      add :friend_b_id, references(:users)
    end
  end
end

I somehow oversimplified the friendships table, You cannot do it because You also have an accepted attribute. But You can define an intermediate friendships schema. And I see no reason why You could not define your friends relation with has_many Friendships, has_many Friends through: …

To me, migration is just a way to generate tables structures, while schema is intended to describe fields and relation.

And it get even clearer to me when I started to look at absinthe/graphql, where You have to define nodes, attributes, and edges

Concerning round trip… I am just calling Repo twice, one for loading user, one for loading tweets.