CODEWITHSUNDEEP
mysqlsqlpolymorphic-associations
Sisir
Sisir

Reputation: 2816

MySQL Wrong id returns for Polymorphic Association Joins

I have polymorphic association with two tables. (I know its a mess, but I will have to leave with it for the time being)

I need to join results from both tables when querying with the master table. I am close with the result I want but the master table id returns wrong in my case.

Data Structure

Table A
    |-- id
    |-- type
    |-- type_id
    |-- property_a1
    |-- property_a2



Table B
    |-- id
    |-- property_b1
    |-- property_b2


Table C
    |-- id
    |-- property_c1
    |-- property_c2

Expected Output

I want like the result below. Each row contains all fields (or selected by query) of all three tables. Null or empty string is assigned if property is missing in a given associated table. 

Result Row
    |-- id
    |-- property_a1
    |-- property_a2

    |-- type
    |-- type_id

    |-- property_b1 // if property from Table c value = ''
    |-- property_b2 // if property from Table c value = ''


    |-- property_c1 // if property from Table b value = ''
    |-- property_c2 // if property from Table b value = ''

My work so far can be seen in this sqlfiddle:

http://sqlfiddle.com/#!9/2d05e/17

SQL Query I Used

From the fiddle. 

SELECT
  product.id,
  res.name,
  res.fruit_id,
  res.vegetable_id,
  res.is_green,
  res.color,
  product.price,
  res.seasonal
FROM
  product
JOIN
  (
  SELECT
    fruit.id AS fruit_id,
    0 AS vegetable_id,
    fruit.name,
    fruit.color,
    fruit.seasonal,
    false as is_green
  FROM
    fruit

  UNION
SELECT
    0 as fruit_id,
    vegetable.id AS vegetable_id,
    vegetable.name,
    '' as `color`,
    false as `seasonal`,
    vegetable.is_green
  FROM
    vegetable

) res ON product.type_id = res.fruit_id OR product.type_id = res.vegetable_id

I could use GROUP BY but that just removes data that is necessary.

Upvotes: 1

Views: 38

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SQl Fiddle Demo

SELECT product.id, res.name, res.fruit_id, res.vegetable_id,
  res.is_green,res.color,product.price,res.seasonal
FROM product
JOIN
  (
  SELECT fruit.id AS fruit_id,
    0 AS vegetable_id,fruit.name,fruit.color,fruit.seasonal,
    false as is_green
  FROM fruit      
  UNION
  SELECT 0 as fruit_id,vegetable.id AS vegetable_id,
    vegetable.name,'' as `color`,false as `seasonal`, vegetable.is_green
  FROMvegetable      
) res ON (product.type_id = res.fruit_id  AND product.product_type = 'fruit')
      OR (product.type_id = res.vegetable_id AND product.product_type = 'vegetable')

Upvotes: 1

Related Questions