CODEWITHSUNDEEP
mysqljoin
kid_drew
kid_drew

Reputation: 3995

MySQL foreign key multi join

I have this table setup (simplified):

ads
 - id
 - brand_id

brands
 - id

tags
 - taggable_type
 - taggable_id
 - tag

The taggable_type in the tags table will be either "Ad" or "Brand", the taggable_id will identify the ad_id or brand_id respectively, and then the tag is a string.

Ads inherit their brand's tags, and I'm trying to write a search query that pulls up a set of ads for a given set of tags, where those tags belong to either the ad itself or the brand associated with that ad.

These tables can all be fairly large, so I need it to be efficient.

Here's what I have (this isn't working)

SELECT
  a.*
FROM
  ads a
JOIN
  ((
    SELECT
      *
    FROM
      tags
    WHERE
      tag IN ({$tags})
      AND taggable_type = "Ad"
  ) t FULL JOIN (
    SELECT
      *
    FROM
      tags
    WHERE
      tag IN ({$tags})
      AND taggable_type = "Brand"
  )) tt
  ON (t.taggable_id = a.id) OR (tt.taggable_id = a.brand_id);

For starters, I'm getting an error on the full join. I've also tried an inner join and a left join, and it's still not working. I feel like I'm just doing something fundamentally stupid. Any help?

Upvotes: 0

Views: 160

Answers (1)

hsan
hsan

Reputation: 1559

Like this it should work

SELECT DISTINCT
  ads.*
FROM
  ads
  LEFT JOIN brands
    ON ads.brand_id = brands.id
  INNER JOIN tags
    ON (
      tags.taggable_type = 'brand'
      AND brands.id = tags.taggable_id)
    OR (
      tags.taggable_type = 'ad'
      and ads.id = tags.taggable_id)
WHERE
  tags.tag IN ('tag 1', 'tag 2', 'tag 7')

SQL Fiddle

But you might also want to think about your database structure again. Perhaps a setup like the following would suit you better.

ads(id, brand_id)
brands(id)
tags(id)
tags_ads(tag_id, ad_id)
tags_brands(tag_id, brand_id)

Would have the benefit of being able to assign a tag to more than one brand or ad. Or to a brand and an ad...

Upvotes: 1

Related Questions