Best practice for a user-likes system schema (json vs regular rows)

@pacmom You do realise you can get a count of rows, and not retrieve all matching “likes”? Just do something like $post->likes()->count(), and that’ll issue a SQL statement like SELECT COUNT(*) FROM likes WHERE post_id = ?.

You don’t want to be storing the users who “liked” something in a JSON object. That’s a horrible approach. You can’t do anything with it without first fetching the entire object, including writing. That’s just horribly inefficient. The JSON blob could possible outgrow the MySQL column length, you might exhaust PHP memory trying to read the object if you have a lot of likes, you can’t do anything like indexing—it’s just a really bad idea.

@martinbean sorry I wasn't clear: I'm not using this table for getting the total number of likes for a post, I'm keeping the total number in my posts-table as a simple integer, so that's not an issue.

I need this table WHEN I actually need the whole users who have liked. For example when I need to check if a user has already liked the post or not(to weather or not show the like button), I need to see if that user's id exists in the list. What makes me worried is that if I keep storing them as the #1 method, soon the number of rows in this table would increase to millions and maybe in years it increases to billions! Is that really cool with MySQL?

@pacmom Yes. That’s fine. MySQL is a mature database system. It can support millions and millions of records. Big companies use it. Just be sure to put a compound foreign key on the user_id and post_id columns so a user can’t “like” a post more than once.

Thank you so much @martinbean.

"Just be sure to put a compound foreign key on the user_id and post_id columns so a user can’t “like” a post more than once."

Could you please explain this part a little more? (or an example)

@pacmom If you place this in your migration file:

$table->primary(['user_id', 'post_id']);

It creates a primary key from the values of both the user_id and post_id, so that the same combination can’t exist more than once.

@martinbean this tip of yours changed the way I look at the database entirely! Using other primary keys, which means the database doesn't really have to search through all the records. I owe you big one.

Any idea what's all the buzz about Eloquent not supporting "composite keys"? Because I simply used: $like = Like::firstOrCreate(['user_id' => $user->id, 'post_id' => $post->id ]); and it seems to do the job. Am I missing something here?!

Hey, just to add to what @martinbean said. You should look into normalisation and database design, this way you will be able to understand and resolve problems like this in the future. In many cases, these problems come down to the lack of knowledge concerning advanced database design.

Hey @joshgallagher24 , Totally agree! Any source in mind?

@pacmom You would probably want to use Eloquent’s relation methods instead:

class User extends Authenticatable
{
    public function likes()
    {
        return $this->belongsToMany(Post::class, 'pivot_table_name', 'user_id', 'post_id');
    }
}

Auth::user()->likes()->attach($postId);

@martinbean I'm a little afraid of using relation in this case, as every time I select a user I assume it also creates a query to catch the likes(while I might don't need them. Am I being paranoid?

This is what I have implemented instead until now:

public function likeAPI(Request $request){
  $user = Auth::user();
  $like = Like::firstOrNew(['user_id' => $user->id, 'post_id' => $request->id ]);
  $post = Post::find($request->id);

  // New like
  if($like->wasRecentlyCreated){
    $like->save();
    $post->increment('likes');
  }
}

And for delete (dislike) as Eloquent currently doesn't support two primary keys, I overwrote the delete function for my Like model with this code:

public function delete(){
  DB::table('likes')->where(['post_id' => $this->post_id, 'user_id' => $this->user_id])->delete();
}

@pacmom You’re being paranoid. Laravel will only query a relation if you request it, or eager-load it.

I don’t understand why you seem afraid to use conventions offered to you by relational databases or the Laravel framework if I’m honest.

@martinbean the answer is easy(and stupid); It's because I started the whole database logic learning with a noSQL database (Cassandra) and then I moved on to relational ones such as MySQL! I also had a paranoid teacher. I know it's not regular but that's how it happened to me, and as you probably know, all noSQL fans scare you from relations and tell you that you shouldn't be using it in big projects.

Anyways, thank you so much, your advices have been more than helpful to me. I owe you big one