Forum: Ruby on Rails Searching over multiple MySQL 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.
068f60670a8f31323854f945105702db?d=identicon&s=25 Dick Dishkuvek (dishkuvek)
on 2006-04-26 01:15
I am racking my brain over this, probably because I only know very
simple mysql functions.

Basically I've got a few tables, ex:

Albums (id,name,band_id); Bands (id,name,label_id), and Label (id,name)

I want to search through both album.name, band.name, and label.name
throwing all results into a variable, with no redundant info.

I think what I need to be doing is setting up some foreign key
relationships.

I am on the right track?  Or way off?
068f60670a8f31323854f945105702db?d=identicon&s=25 Dick Dishkuvek (dishkuvek)
on 2006-04-26 01:45
Dick Dishkuvek wrote:
> I am racking my brain over this, probably because I only know very
> simple mysql functions.
>
> Basically I've got a few tables, ex:
>
> Albums (id,name,band_id); Bands (id,name,label_id), and Label (id,name)
>
> I want to search through both album.name, band.name, and label.name
> throwing all results into a variable, with no redundant info.
>
> I think what I need to be doing is setting up some foreign key
> relationships.
>
> I am on the right track?  Or way off?

Thinking over this more...  Should I be separating these tables in the
first place?  What would the advantage be over one table that looked
like this:
Music (id,band,album,label), than splitting the tables like I have
above?
0900e6a4828bd989f96427082c6c74ca?d=identicon&s=25 Mike Garey (random52k)
on 2006-04-26 06:35
(Received via mailing list)
On 4/25/06, Dick Dishkuvek <dishkuvek@0xf00d.com> wrote:
> > throwing all results into a variable, with no redundant info.
> above?
no, you should keep the tables separate, to prevent redundancy.  ie, if
you
use one table such as Music, it may look like this:

Table Music:

id | band | album | label
1, band1, album_a1, label1
2, band1, album_a2, label1
3, band1, album_a3, label1
4, band2, album_b1, label1
5, band2, album_b2, label2
6, band2, album_b2, label2

notice that band1 is repeated and label1 is repeated quite a few times.
Also, if you wanted to change the name of label1 to "Label 1", you may
have
to update thousands of rows (depending upon how large your table is).
If
you keep them separate, the schema would look like the following:

bands: (I would probably rename this table to 'artists')
id | band_name |
1, band1
2, band2

albums
id | album_name | band_id
1, album_a1, 1
2, album_a2, 1
3, album_a3, 1
4, album_b1, 2
5, album_b2, 2

labels
id | label_name
1, label1
2, label2

bands_labels (join table)
band_id | label_id
1,1
2,1
2,2

and your models would look like:

band.rb
has_many :albums
has_and_belongs_to_many: labels (since a band can switch from one label
to
another during their lifetime)

album.rb
belongs_to: band

labels.rb
has_and_belongs_to_many :bands

hopefully this will help you get started.

Mike
D1fb4423204d9440a1b7aacce50eb641?d=identicon&s=25 Corey Lawson (Guest)
on 2006-04-26 18:59
(Received via mailing list)
here's some SQL that should work:

select id, name, band_id as subid, 'Album' as source
from albums
union all
select id, name, label_id, 'Band'
from bands
union all
select id, name, null, 'Label'
from labels

You could stick this in a view in the database, or run it from within
Rails by one of the custom sql methods...

You could conceivably join this all into one basic table ala the
Single Table Inheritance pattern as well... (with views to slurp out
the individual subtypes if needed).

You do have indexes on the Name fields in those tables, right?
Ab4c5cd5d9cc028fcba7a5eec8e1bf30?d=identicon&s=25 Alain (Guest)
on 2006-04-26 19:32
all the solution provided so far used SQL, isnt there an elegant way to
merge all the records using activeRecord? Or create an hash with all the
names?
068f60670a8f31323854f945105702db?d=identicon&s=25 Dick Dishkuvek (dishkuvek)
on 2006-04-26 20:58
Mike Garey wrote:
> bands_labels (join table)
> band_id | label_id
> 1,1
> 2,1
> 2,2
>
> and your models would look like:
>
> band.rb
> has_many :albums
> has_and_belongs_to_many: labels (since a band can switch from one label
> to
> another during their lifetime)
>
> album.rb
> belongs_to: band
>
> labels.rb
> has_and_belongs_to_many :bands

Thank you Mike.  It looks like one of the primary items I was missing
was a separate join table.  When I set up inheritance in the modules
like that, where does that functionality express itself when coding?
Does it mean that I can liberally string together combinations of
artist, album, label and attached it to a method for processing?  I've
been trying to find a resource that will explain the inheritance a
little more in depth than the rails api.
068f60670a8f31323854f945105702db?d=identicon&s=25 Dick Dishkuvek (dishkuvek)
on 2006-04-26 21:00
Corey Lawson wrote:
> here's some SQL that should work:
>
> select id, name, band_id as subid, 'Album' as source
> from albums
> union all
> select id, name, label_id, 'Band'
> from bands
> union all
> select id, name, null, 'Label'
> from labels
>
> You could stick this in a view in the database, or run it from within
> Rails by one of the custom sql methods...
>
> You could conceivably join this all into one basic table ala the
> Single Table Inheritance pattern as well... (with views to slurp out
> the individual subtypes if needed).
>
> You do have indexes on the Name fields in those tables, right?

No, I do not have indexes on the name fields.  Clearly I need to dive
into the documentation at mysql.org a little more.  Additionally, I do
not know what a view is.
This topic is locked and can not be replied to.