Forum: Ruby on Rails Soft delete in HABTM tables

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Preethi S. (Guest)
on 2009-02-19 09:17
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)
MaD (Guest)
on 2009-02-19 09:34
(Received via mailing list)
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?
Preethi S. (Guest)
on 2009-02-19 09:40
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 :)
Andrew P. (Guest)
on 2009-02-19 10:53
(Received via mailing list)
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.
Preethi S. (Guest)
on 2009-02-19 11:08
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.
Andrew P. (Guest)
on 2009-02-19 11:20
(Received via mailing list)
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
:)
Preethi S. (Guest)
on 2009-02-19 11:41
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 !!!! :)
Matt J. (Guest)
on 2009-02-20 04:02
(Received via mailing list)
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-
Preethi S. (Guest)
on 2009-02-20 06:12
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?
Matt J. (Guest)
on 2009-02-20 22:34
(Received via mailing list)
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.
Preethi S. (Guest)
on 2009-02-23 07:38
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 :)
This topic is locked and can not be replied to.