Effective way to perform conditional join

Question

Is there any way to provide conditional join instead of case when ... then ... end?

I have 3 tables: vehicles, sport_cars & motorcycles.

• vehicles - base table that stores common data for sport_cars and motorcycles
• sprot_cars - table with sport-car specific data
• motorcycles - table with motorcycle specific data

Also, vehicles has a discriminator column type that contains varchar value sport_car / motorcycle.

Example model:

vehicles:

• id bigint,
• type varchar(20)
• cost number(7,2)
• sport_car_id bigint,
• motorcycle_id bigint,

sport_cars:

• id bigint
• is_cabriolet boolean
• seats integer

motorcycles:

• id bigint
• is_motocross: boolean
• handlers: integer

I need to retrieve all vehicles by type and be able to use limit & offset, using a single query, but can't figure out the approach for it.

select 
  r.id, 
  r.cost,
  case 
    when :type = 'sport_car' then s.is_cabriolet
    else m.is_motocross
  end as flag
from (select 
    v.id,
    v.cost,
    v.sport_car_id,
    v.motorcycle_id
  from vehicles as v
  where
    v.type = :type
  limit :limit
  offset :offset) as r
left join sport_cars s on r.sport_car_id = s.id
left join motorcycles m on r.motorcycle_id = m.id

Maybe this solution could work, but it's quite uneffective, because we provide at least one odd join. Also, if there could eb multiple tables instead of 2 it would become a nightmare to join all of these.

Also, I've tried to retrieve it using union all, but the thing is that it would be tricky to provide limit and offset for such query.

Seems, that this could be a problem of a bad design, but this model already exists and it looks like that there's no need to update it.

Is there any way to provide some kind of conditions for joins? Or maybe there could be another approach to select the necessary data?

Answer 1

Your database is not correctly modeled. There are several redundancies...

1. the foreign keys sport_car_id and motorcycle_id are of no interest. The primary key of the vehicles table must serve as a foreign key to the child tables while being a primary key

2. the type column is of no interest because it can be deduced from the join

The correct model is:

CREATE TABLE vehicles
(   id bigint PRIMARY KEY,
    cost decimal(7,2) -- instead of number
);

CREATE TABLE sport_cars
(   id bigint PRIMARY KEY REFERENCES vehicles (id),
    is_cabriolet boolean,
    seats integer
);

CREATE TABLE motorcycles
(   id bigint PRIMARY KEY REFERENCES vehicles (id),
    is_motocross boolean,
    handlers integer
);

This is called inheritance in modeling terms...

This model must be completed by a trigger to ensure the exclusivity constraint: either a vehicle is a motorcycle, or it is a car, in other words there must never be the same id value in the two child tables ....

Here is one of theese two triggers :

CREATE FUNCTION sport_cars_id_exists() 
   RETURNS trigger
   LANGUAGE plpgsql
AS $$
  DECLARE
    id_exists boolean;
  BEGIN
    SELECT (id IS NOT NULL) FROM sport_cars WHEREid = NEW.id INTO id_exists;
    IF id_exists THEN
      RAISE EXCEPTION 'Exclude exception between motorcycle and car (id : %)', NEW.id;
      RETURN NULL;
    END IF;
    RETURN NEW;
  END; $$;


CREATE TRIGGER EVT_IU_motorcycles
   BEFORE INSERT OR UPDATE 
   ON motorcycles
   FOR EACH STATEMENT EXECUTE PROCEDURE sport_cars_id_exists();

Now to know if a vehicle is a motorcycle or a car you just need to know if the join exists in one or the other table like this :

SELECT *, CASE WHEN EXISTS(SELECT * FROM sport_cars AS c WHERE m.id = v.id)
                       THEN 'Sport Car'
                    EXISTS(SELECT * FROM motorcycles AS m WHERE m.id = v.id)
                       THEN 'Sport Car'
       END AS vehicle_type
FROM   vehicles AS v

This query should be the view systematically used in dev...