Soft delete in HABTM tables


#1

Hi,

I’m having a HABTM relationship between profiles and users where User
and Profile are having a many to many relationship.

User Model:
has_and_belongs_to_many :Profiles

Profile Model:
has_and_belongs_to_many :Users

My mapping table has profile_id and user_id as two columns

My requirement is, I want to add an additional column in the mapping
table without creating a model for that table.

The additional column i need is “rowstate” which I’ll be using to
softdelete the rows in the mapping table when user is unassigned from a
profile.

How could i achieve that?(without creating a new model for the mapping
table)


#2

how would you access that attribute (rowstate), if you don’t have a
model for it?

what exactly do you mean by “softdelete”? why not leave it to rails to
delete the rows whenever you unaasign a user from a profile?


#3

MaD wrote:

how would you access that attribute (rowstate), if you don’t have a
model for it?

what exactly do you mean by “softdelete”? why not leave it to rails to
delete the rows whenever you unaasign a user from a profile?

I dont want to delete any rows from the mapping table as,
considering from performance point of view, deleting will take more
time than updating.
So , whenever i unassign a person from a profile, i’ll just update the
rowstate as “deleted”. My requirement is that.
I know that we cant access the extra attribute without a model. As i’m a
fresher in rails i just wanted to know if there is a way to do that.
Thanks :slight_smile:


#4

Andrew P. wrote:

Preethi S. wrote:

I’d question your theory that deleting a row from a join table is
significantly slower than updating a status on it also. Having a status
means that status will need to be part of both foreign keys’ indexes as
you will be accessing the join with a condition on the status. This
will add an “overhead” - ignoring the extra work you are going to have
to do in all your automatic associations.

I was in an impression that updation takes less time than deletion.
I’m getting ur point now.
Could you please elobarate on this point so that i could proceed with
the idea of deletion instead of updation.


#5

Preethi S. wrote:

I dont want to delete any rows from the mapping table as,
considering from performance point of view, deleting will take more
time than updating.

But you will be creating more work and time in managing changing the
state of the join instead ?

I would classify this as severely premature optimising. Unless you
are very regularly removing thousands of profiles from thousands of
users this is a mad design decision.

I’d question your theory that deleting a row from a join table is
significantly slower than updating a status on it also. Having a status
means that status will need to be part of both foreign keys’ indexes as
you will be accessing the join with a condition on the status. This
will add an “overhead” - ignoring the extra work you are going to have
to do in all your automatic associations.


#6

Preethi S. wrote:

I was in an impression that updation takes less time than deletion.
I’m getting ur point now.
Could you please elobarate on this point so that i could proceed with
the idea of deletion instead of updation.

Instead of accessing user.profiles (as simply as that) you are going to
always have to introduce a condition (or use named_scopes but ultimately
the same condition is there). This is complicated more so because your
condition needs to test a column on the join table so you are going to
be hand crafting all your finds to reference the status column.

If you are accessing a table via user_id and status regularly it makes
sense to index your join table on (user_id, status) and possibly also on
(profile_id, status). This probably adds more overhead than a delete
would on a simpler table because changing the status will result in 2
index updates. Deletes aren’t that expensive.

Get your rails application working using rails’ conforming techniques
first. Worry about problems when they occur. And don’t optimise early
:slight_smile:


#7

Andrew P. wrote:

Preethi S. wrote:

Instead of accessing user.profiles (as simply as that) you are going to
always have to introduce a condition (or use named_scopes but ultimately
the same condition is there). This is complicated more so because your
condition needs to test a column on the join table so you are going to
be hand crafting all your finds to reference the status column.

If you are accessing a table via user_id and status regularly it makes
sense to index your join table on (user_id, status) and possibly also on
(profile_id, status). This probably adds more overhead than a delete
would on a simpler table because changing the status will result in 2
index updates. Deletes aren’t that expensive.

yeah. thanks !!! :slight_smile:


#8

Matt J. wrote:

  • finally, while what you’re asking about was possible in older
    versions of Rails
    (but seriously deprecated since about 2.0), I believe that the support
    has been
    removed from 2.3. The best practice now is to use a join model with
    has_many :through.
    Message me if you’re not sure how to set that up.

–Matt J.

On Feb 19, 2:17?am, Preethi S. <rails-mailing-l…@andreas-

Thanks for your tips Jones.

So, do you mean to say that, if I want to use a join model i’ve to use
it using
has_many and through relationship?
if I’m not wrong, should it be like this?

Model Profile
has_many :users, through=>profile_user_mapping
Model Users
has_many :profiles, through=>profile_user_mapping

Model ProfileUserMapping
belongs_to :profile
belongs_to :users

and one more question, is it not a good practice to use a mapping table
without a model? should i use it only using has_many :through
relationship?


#9

On Feb 19, 11:12 pm, Preethi S. <rails-mailing-l…@andreas-
s.net> wrote:

–Matt J.
Model Profile
has_many :users, through=>profile_user_mapping
Model Users
has_many :profiles, through=>profile_user_mapping

You’ve almost got it - it should look like this:

  • in profile.rb:
    has_many :profile_user_mappings
    has_many :users, :through => :profile_user_mappings

  • in user.rb:
    has_many :profile_user_mappings
    has_many :profiles, :through => :profile_user_mappings

and one more question, is it not a good practice to use a mapping table
without a model? should i use it only using has_many :through
relationship?

The has_and_belongs_to_many stuff works, but it isn’t capable of
expanding.
For example, if you wanted to add the kind of ‘soft delete’ you were
thinking
about at the beginning, habtm wouldn’t work. With has_many :through,
you can
add the flag to ProfileUserMapping and then change
the :profile_user_mappings
assocation to:

has_many :profile_user_mappings, :conditions => { :deleted => false }

…or similar, and now the :profiles and :users associations will only
find records
joined by a profile_user_mapping with deleted = false.

–Matt J.


#10

Matt J. wrote:

The has_and_belongs_to_many stuff works, but it isn’t capable of
expanding.
For example, if you wanted to add the kind of ‘soft delete’ you were
thinking
about at the beginning, habtm wouldn’t work. With has_many :through,
you can
add the flag to ProfileUserMapping and then change
the :profile_user_mappings
assocation to:

has_many :profile_user_mappings, :conditions => { :deleted => false }

…or similar, and now the :profiles and :users associations will only
find records
joined by a profile_user_mapping with deleted = false.

–Matt J.

Now I got what I needed exactly.
Thank you so much for guiding me in the proper way :slight_smile:


#11

Couple tips:

  • don’t capitalize association names. It might not affect anything at
    first, but
    it will eventually bite you when the inflector gets confused.

  • as noted by others, don’t optimize early. And even if you’re trying
    to optimize
    early, why would ‘delete profile’ be a bottleneck? Surely views are
    going to be
    much, much more common…

  • finally, while what you’re asking about was possible in older
    versions of Rails
    (but seriously deprecated since about 2.0), I believe that the support
    has been
    removed from 2.3. The best practice now is to use a join model with
    has_many :through.
    Message me if you’re not sure how to set that up.

–Matt J.

On Feb 19, 2:17 am, Preethi S. <rails-mailing-l…@andreas-