Set Up Many-to-Many

This guide shows you how to set up a many-to-many relationship using a join table.

When to Use

Use many-to-many when:

• A Post has many Tags, and a Tag has many Posts

• A User belongs to many Groups, and a Group has many Users

• A Product is in many Categories, and a Category has many Products

Schema Setup

Create three tables: two main tables and a join table.

schema.table :posts do
  primary :id, Int64, auto_increment: true
  column :title, String
  timestamps
end
schema.posts.create!

schema.table :tags do
  primary :id, Int64, auto_increment: true
  column :name, String
  timestamps
end
schema.tags.create!

# Join table
schema.table :post_tags do
  column :post_id, Int64, null: false
  column :tag_id, Int64, null: false
  column :created_at, Time

  foreign_key [:post_id], references: :posts, references_columns: [:id], on_delete: :cascade
  foreign_key [:tag_id], references: :tags, references_columns: [:id], on_delete: :cascade
  index [:post_id]
  index [:tag_id]
  index [:post_id, :tag_id], unique: true
end
schema.post_tags.create!

Define the Models

Post Model

struct Post
  include CQL::ActiveRecord::Model(Int64)
  db_context MyDB, :posts

  property id : Int64?
  property title : String

  has_many :post_tags, PostTag, :post_id

  def initialize(@title : String)
  end

  # Helper to get tags
  def tags : Array(Tag)
    tag_ids = post_tags.all.map(&.tag_id)
    return [] of Tag if tag_ids.empty?
    Tag.where { id.in(tag_ids) }.all
  end

  # Helper to add a tag
  def add_tag(tag : Tag)
    PostTag.create!(post_id: id.not_nil!, tag_id: tag.id.not_nil!)
  end

  # Helper to remove a tag
  def remove_tag(tag : Tag)
    PostTag.where(post_id: id, tag_id: tag.id).each(&.delete!)
  end
end

Tag Model

struct Tag
  include CQL::ActiveRecord::Model(Int64)
  db_context MyDB, :tags

  property id : Int64?
  property name : String

  has_many :post_tags, PostTag, :tag_id

  def initialize(@name : String)
  end

  # Helper to get posts
  def posts : Array(Post)
    post_ids = post_tags.all.map(&.post_id)
    return [] of Post if post_ids.empty?
    Post.where { id.in(post_ids) }.all
  end
end

Join Table Model

struct PostTag
  include CQL::ActiveRecord::Model(Int64)
  db_context MyDB, :post_tags

  property post_id : Int64
  property tag_id : Int64
  property created_at : Time?

  belongs_to :post, Post, :post_id
  belongs_to :tag, Tag, :tag_id

  def initialize(@post_id : Int64, @tag_id : Int64)
  end
end

Create Relationships

Add Tags to a Post

post = Post.create!(title: "My Post")
tag1 = Tag.create!(name: "Crystal")
tag2 = Tag.create!(name: "ORM")

# Add tags
post.add_tag(tag1)
post.add_tag(tag2)

post.tags.map(&.name)  # => ["Crystal", "ORM"]

Create Join Records Directly

PostTag.create!(post_id: post.id.not_nil!, tag_id: tag.id.not_nil!)

Query Through Join Table

Get Tags for a Post

post = Post.find(1)
tags = post.tags
tags.each { |tag| puts tag.name }

Get Posts for a Tag

tag = Tag.find_by(name: "Crystal")
posts = tag.posts if tag
posts.each { |post| puts post.title }

Find Posts with Specific Tag

crystal_tag = Tag.find_by(name: "Crystal")
if crystal_tag
  post_ids = PostTag.where(tag_id: crystal_tag.id).all.map(&.post_id)
  crystal_posts = Post.where { id.in(post_ids) }.all
end

Remove Relationships

Remove a Tag from Post

post.remove_tag(tag)

Remove All Tags from Post

post.post_tags.all.each(&.delete!)

Verify It Works

post = Post.create!(title: "Test")
tag = Tag.create!(name: "Test Tag")

post.add_tag(tag)

post.tags.map(&.name)  # => ["Test Tag"]
tag.posts.map(&.title)  # => ["Test"]

post.remove_tag(tag)
post.tags  # => []

Related

• Set Up Has Many

• Set Up Belongs To

• Use Transactions